Forms: Limit content of combo/list boxes

(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.

'**************** Code Start *************
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
End Sub
'**************** Code End *************

     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 = Forms!FormName!NameOfFirstControl;

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()
    Me!NameOfSecondControl.Requery
End Sub