Adding Multiple lines to a table using one form entry

[Rif]

Hello,
Could use some help with taking information from a form and creating
multiple data entries into a table. The tables and the forms were developed
several years ago using Access 00 and now using 07. With the current form,
we capture customer information and the product that has been purchased.
When the form was created, the company only offered one product so customerID
was a table and the product was a text box. Now we multiple products and we
need to capture that information. I have created a product ID table with a
list of the different products that we offer, now I need to get that
information to write to the database. The information should be written
like this….
If the customer has one product, the entry should look like this.
Customer A Name Product A

If they customer has multiple products, then the entry should have multiple
lines:
Customer A Name Product A
Customer A Name Product B
Customer A Name Product C

All this information should be captured by filling out one form.
Is this possible?

 

[Beetle]

You have a many-to-mant relationship between Customers and
Products, so you need a third (junction) table to define the relationship.
A simplified example;

tblCustomers
**********
CustomerID (Primary Key)
FirstName
LastName
other attributes of the Customer

tblProducts
********
ProductID (PK)
ProductName
ProductPrice
other attributes of the Product

tblPurchases (the junction table)
*********
CustomerID(Foreign Key to tblCustomers)
ProductID (FK to tblProducts)
PurchaseDate
PurchaseQty

The junction table could use the first three fields as a composite key, or
you could add a surrogate PK like PurchaseID or something.

Then you would, for example, have a main form based on tblCustomers
with a subform based on tblPurchases (the junction table) and you
would use a combo box for selecting the Product in the subform. The
subform would display all the products purchased by a given customer.

You could also have a main form base on tblProducts with a subform
based on tblPurchases, and have it display all the Customers that have
purchased a given Product.

 

[Rif]

Thanks for the response Beetle.
The more I look into this DB, the more I see issues. This will help me get
started.