Cascading Combo Boxes

Sometimes, you would like your user to choose a value of a combo box, and then use that value to limit the selections in another combo box. Since the upper combo box affects the lower, this is sometimes called "cascading" the combo boxes.

To accomplish this, you need the SQL statement building techniques described earlier in this chapter. Let's say we have two combo boxes: one for County and one for City. Each County can have many Cities and each City is in one County. The table design would look something like this:

tblCounty

CountyKey

AutoNumber

CountyName

Text 255

tblCity

CityKey

AutoNumber

CityName

Text 255

CountyKey

Long Integer

When we present the selection form, we want the County to be selected first, then limit the City to those found in that County.

The control cboCounty will start off enabled in Design View, but cboCity will be disabled. In the After Update event for cboCounty, we'll include the following code:

Me!cboCity = Null

If IsNull(cboCounty) Then

Me!cboCity.Enabled = False

Else

Me!cboCity.Enabled = True

Me!cboCity.Rowsource = ReplaceWhereClause(Me!cboCity.Rowsource, _

"Where CountyKey = " & Me!cboCountyKey" Me!cboCity.Requery End If

Let's take this code section by section. Me!cboCity = Null

First, we clear out the City combo box by setting it to Null. We do this because we are in the After Update event of the County combo box, so we know it's been changed. If the whole County has been changed, then any value that was in the City combo box is no longer valid, so we just wipe it out.

If IsNull(cboCounty) Then Me!cboCounty.SetFocus

We are about to disable the City combo box, which won't be possible if it has the focus. Just in case it does, we set the focus back to cboCounty.

Me!cboCity.Enabled = False

Now, if the user just deleted the value for County (setting it to Null), we need to disable the City combo box, since they must choose a County before they can select a City.

Else

Me!cboCity.Enabled = True

Alternatively, if the user changed the County to another value, the City combo box can be enabled so they can select a City.

Me!cboCity.Rowsource = ReplaceWhereClause(Me!cboCity.Rowsource, _ "Where CountyKey = " & Me!cboCountyKey"

But now we need to limit the Cities in the drop-down list to those that are in the selected County. To do this, we modify the Rowsource property for the City combo box using our old friend ReplaceWhereClause.

Me!cboCity.Requery End If

Although we have changed the Rowsource property of the City combo box, it won't take effect until we requery it. At this point, the user can go down and select from a list of Cities that are in the selected County. Our cascading selection is complete!

Was this article helpful?

+1 0

Post a comment