Jul 12, 2016

In Microsoft Access, we are going to create two Combo Boxes whose values in one of them depend on the value selected in the other one. For example selecting a country from a Country Combo Box will automatically populate the second Combo Box City with the name of cities in that country only, so that the user can easily select appropriate city.

The important requirement for this to work is that there has to be two columns in one table that hold the values of the two Combo Boxes. Therefore in the case above we need to have a table that has at least two columns, Country and City. You create a Combo Box to get data from one of the columns and the other Combo Box gets its data from the other column. So let’s go and see how it’s done.

Here’s our table:

In a form we add a combo box and follow the wizard to select values from a table.

Select the table, Contacts in our example.

Drop Country field to the right.

Apply sorting if you want, and click Next and then the important step is to un-hide the key column.

In the next screen select Country, click Next and type “Country” for Title and click on Finish.

Follow the same steps for the City combo box and then save the form as say, frmContacts.

Now select the Country combo box and activate its Properties pane. In the Other tab set the Name property to cboCountry and then in the Data tab select the Row Source and click on its “…” button. This takes you to query design view. In this view remove the ID from the grid if you have one, so it only has a single field Country.

Then click on the grey area of the query shown by red arrow to activate the General Query properties and set its Unique Values to Yes.

Save and close the query. Back in the Properties of the cboCountry in the Data tab set the Bound Column to 1.

Follow the same procedure with the City combo box and name it cboCity, however in the cboCity query design you need to add the Country field and set its criteria to Forms!frmContacts!cboCountry and make sure the Show check box is unticked shown by red arrow below.

What you are saying is that the entries in the City combo box should be filtered so that their Country field matches the one selected in the cboCountry combo box.

Set other setting as described for cboCountry above.

Test the form. You’ll notice that it is working only in the first try. Subsequent selection of cboCounty will not fetch appropriate cities. The reason is that Access does not run the cboCity query every time you change your selection in the cboCountry. You need to force it to re-query. To do this you need to write a command to be executed when something is changed in cboCountry. So go to the Properties of cboCountry, in the Event tab, for the On Change event drop down select [Event Procedure] and then click on the “…” button. This takes you to VBA programming screen. Then add this line of code cboCity.requery where shown in the picture below:

Close the VBA screen and test your form. It should now be working. Don’t forget to save your form.

Hope this has helped.