This article will demonstrate how to create cascading list boxes between tables with one-to-many relationships. The example provided here has been created by the author using Microsoft Access 2007. This method also modified to work with combo boxes and can easily be adapted for use with many-to-many relationships.
Our example will include four (4) list boxes based on geographic location: Continent, Country, State, City.
We will begin by creating a table for each.
Tables
We will begin by creating a table for each.
Tables
tblContinents - ContinentID, ContinentName
tblCountries - CountryID, CountryName, ContinentID
tblStates - StateID, StateName, CountryID
tblCities - CityID, CityName, StateID
Next, we will establish single-to-many relationships between the tables.
Each continent may be related to many countries, each country may be related to many states, each state may be related to many cities.
Form Controls
Now that the table relationships are in place, it's time to add the list box controls to a form.
We'll add one list box for each of our tables; lstContinent, lstCountry, lstState, lstCity.
Tip: The column count property for each should be 2. Column width should be 0";2" in order to hide the bound ID column and show the name column.The row source of each child list box will be dependent on the parent list box(es). While we could set the row sources of our list boxes in VBA, this example will accomplish the task by setting a query as the rowsource.
lstContinent.RowSource
The row source for the continent list box is pretty straight forward as it is the top level of our cascade and isn't dependent on any of the other list boxes.
SELECT tblContinents.ContinentID, tblContinents.ContinentName
FROM tblContinents
ORDER BY tblContinents.ContinentName;
lstCountry.RowSource
The country list box row source is dependent on the continent list box control.
SELECT tblCountries.CountryID, tblCountries.CountryName, tblCountries.ContinentID
FROM tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID
WHERE ((tblCountries.ContinentID)=[Forms]![frmLocation]![lstContinent]);
For the remaining levels of our cascade, we must not only reference the list box directly above it, but all parent list boxes back to the top level. This will be explained when we configure the list boxes to be updated in VBA.
lstState.RowSource
SELECT tblStates.StateID, tblStates.StateName, tblStates.CountryID, tblContinents.ContinentID
FROM (tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID) INNER JOIN tblStates ON tblCountries.CountryID = tblStates.CountryID
WHERE (((tblStates.CountryID)=[Forms]![frmLocation]![lstCountry]) AND ((tblContinents.ContinentID)=[Forms]![frmLocation]![lstContinent]))
ORDER BY tblStates.StateName;
lstCity.RowSource
SELECT tblCities.CityID, tblCities.CityName, tblCities.StateID, tblCountries.CountryID, tblContinents.ContinentID
FROM ((tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID) INNER JOIN tblStates ON tblCountries.CountryID = tblStates.CountryID) INNER JOIN tblCities ON tblStates.StateID = tblCities.StateID
WHERE (((tblCities.StateID)=[Forms]![frmLocation]![lstState]) AND ((tblCountries.CountryID)=[Forms]![frmLocation]![lstCountry]) AND ((tblContinents.ContinentID)=[Forms]![frmLocation]![lstContinent]))
ORDER BY tblCities.CityName;
Below is the lstCity.RowSource query for those of you more comfortable building queries in design view.
Updating the Row Sources
Finally, we'll add VBA code to our form to requery our list boxes to update the row source(s) based on the user's selection.
Private Sub lstContinent_AfterUpdate()
' requery child list boxes.
Me.lstCountry.Requery
Me.lstState.Requery
Me.lstCity.Requery
End Sub
Private Sub lstCountry_AfterUpdate()
' requery child list boxes.
Me.lstState.Requery
Me.lstCity.Requery
End Sub
Private Sub lstState_AfterUpdate()
' requery child list boxes.
Me.lstCity.Requery
End Sub
As you can see, the continent list box requeries all three child list boxes. This is to ensure that all of the list boxes are updated when a new continent is selected. Each level requeries all of it's child controls. By including all of the parent controls as criteria in in our row source queries (set up earlier), we ensured that only related child records are displayed when the selected parent records are updated.
This article will be updated to include a functional example for download in the near future.
