I am trying to have Access create multiple records when I input contract number in ticket table.

table1 - Contract
table2 - Contract deductions
table2 - Tickets (contains contract Number)
table3 - Ticket deductions

I need all contract deduction records for the contract number entered in the ticket to be created in the ticket deductions table.

Each ticket may have a different deduction amt then the contract and there are many tickets to each contract.

I am lost and I do not know where to begin.
Would I use an afterupdate event on the ticket when I enter the contract number?
and
then create a sql statement to read the contract detail records and add them to the ticket detail.
Any help or direction appreciated.

Thanks

---

Reply from Richard Rost:

There are 3 ways I can think of to do this, in increasing order of difficulty / coolness:

1. Create a form and using an AfterUpdate event and some VB code populate the form fields like I do in Access 302 when a user picks a product and it populates in the order subform.

2. Use a couple SQL INSERT INTO commands. The problem is that as you enter the value in each table you'll need to DLOOKUP that value to form your relationships.

3. Use Recordsets. Manually add the records using a couple of recordsets. The most difficult to program, but produces the best results.

Option 1 is generally the easiest to implement. Just set up the form (and it looks like TWO subforms) and then on another form collect the data you need, make a command button to open your main form and set the form field values.

DoCmd.OpenForm "MyBigForm"
DoCmd.GoToRecord acNewRec
DoCmd.GoToControl "FirstName"
Forms!MyBigForm!FirstName = FirstNameField
etc.

This is just off the top of my head; check the command parameters yourself. I know I've covered this in a couple of classes.