Linking 2 combo boxes

Question

I would like to know the best way to create a combo box which is linked to the selection of another combo box. For example, in combo box number 1 selects 'fruits', the options in combo box number 2 are mango, orange and kiwi.. When the user selects in combo box number 1 'vegetables', in combo box number 2 the options are carrot, artichoke, and tomato. Both combo boxes should be linked to the same table called Produce.

I don't have trouble building the query to support combo box number one, but don't understand how I can link what is selected to the query supporting combo box number 2.

Marek Stejskal Marek Stejskal · Accepted Answer · 2015-04-25T20:05:18

There are several ways how to do this, but the easiest one and the one I use mostly is setting a different Row Source to the Combobox2 when the Change event (or AfterUpdate, depending on your needs) of Combobox1 is fired.

Example:

I have two tables

Animals
1  Dog
2  Cat
3  Mouse
4  Rabbit

Cars
1  Audi
2  BMW
3  Ferrari
4  Porsche
5  McLaren

On the form I have two comboboxes, the second one is based on the selection of the first one, which contains just two options: Animals, Cars.

Sample code:

Private Sub Combo1_Change()
    Dim cmb1 As ComboBox: Set cmb1 = Me.Combo1
    Dim cmb2 As ComboBox: Set cmb2 = Me.Combo2

    Select Case cmb1.Value
        Case "Animals"
            cmb2.RowSource = "Animals" ' Table name Animals
        Case "Cars"
            cmb2.RowSource = "SELECT TOP 3 * FROM Cars" ' SQL command to table Cars
        Case Else
            cmb2.RowSource = "Animals"
    End Select
End Sub

Now each time the value in Combo1 changes, so does the rowsource of Combo2.

Note: You need to set a default rowsource of Combo2 based on the value in Combo1 on form load, so the Combo2 is not empty on start.

Linking 2 combo boxes

1 Answers