Limiting the selection available in a Combo Box based on another

Limiting the selection available in a Combo Box based on another

If you have two Combo boxes on a form that are displaying related data, you can make it easier for your users to select data by limiting what is displayed in the second combo box based on the selection made in the first.

For example, assume that we have two tables that will provide the data for two combo boxes, tlkpCounty and tlkpTown. tlkpCounty has two fields, CountyID (an AutoNumber Primary Key) and CountyName (a Text field). tlkpTown has three fields, TownID (an AutoNumber Primary Key), TownName (a Text field), and CountyID (the foreign key from tlkpCounty).

Open the form in design mode, and add two combo boxes to the form, cboCounty and cboTown. Set the ControlSource of these two controls to the appropriate fields in the underlying table. Then select cboCounty's properties, and click on the ellipsis (...) that appears next to its RowSource. In the Query Grid that appears, add tlkpCounty, and then add both fields to the grid, setting the query to sort by CountyName ascending. Close the query grid, and accept the changes. Set the ColumnCount of this Combo Box to 2, and the ColumnWidth to 0;2.54cm, so that the user doesn't see the CountyID.

Now, with cboTown, create a RowSource, based on tlkpTown, that has TownID and TownName, sorted by TownName. Set the ColumnCount to 2, and theColumnWidth to 0;2.54cm.

In the AfterUpdate event for cboCounty, we need to dynamically create a new RowSource for cboTown:

Private Sub cboCounty_AfterUpdate()
Me!cboTown.RowSource = "SELECT [TownID],[TownName] FROM [tlkpTown] WHERE [CountyID]=" & Me!cboCounty & " ORDER BY [TownName];"
End Sub

Now, as we change the selection of County in the first Combo Box, it changes the values displayed in the second Combo Box. And, if the user doesn't enter a County, it will still work, as there is a default RowSource for the second Combo Box.

You can download a sample database from here that has three combo boxes together. In this sample, I use the GotFocus event of the combo box to set the RowSource, as it is possible that the user may not select from one of the combo boxes.

Top

Last modified at 06/06/2006 14:54:05