Recording a many-to-one relationship in Access 2007 forms

Question

Here's what I want to do:

The user should be able to enter a new "sale" record with some basic information (who's buying, etc.), and then add items to that sale (like a shopping cart). There's one master "Sales" table and one "SaleContents" table, that look like:

Sales: int ID, name of person buying, etc.

SaleContents: FK SaleID, Item Purchased, Quantity, etc.

How do I make a form that will allow a user to add an arbitrary number of purchased items, and then, upon saving, it:

1. Makes a Sale record
2. For each item purchased, makes a SalesContent record with a FK pointing to the above

If the user cancels, neither the Sale record nor the SalesContent record are created.

Will I have to do all of this by hand in VBA, or is there some functionality in Access to help me?

Answer 1

The simplest way is to use a bound form for the sale and a subform for the sales content, however, you will have to delete the record if the user cancels. It will be simpler if you allow cascade delete in the relationship between sale and sale content.

With a form / subform set-up the Link Master Field (which can also be a control) provides the value for the Link Child Field, in this case SalesID. The content ID can be provided with an autonumber.