Odds are, you use comboboxes for data entry on forms. Maybe even multiple comboboxes. There are times when a selected value in one fields should limit the available options in another field. But if you've just bound the combobox to a field in a table, the user can select a value in subsequent comboboxes.

Let's say you have a one-to-many relationship between two table, and that second table has the same relationship with a third table

Now let's say you have a unbound single main form (no subforms) with comboboxes with a main field for all three tables. Nothing would stop you from selecting unrelated items in each combobox. What you want to be able to do is to create cascading comboboxes, i.e. selecting an item in a higher level combobox filters the available items in the combobox level below it.

The easiest example is the selection of Countries, States and Cities. If you select Brazil as the country, you shouldn't be able to select Rhineland-Palatinate as the state and Beijing as the city. These fields are related.

One way to make sure that something like this never happens is to use cascading comboboxes. That is when the user selects the country, only states in that country are options in the State combobox. Then, when the user selects a state, only cities from that state are available to the user. A selection at a higher-level will limit the values in the lower level comboboxes.

The way to accomplish this is to use a query as the record source for all the comboboxes in question with the exception of the 1st level combobox. The query for the subsequent combobox will be based on a query that is filtered base on the current value of the higher level combobox.

Country combobox

All countries

State combobox

All states where country = Country combobox

City combobox

All cities where state = State combobox

We do this by changing the .RowSource property of the comboboxes, e.g.;

SELECT strState
FROM tblStates
WHERE (((tblStates.CountryFK)=[Forms]![frmDataEntry]![cboCountry]));

• CountryFK: Foreign key in our States table.
• cboCountry: Country combbox

We change it using .Requery on the AfterUpdate event of a combobox. We requery the data source of the combobox below it (since the filter will have changed due to the change in the combobox, e.g. changing the Country combobox from Australia to South Korea, means the State combobox will now contain states in S.Korea instead of those in Australia.).

When a user changes the value of a combobox, we have to be careful to set the values of lower level comboboxes to Null. Is a user had previously selected a Country-State-City and then went back to select another State, the city combobox will not show anything but the value stored in it will still be there. It won't show anything because the options available don't contain the existing city due to the change in the State combobox. We would also want to disable City, until a State has been selected.

VBA Code

Private Sub cboCountry_AfterUpdate()
    Me.cboState.Enabled = True
    Me.cboState.Requery
    If Me.chkClear Then
        Me.cboState = Null
        Me.cboCity = Null
    End If
    Me.cboCity.Enabled = False
End Sub

It's easier to understand once you've looked at the sample file. I didn't use Countries/States/Cities in the sample, but instead just used letters and numbers. And the 'levels' are just L1, L2, and L3.