Customizing Two or More Combo Boxes

This shows how to create a custom drop down list that is dependant on a previous custom combo box choice on the same form. For instance the user has chosen a country from a dropdown list and now you want the cities box to only show cities in that country.

   You can create your own database or use an existing one or download the example which has the table already in it. 

combobox.mdb or zipped combobox.zip

Open the database and create a new form. Don't link it to any tables.

Create an unbound combo-box on the form, using the wizard. You want the combo-box to look up values in a table or query. Choose CityList as the table to get the list from. Put just the Country field into the list. leave the label as Country.

    Create a second combo-box the same way this time choosing Country and City for the fields and City as the unique field, and City as the label.

    Right click the country combo box and choose properties. On the 'Other' tab change the name to PickCountry , and then select the 'Data' tab ad click on the Row Source to highlight it then click the small button with three dots. This opens the query the combobox is based on and we need to modify it slightly to group the Countries so it doesn't display multiples. Choose Menu -> View -> Totals  A little box with groupby should have dropped into the query. that's all we need to do here so close the query and choose yes to save changes.

    Close the properties box and save the form as Form1.

    Bring up the properties for the City combobox and change it's name to city and then bring up it's row source again by selecting the data tab, highlighting the row source box and clicking the little button with three dots on it. Okay this one is a little more complex. In the criteria section for the  Country field type the following

Close the query saving the changes.

Open the form and choose a country from one box and notice the second box lists only cities in that country. Now change the country box again and choose a city. Wait a minute you say it's not right. When access loads a list into a combo box it only does it once. You need to tell it to do it again.

Back into design mode and bring up the properties for the City combo Box. Choose the event tab select On Enter and click that button with three dots then choose code builder. Access will write the start of the code for you all you have to do is type the following.

this tells access that every time the city box is given the focus requery it to get the latest list. Close the code window save and open the form. Give it another go. Et Voila.

Don't want the city box clutered up with the list of countries. Go into the properties and choose the format tab and see the section column widths. Change the first col to 0cm so it looks like this  0cm;2.542cm

That will hide the column.

Happy coding.