Requery combo boxes in Access 2013

Question

I've created two dependent combo boxes in a form. If you choose a Category in the first box, it will filter the list of Products available in the second. I've saved the Category-Product selections to a table called Test, where Categories get one row each.

What I'd like to do is display the Product in the Product combo box if you select a Category already in Test. For example, if you select Category=Condiments and Product=Ketchup in the form, it's added to Test. Then, the next time you select Category=Condiments in the form, the Products combo box (the box where you type, not the dropdown) will immediately show Ketchup. This only seems to work if I have one row in Test. If I add more rows, ProductComboBox does not change.

Here's how my form is constructed.

In the RowSource for CategoryComboBox, I select everything

Select * From CategoryTable

In the RowSource for ProductComboBox, I filter Products based on the Category selected

Select * From ProductTable Where ProductTable.CategoryID=[forms]![FormTest]![Category]

The form source is Products left joined to Category on CategoryID.

In the On Change event for CategoryComboBox and the On Current event for the form, I requery the ProductComboBox

ProductComboBox.Requery

However, using ProductComboBox.Requery only requeries the options available in the dropdown. It doesn't change the current combo box value after selecting a Category but before selecting a Product. How do I requery data from the table linked to the ControlSource? Is there a better VBA function than Requery, or do I need to use SQL in a macro?

Note that I still need the ability to enter data using the dropdown box. — jjjjjjjjjjj

OpiesDad OpiesDad · Accepted Answer · 2016-04-21T23:32:28

In the OnChange Event of the first combobox, do the following:

 Private Sub category_Change()
      Dim myID As Long
      Me.ProductComboBox.Requery
      If Not IsNull(DLookup("ProductID", "tblTest", "CategoryID = " & me.Category)) Then
           Me.ProductComboBox = DLookup("ProductID", "tblTest", "CategoryID = " & me.Category)
      End If
 End Sub

Then to update your test table, in the event where you want to update the "default" option, put:

 CurrentDb.Execute "UPDATE tblTest SET ProductID = " & Me.ProductComboBox & " WHERE CategoryID = " & Me.Category

This assumes that "tblTest" already has a record for each category. If not, you can generate some checks and insert the category into the table.

Requery combo boxes in Access 2013

1 Answers