Using a combo box to automatically complete another field

Question

I would like to be able to enter the city name in one field and have the next field automatically enter the county that the city is located in. I am running windowsXP and access 2000 The fields that I am using are as follows: Month, Ticket NO,City, County, Type Svc, Amt, Tech

Answer

To do this, you will need a table that lists all the cities and the county that they are in.

Once you have sourced the above table; to do what you ask:

Create a combo box based on the above table.  Do this by following the wizard, add the city field and the county field.

Then, in the after update event for the combo box, you will need to add some code.

To do this:

Right-click on the combo box you have just created and select 'properties' from the drop-down list.

Click on the event tab.

Place your cursor next to 'After Update', then click on the '...' button that should now appear.

In the window that is now displayed, double-click on 'code builder'. This will open the VBA window.  There will be two lines of code, similar to below:

Private Sub Combo13_AfterUpdate()

End Sub

In between these two lines of code, add the following:

Me!County = Me!Combo13!Column(2)

Change the name of County to the name of the field that will contain the county information, and Combo13 to the name or your new combo.  You may also need to change (2) to (1).  Access counts columns starting from 0, i.e. 0 1 2 3, etc.

Therefore, if county is in the second column of your combo box, the number in () would be 1.  Note:  There is also often a hidden primary key in the combo, so you may need to include this in your count as well.

Now when you choose a city from your combo box, the county should automatically appear in the county text box.

Do you like this tip? Subscribe to my free weekly Newsletter to receive tips weekly via email. Click Here to subscribe.