Link my tables the right way

[Greg]

I have three tables
tblCatagory
tblVendor
tblProduct
I would like to filter the products on frmSubInvoice like
cmboCatagory will filter
cmboVendor will filter
cmboProduct

at the moment i have tblCatagory and tblVendor linked to tblProducts
with their Primary keys
hope this makes sense

thanks in advance
Greg

 

[KARL DEWEY]

Do Products fall into Catagories or do lVendors fall into Catagories?

Are do you have some Catagories for Vendors and other Catagories for Products?

 

[Tim Ferguson]

I would like to filter the products on frmSubInvoice like
cmboCatagory will filter
cmboVendor will filter
cmboProduct

You can google for "Cascading combo boxes" to get (lots of) complete
solutions. In brief, you

- set the first combo's RowSource to
SELECT DISTINCT Category
FROM Products
ORDER BY Category
.... and set its AfterUpdate event to call a cmboVendor.Requery method

- set the second combo's Rowsource to
SELECT DISTINCT Vendor FROM Products
WHERE Category = Forms!frmSubInvoice!cmboCategory
ORDER BY Vendor
.... and set its AfterUpdate event to call a cmboProduct.Requery method


- set the third combo's RowSource to
SELECT ALL ProductID
FROM Products
WHERE Category = Forms!frmSubInvoice!cmboCategory
AND Vendor = Forms!frmSubInvoice!cmboVendor
.... and set its AfterUpdate event to do something else useful.

You may want to Join these queries on to the tblCategories and tblVendors
tables, so that the users see the real english names rather than the FK
codes. In any case, this should show you what you are trying to aim for.

Hope it helps


Tim F