Access: Combobox Values Depend on Previous Combobox

Question

So I have two comboboxes, Combobox1 and Combobox2. If the user chooses "Apple" for Combobox1, I want the values for Combobox2 to be "Sauce" and "Seeds".

Likewise, if a user picks "Blueberry" for Combobox1, i'd like Combobox2 values to choose from be "Pie" and "Cobbler".

I'm having touble figuring out how to make the values for the second combobox based on the first choice. I think it would be something like this...

Private Sub Combobox1_Change()
    If Combobox1= Apple Then
        Combobox2.AddItem "Sauce"
        Combobox2.AddItem "Seeds"
    End If

    If Combobox1= BlueberryThen
        Combobox2.AddItem "Pie"
        Combobox2.AddItem "Cobbler"
    End If
End Sub

The onChange event is working based on test i've done, but no matter what I pick for the first combobox, the second combobox is empty.

Best Practices VBA Troubleshooting: Set a breakpoint (F9) and then Single Step (F8) — user2261597
Put Option Explicit at the top of each module. It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor. — Andre

HansUp HansUp · Accepted Answer · 2019-04-10T15:18:23

The combobox AddItem method will add an item to the combo's ValueList. But I doubt that is what you really want. If the user selected "Apple" in the first combo and then went back and selected "Blueberry", I suspect you want the second combo to contain only the "Blueberry" choices, not both the "Apple" and "Blueberry" choices.

Avoid that by altering the ValueList property directly ...

Option Compare Database
Option Explicit

Private Sub Combobox1_AfterUpdate()
    Dim strValueList As String

    Select Case Me.Combobox1.Value
    Case "Apple"
        strValueList = "Sauce;Seeds"
    Case "Blueberry"
        strValueList = "Pie;Cobbler"
    Case Else
        MsgBox "What should happen when selection = '" & Me.Combobox1.Value & "'?"
    End Select
    Me.Combobox2.RowSource = strValueList
End Sub

Notes:

Use the first combo's AfterUpdate event because its value has been finalized at that point.
Include Option Explicit as Andre suggested. Don't write VBA code without it.
Consider storing your fruit names and options in a table and using queries instead of value lists for the combobox row sources. Then when you need to change the available choices, edit the data in the table instead of revising VBA code.

Access: Combobox Values Depend on Previous Combobox

1 Answers