Many-to-Many relationship, forms & subforms

[Guest]

I am trying to set up a form so that I can apply a purchase order to a
customer. The PO should show each item that applies to that order.

There are 4 tables:
Customer ID Table - Customer ID #, Name, address, etc.
Item Table - Item #, Product Name, Unit Price
Purchase order Table - Purchase Order No, Customer ID
Purchase Order Details Table - PO Details ID, PO No, Item No., Quantity

There are 2 queries:
Order With Customer Info - PO No., Customer ID
Order With Item Info - Item No, Product Name, Unit Price, Quantity

There is a Form and a subform:
Form - Customer Info with PO No.
Subform - PO Details ID, Item#, Product Name, Unit Price, Quantity

When I try to add a record to the form, this is the response:
"You cannot add a record because a related record is required In the
Purchase Order Table."
I am not sure what it it looking for. Does anyone know?

Ronnie

 

[Jerry Porter]

Ronnie,

This means you have defined a relationship between the PO table and the
PO Details table that won't allow a record in the details table unless
the PO No is found in the PO table. It appears your form is not making
sure the detail data is created correctly.If your subform has the Link
Child Fields and Link Master Fields set to the PO from each table, this
should be automatic.

Note: I would think you would want the PO Details data to include the
Unit Price, copied from the Item table. Otherwise, when you change your
unit price, it will affect all your past orders.

Jerry Porter
Personable PC Solutions

 

[Guest]

Thank you. That worked. Now I just have to figure out how to prepare the
report. Should I prepare it from the queries?

 

[Jerry Porter]

It all depends what you want in the report. You might need to develop a
separate query to be the source of your report.

Jerry