(Q) How can I limit the contents of one combo/list box based on
what's selected in another combo/list box?
(A) An easy way to do this would be to assign a dynamic SQL statment
to the RowSource of the secondary combo/list box at runtime.
Let's say you have two comboboxes, cbxCombo1 and cbxCombo2.
The RowSourceType of cbxCombo1 is set to "Field List" and RowSource to a table
Category. cbxCombo2 doesn't have anything under RowSource.
In this case, you can put code in the AfterUpdate event of
cbxCombo1 that assigns the proper RowSource to cbxCombo2.
Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!cbxCombo1
strSQL = strSQL & " from Category"
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
To filter records in a combo/listbox based on the value
selected in another combo/listbox, you can use a stored query which uses the first
control's value as a parameter. For example,
Select PeopleID, PeopleName from tblPeople Where PeopleID =
Then all you need to do is issue a Requery on the second combo/listbox in this first
control's AfterUpdate event.
Private Sub NameOfFirstControl_AfterUpdate()