How to get a ComboBox to return the fields relevant to the row I am working on.

[Bob Vickers]

I have a subform called “subfOrders” in dataset view showing the orders related to a customer. One field is CategoryID and a second is Sub-CategoryID. Both of these fields are combo boxes and the Category is set from a Catagories table and the Sub-category from a Sub-Catogories table.

I want the Sub-Catagories combo box to show only fields relevant to the Category in that row.

In the query behind the Sub-category ComboBox I have the following as the criteria to identify the appropriate category: - [subfOrders]![CatagoryID].

This picks up the CategoryID in the first row of the table or the first row I click on (but that bit does not seem a consistent). Once it has got this ID it does not change.

How can I set up the query so I always get the CatagoryID of the row I am working on returned?

 

[apr pillai]

The underlying Query Criteria value ([subfOrders]![CatagoryID].) is not getting refreshed to filter the values for the second Combobox.

Add a Me.Refresh statement on the Form_Current() Event procedure:

Private Sub Form_Current()
    Me.Refresh
End Sub