Have you ever wanted to organise two combos on an Access form such that the selection from one combo determines the data for the second combo? For example the first combo gives a choice of country and the second combo then shows a list of cities in that country. This article describes the concepts by using a table, a form, two queries and a macro. A good way to gain a fuller understanding might be to attend one of the many available training courses.

We'll stick with the example of cities and countries. Suppose you have an Access database with a table which lists all the offices in your organisation. The table is called called 'TblOffices' and has three fields, Ref, City and Country. So the list contains details of city and country for each office. It's important to be aware that you have a table showing these details before creating the combos. You might like to add records for, say, ten cities in at least three different countries.

Before creating the form we need to create two queries. The first query will be the source of the first combo's lookup. The second query will use the results of the first combo to determine the second combo's lookup.

To create the first query, create a new query in design view and add the table 'TblOffices'. In the query designer add the Country field only. If you run the query you'll see a list of all ten countries for the various offices, with some appearing more than once. Back in query design view, choose the Group command on the upper toolbar or tab. Then run the query again. This time you'll see each country listed once. This is just what we want for the lookup for the first combo. Save the query as 'QryCountries'. We'll build the second query later, so now it's time to create the form.

Create and save a new blank form called 'FrmCombos'. In reality you would link this form to another table so you could add the data selected by the two combos to the other table. However in this article we'll focus just on the combos. In form design view create the first combo. Once added the combo wizard should appear. Use the selections to choose the data source as a query, and then choose 'QryCountries' and complete the wizard. Give the combo a name, for example 'ComboCountries', then save the form. Then back in regular form view test the combo to ensure you can now see a list of the countries. Make a choice of country, and then minimise the form. It's important not to close the form for the next steps to work.

Now we create the second query. So create a new query in design view. Add the same table as before, 'TblOffices'. Add the fields City, then Country in that order. Then select the Criteria cell under the Country field. We're going to add the first combo's name here to filter the query. To do this right click and choose the wizard to turn on the Expression builder. Expand the left hand list to show forms, loaded forms and select 'FrmCombos' (which is still open don't forget). Choose the item ComboCountries, value and either click Paste or OK to add this to the Expression builder panel, and click OK to finish. Now the first combo will be used as the filter for the second query. Save the query as 'QryCities'. If you run the query you should see the cities corresponding to the chosen city on the form in the first combo. The query can now be closed.

Next return to 'FrmCombos' design view and add the second combo. The combo wizard should again appear. Use the selections to again choose the data source as a query, and this time then choose 'QryCities' and complete the wizard. Give the combo a name, for example 'ComboCities', then save the form.

Now back in regular form view test the two combo combination. Make a choice of city in the first combo. Then click the popdown in the second combo and you should see the cities for the country chosen in the first combo. You may find that the second combo retains the lookup list after a selection has been made. So if you change your choice in the first combo, the old selection is still there. To avoid this we will use a simple macro which re-queries or refreshes the second combo lookup if the first combo is changed.

To do this return to form design view, select the first combo, and choose Properties. In the Properties panel choose the event tab and click into the AfterUpdate cell. Use the pop down or click on the right hand dots to create a macro. In the macro builder add a single item 'Requery' (for Access2003 and 2007) or refresh (Access2010), save, and close the macro. Then save the form and return to regular form view and test the form again.

Now if you make a selection in the first combo and then change the selection later, the lookup list in the second combo will always be up to date.

Hopefully this article will wet your appetite to learn more about Access controls and different methods used to select data. Attending a training course might well be a worthwhile next step to take your Access skills to the next level.