Saving to two tables

[Guest]

Hi! I am trying to save information in two tables and get seem to get it
right. It seems like it should be easy to do, but I can't get it to work. I
have several tables linked together, all with common fields to link by.
However, I need to be able to go and assign an Invoice# to a particular
product (the Invoice# being saved in the products table), and at the same
time, create a record with that same Invoice# in the Invoices table. The
Invoices table should contain only each Invoice# once, but the products table
could have duplicate invoice#s (thus creating a one-to-many relationship,
right?).

I have a query set up that pulls the necessary fields from each table, and I
want to be able to run the query and then assign the Invoice# from the
resulting dynaset. Is that possible? I've also tried creating a form based
on the query, but that didn't seem to help--I tried running a macro from the
Invoice# field to open the Invoices table and add a new record with the value
from that field, but it didn't work anyway. I'm not at all familiar with
code, so if that is what it takes to get this to work, please tell me and
I'll give up now!

Thanks for any help you can offer.

 

[Allen Browne]

That design won't do. Even if you did succeed, any product could only be
associated with one invoice. Over time, one would hope that you might have
multiple invoices for any product.

That means you need another table for the line items of the invoice. Fields:
InvoiceDetailID AutoNumber primary key
Invoice# relates to primary key of your Invoice table.
ProductID relates to the primary key of your Product table.
Quantity how many of this product in this row of the
invoice.

For an example of a similar structure, open the Northwind sample database,
and choose Relationships on the Tools menu. See how the Orders, Order
Details, and Products tables fit together. Then see how the Orders form
works for creating new orders, with the subform for the line items.