Subform Query Criteria is Carrying over to New Records

Question

I have a invoicing database that has a Main and Subform. The main form has the customers info as well as invoice ID and some Misc Info.

Some needed info: on my main form I have a Combo-Box that I select the customer and their ID# from. Then in the query I have another Combo-Box that limits its selection based on Customer ID with the following code:

[Forms]![Invoice]![Customer_ID]

I do this because my customers all have different pricing for the same products. and this criteria allows me to only select that customers pricing. My issue is that when entering multiple invoices, it is carrying over the query from the previous invoice.

Example: I process a invoice for customer# 2 and it shows customer #2 pricing in subform Combo-Box, hit create new record, enter customer# 3 for the next invoice, however customer #2 pricing still shows.

Cascading combobox is common topic. You have to Requery the combobox in some event. Try combobox GotFocus. fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html — June7

Andrew Henry Andrew Henry · Accepted Answer · 2019-02-27T16:17:14

Here's How I fixed it thanks to June7's comment. I removed the Query from the Row Source and modified it to work in VBA on AfterUpdate on the Customer ID ComboBox. Here's what my code looks like.

Private Sub ComboBox1_AfterUpdate()
    Me!Subform1.Form.ComboBox2.RowSource = "SELECT Data " &_ 
        "FROM Table " &_ 
        "WHERE Criteria;"
End Sub

Subform Query Criteria is Carrying over to New Records

1 Answers