How to populate a combo box based on another combo box in MS Access

0
votes

I am trying to add items to a combo box based on the selection of another combo box.

I have one combo box that displays the total list of all Die numbers. Each die number has a specific number of cavities associated with it. Example Die 1111 has 4 cavities. number of cavities varies from 1 to 8. I want the second combo box to populate with number 1 to X where X is the number of cavities for that Die number.

I was thinking of perhaps doing a for loop that looks something like this.

Private Sub cmbSection_Change()

    Dim Cavities
    Cavities = Me.cmbSection.Column(0)

    For intI = 1 To (Cavities)
        cmbCavities.AddItem (intI)
Next intI
End Sub

EDIT: What i am really looking for is how to reference the number of cavities of each Die when the Die is selected in the combo box. Because combobox1.value will just return the value of the Die #. I was assuming since the combobox1 is populated with a query that returns both the die # and the number of cavities that i could reference the combobox1.cavities and how to add the items to the second combo box.

2
ms-accesscomboboxvba
What is the problem with the proposed solution? – OpiesDad
not exactly sure of exact syntax for referencing the cavities column of combobox1 or the syntax to add items to a combo box. Not very familiar with VBA. The above does not work. Just a bit of pseudocode. – Azuraith
You probably want Me.cmbSection.Column(1) to get the second column of the combobox. – OpiesDad

2 Answers

0
votes

I found the answer. I used my original method of using a for loop to populate the list. Here is the entire method. Works as intended.

Private Sub cmbSection_Change()

    ' Clears Current Items In Combo Box
    cmbCavities.RowSource = ""

    'Declaring and setting proper variables
    Dim Cavities As Integer
    Cavities = CInt(Me.cmbSection.Column(1))
    Dim intI As Integer

    ' Loop
    For intI = 1 To (Cavities)
        Me.cmbCavities.AddItem (intI)
    Next intI

End Sub
0
votes

It sounds like you are trying to achieve a cascading effect in you combo boxes. Put this on the After_Update of the first ComboBox.

Private Sub Form_AfterUpdate()

Me.SecondComboBoxID.RowSource = "SELECT CavityID, CavityName " _
                               & "FROM CavityTableName " _
                               & "WHERE DieID = " & Nz(Me.FirstComboBoxID) _
                               & "ORDER BY CavityName"

End Sub