Access Tips: Cascading Lists for Access Forms

Combo Boxes and List Boxes are popular tools for both assisting the user and controlling data input. Their functionality can be enhanced by changing the contents of the list depending on circumstances. For example, the user makes a choice from one list which causes the contents of another list to change. This concept of cascading or interdependent lists is quite easy to put into practice with a little VBA programming.

This tutorial demonstrates several different ways to create cascading lists on your Access forms. I will use combo boxes, rather than list boxes, as the latter have a more specific purpose (to allow the user to make multiple selections).

What Do Cascading Lists Do?

The purpose of cascading lists is to give the database designer greater control over data input, and to make things easier for the user. On their own, combo boxes make data input easier and more reliable. When two or more combo boxes work in conjunction with each other they can help to reduce the user's decision making by prompting them with only relevant data.

In this example there are two combo boxes: one displays a list of countries, the other displays a list of cities. If they worked independently the user could choose a country and a city but might get the combination wrong. Also, the list of cities in would have to show all the cities available. The combo boxes can be linked in a number of ways. The illustrations below show how linked combo boxes might work. The user chooses a country first then opens the city list. They see a list of cities relevant to the country they selected.

	The main combo box displays an unchanging list of countries.
	If the user chooses "France" the dependent combo box displays a list of French cities
	If the user chooses "United Kingdom" the dependent combo box displays a list of UK cities.

The following examples show several different ways of achieving this effect and are presented in an increasing order of complexity.

Example 1: Multiple Row Source Tables

This is the simplest method to code but it requires a table for the main combo box and several tables for the dependent combo box. This example has a table (tblCountries) that is assigned as the Row Source of the main combo box (cboCountry). There are also three more tables (tblFrance, tblUnitedKingdom, and tblUnitedStates) which will provide in their turn the row source for the dependent combo box (cboCity). To begin with, no row source is specified for the dependent combo box.

The plan is to have the contents of the cboCity list change to reflect the user's choice from cboCountry. This will be achieved by programmatically defining the Row Source property of cboCity using the After Update event of cboCountry. Here's the code that does the job:

How it works...

The code for this method is very simple. The AfterUpdate event fires when the user makes a choice from the cboCountry combo box. The code uses a Case Statement to assign one of the city tables to to the Row Source property of the cboCity combo box according to the user's choice.

Example 2: A Single Row Source Table

This example uses a single table which provides data for the Row Source of both combo boxes. The table (tblAll) contains two fields, one for the name of the City and the other for the name of the Country to which it belongs...

The Row Source property of the cboCountry combo box takes the form of an SQL statement which represents a query of the tblAll table returning the unique values found in the Country field, sorted into ascending order:

As in the previous example, no row source is specified for the dependent combo box. The code will deal with that. The following procedure runs on the After Update event of the cboCountry combo box:

How it works...

When the user makes a choice from the cboCountry combo box the AfterUpdate event fires. The attached code defines a Row Source to the cboCity combo box in the form of an SQL statement.

NOTE: When writing SQL in VBA code I usually write my SQL statements as shown above with each clause on a separate line. I think this makes them easier to read. If you do this remember to enclose each line in quotes, concatenate (i.e. join) each line with an ampersand (&) and insert the line continuation character space+underscore ( _) when you break the code line.

The SQL statement changes according to the user's choice as determined by the inclusion of cboCountry.Value in the WHERE clause. If, for example, the user had chosen France then the line containing the WHERE clause would read:

Example 3: Synchronising the Lists

This example uses the same code for the After Update event of the cboCountry combo box to assign the appropriate Row Source to the cboCity combo box as in the previous example. This time an additional procedure has been added to synchronise the cboCity Row Source with any existing value that might already be in the City field.

When a Row Source is assigned to the cboCity combo box, it remains assigned until it is reassigned by a change in the cboCountry combo box. So, when navigating through existing set of records, the list of cities may not be appropriate for the city shown in the City field.

For example, you enter a record for an address in the United Kingdom so cboCity displays a list of UK cities. You then move back through the records and stop on an address in France. You might wish to edit that address so you open the cboCity combo and instead of seeing a list of French cities you see a list of UK cities! This happened because that was the last Row Source assigned to the combo box and nothing has happened since to change it.

This example corrects that using the following code on the form's On Current event:

How it works...

The OnCurrent event fires when a form moves from one record to another (or when the form is refreshed or requeried).

The first line uses the DLookup function (this works like Excel's VLOOKUP function). This gets the value (i.e. the existing city name) from the City field, looks for it in tblAll table, and returns the corresponding Country name which it places in cboCountry combo box. (NOTE: If, as in my example there is a Country field in the form's underlying table then this data should be in place anyway - but I'm just making sure! In fact, you might want to omit or modify this line if you were allowing users to enter city names that were not on the list.)

The next line is the same as the one that runs on the AfterUpdate event of the cboCountry combo box but we need it here because the AfterUpdate event has not been fired.

Example 4: Mixing Controls - Using an Option Group

The method works just as well with different combinations of controls. Here I have replaced the cboCountry combo box with an Option Group called grpCountry with option buttons for the various countries. The illustrations below shows how it works.

	If no city has yet been selected the option group has a null value and no country is selected.
	When a country is chosen a matching list of cities is displayed by the dependent combo box.

Writing the cascade code...

This form incorporates all the features of the previous example (e.g. synchronising the form with the data) but the code differs because I have used an Option Group (named grpCountry) instead of a combo box for the user to choose a country. Of course, an option group does not require a Row Source. Instead, all the options are on view as labelled option buttons (sometimes called "radio buttons"). Each Option Button has an Option Value property. This value must be a whole number but it can be whatever you want (within limits - its data type is Long) but it is logical to give them sequential numbers so mine are France(1), United Kingdom(2) and United States(3). When the user chooses one of the options by clicking it a black dot appears in the button and the Option Group has a value corresponding to the Option Value of the selected button. So, if the user selects the United States button the Option Group has a value of 3.

NOTE: When you draw a group of Option Buttons you should draw the Option Group frame first, then draw the Option Buttons inside. When you do this all the buttons inside the frame become part of the same group and function properly. Only one can be selected at a time, and the selected button passes its value to the group.

The code used to assign a Row Source to the cboCity combo box runs on the After Update event of the grpCountry Option Group (NOT the event of the individual button). The event fires when the user selects one of the buttons:

How it works...

First of all, a Case Statement checks the value of the group and puts the corresponding country name a string variable called strCountry. Then an SQL statement, incorporating the variable and querying the tblAll table, is assigned as the row source of the cboCity combo box.

It works in much the same way as previously but the code gets the name of the country from an Option Group instead of a Combo Box.

Writing the synchronise code...

Similarly the forms On Current event is used to synchronise the Option Group with the City field, and again the code is a bit more complex:

How it works...

You might have noticed that in the first illustration none of the option buttons are selected, all are greyed out. Form designers often like to set one of the buttons of an option group as the "default" button so that it is already selected when the form opens or when the user moves to a new record. It's easy to do, just find the option group's Default Value property and set it to whatever value represents the button you want selected. But I didn't want to do that. Since I was going to synchronise the option group country with any existing city, I decided to give the group a Null value if no city name was present. This is achieved by the If Statement at the start of the code procedure.

Next comes the code to determine the correct country for the existing city (if present). Like the previous example it uses the DLookup function, but unlike the previous example it can't feed the country name straight into the option group. It has to convert the name to a number (the value of the appropriate option button) so it uses a Case Statement to do this. Finally, the same code as before synchronises the combo box list with the existing city.