Related Content:
    Combo Box
    Cascading Combo Boxes
    Cascade Combo - Leaving Null Values

Cascading Combo Boxes (a.k.a. Synchronized Combo Boxes)

Definition

Cascading Combo Boxes are a series of 2 or more Combo Boxes in which each Combo Box is filtered according to the previous Combo Box.

Example

A simple example of where you could use Cascading Combo Boxes is a property address. Each address consists of a state, city, and zip-code. Three Combo Boxes will be used in this example. The first Combo Box, State, has a RowSource which lists all distinct states. When a user selects a state, the City Combo Box and the Zip Code Combo Box are both filtered to only the distinct cities and zip codes which reside within the selected state. (Note that this example does not filter the list of zip codes to reflect only zip codes associated with the selected City)

This example is based on a normalized structure of three tables:

• tblStates:
  • StateID - Primary Key,
  • StateName
• tblCities:
  • CityID - PrimaryKey,
  • StateID - Foreign Key,
  • CityName
• tblZipCodes:
  • ZipCodeID - Primary Key,
  • StateID - Foreign Key,
  • ZipCode

How-To:
Using the example above, the first Combo Box, State, would simply have its RowSource property set to tblStates, and its RowSourceType property set to Table/Query.

The City Combo Box would have as its RowSource property SQL for a query which places a WHERE condition on the Foreign Key, StateID, to equal the State Combo Box, like this: "SELECT * FROM tblCities WHERE StateID =" & cboState (cboState is the name of the state Combo Box). Its RowSourceType property would be set to Table/Query.

The Zip Code Combo Box would also have as its RowSource property SQL for a query which places a WHERE condition on the Foreign Key, StateID, to equal the State Combo Box, like this: "Select * FROM tblZipCodes WHERE StateID =" & cboState. Again, its RowSourceType property would be set to Table/Query.

In order to have the City and Zip Code Combo Boxes updated to reflect changes in the value contained in the State Combo Box, it's necessary to invoke the Requery method for the two Combo Boxes in a procedure called from the AfterUpdate event of the State Combo Box.