Many to Many Form design

[Guest]

I am having trouble with a many to many issue. We are a Yacht charter
business and each charter order can have many products associated. Each
product may have one or many components. I have created tables as follows:

tblProducts
ProductId PK
ProductDescrip
ProductCost
ProductCategoryId

tblComponents
ComponentId Pk
ComponentDescription
SupplierId
ComponentCost
ProductCategoryId

tblProductComponents join table
ProductId CPk
ComponentId CPk
ComponentQuant

I need to be able to build a product record from one form. I have created a
main form (frmProducts) based on tblProducts and a subform
(sfrProductComponents) based on tblProductComponents. I need to sum all
components and pass that amount to ProductCost on main form. I do not
understand how to get the ComponentCost information to the subform
(sfrProductComponents).

I would appreciate any assistance for this phase of our project.

Gary

 

[Allen Browne]

Answered in m.p.a.tablesdesign

BTW, once you finally get the Orders and OrderDetails tables in place as
well, you can drop the price into the subform in a similar way to the Orders
Subform in the Northwind sample database. Take a look at how it handles the
After Update event of the ProductID combo.