What query to filter dropdown on subform

[sstiebinger]

Thank you for reading my post, I'm still pretty new to Access so I appreciate your help.

Setup:
I have 3 relevant tables...


Customer
Product
Customer Product Join Table

I have a customer form with a subform for detail of products that the customer has purchased (the join table), and the user can also add additional items to the customer via that sub form.
My products table has a checkbox legacy/not legacy.

I want to design a query to populate the sub form products field such that if they add a new entry the only options presented are items where tblProducts.Legacy = False.
However, if a customer owns a legacy product (Quantity > 0) display that product in the sub form...

Hopefully I'm explaining this properly... Please let me know what further details you need, or if you need example tables/forms.

[CJ_London]

something like

SELECT ProductPK, ProductName 
FROM tblProducts
WHERE Legacy=False OR ProductPK IN (SELECT ProductPK FROM tblProducts AS P INNER JOIN tblCustProdJoin ON P.ProductFK=tblCustProdJoin.ProductFK WHERE P.Legacy=True AND tblCustProdJoin.CustomerFK=[CustPK])

[CustPK] refers to a control on your form which contains the CustomerPK

[June7]

Code in the combobox GotFocus event to set the combobox RowSource property and requery the combobox.

Me.comboboxname.RowSource = "SELECT * FROM Products" & IIf(IsNull(Me.comboboxname), " WHERE Legacy=False", "") & ";"
Me.comboboxname.Requery

[sstiebinger]

Thanks for the replies...

After discussing the case use for this combobox, and multiple different scenarios, we decided that it's actually preferable to show all items, regardless of legacy.