How to link Combobox between Form and Subform to only show selected filtered data (1 Viewer)

[Pauline123]

Hi, hope you can help. I have a suppliers table and a products table. Two forms, Stock form with combobox to list Suppliers and a sub form with combobox that lists Products.

I want to select a supplier from the Stock form and then the combobox in the subform to only list products directly sold by the Supplier.

Have dabbled in SQL as follows:

SELECT Products.ProductID, Products.ProductName
FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE ((Stocksubform.SupplierID=Stock.SupplierID))
ORDER BY Products.ProductName;

But this has not produced anything close to what I need.
Can someone help.
Many thanks Pauline

 

[jdraw]

Can a Product be supplied by more than 1 supplier? You may have a database structure issue.

Often there are 3 tables involved.

Suppliers
Products
ProductSupplier (known as a junction table) Identifes which Supplier "supplies" which Product.

Suppliers--->ProductSupplier<-----Products.

 

[Pauline123]

Hi, thanks for replying - No the combo references only refer to two tables though there are other tables involved in the queries for the forms. Both Comboboxes use DLookup against the two tables Suppliers and Products.

I am thinking that its the actual statement expression thats wrong -what do you think ?