how to use many to many join in a database

[Gary.]

The project I am working on is a PO Database
For this discussion the database will have 4 tables
tblPO
tblSupplier
tblProduct
tblSupplierProductTransition

I have setup a standard many to many join with tblSupplier,
tblSupplierProductTransition and tblProduct
What I don't under stand is how to tie them all together
What I need is a form that I can fill out the PO information then select
the Supplier and from their add the Products that I need to order
I am not sure what to do next

Any help would be apprechaited

 

[Jeanette Cunningham]

Hi Gary,
You can create a form that relates 2 tables in a many to many relationship
like this-->
tblPO, tblProduct, txrefProductPO.

txrefProductPO has the primary key from tblPO and the primary key from
tblProduct as its 2 foreign key fields.

tblPO is related one to many to txrefProductPO

tblProduct is related one to many to txrefProductPO.


With the above table set up, you can build-->
Main form based on tblPO.
Subform based on txrefProductPO.
On the subform put a combo with its row source based on tblProduct and a
combo to choose the supplier.
After user chooses the supplier, change the row source of the product combo
so that it shows only products from the selected supplier.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia