Linking a subform to 2 fields

[Pure Salt]

I am having a mental block.



I have the following tables - Suppliers, Products, Orders, & Order Transactions.

I have an order 'main' form which has the order date etc & chosen supplier. My subform links to the main order form by the 'order id', when I click on the combo box for the list of products it shows all products from all suppliers. How can I get it to only show the products for the supplier selected in the main form.

Probably a simple solution, but I can't think of it!!

[ranman256]

In the query connected to the combo, set the critiera to look at the form.

where [supplier] = forms!frmMain!txtSupplier

[June7]

Will possibly also need code that requeries the combobox when the supplier is selected. Also when navigating records on the main form.

Try the combobox GotFocus event.

Me.comboboxname.Requery

[Pure Salt]

Thanks, can't get it to work though.

[NTC]

well R256 & J7 advice is the way; so you are having some sort of implementation issue....

you select the Supplier in a combobox - call it ComboS

then the selection of a Product is a combobox which has a query record source and when you look at that query it should have the Product field and the Supplier field - even though the supplier field doesn't display in the combobox it must be part of its record source.....so that you can apply the filtering query i.e. Forms!FormName.ComboS

then as J7 notes - in the AfterUpdate event of ComboS you must tell the Product combobox to requery.....

[Pure Salt]

What am I doing wrong? Tried using combo box name & field name, in various different places & just can't see where I am going wrong. Having a bad day

[Pure Salt]

Blonde moment over, now working

Thanks for your help