New Customer ID into multiple tables

[DH]

What is the best way to accomplish the following.

Table:

tblCustomers

Table Fields:

CustomerID - autonumber
FirstName - text
LastName - text

A user enters the customer FirstName and LastName on a form and hits okay to close the form and they are done.

I have 2 other tables in the database:

tblOtherTable1
tblOtherTable2

Each one of these tables also has a CustomerID field.

I would like the other 2 tables to recognize that a new CustomerID was created when the user filled out the form and insert this CustomerID value into the 2 other tables.

What is the best way to get this new CustomerID value into the other 2 tables?

Thanks for any suggestions.

DH

 

[mpastore]

No suppor for triggers in MS Access so this is more of a VBA coding issue, in the update/insert event of your form open up the 2 other tables and .addnew the new ID in.




Mike Pastore

Hats off to (Roy) Harper

 

[TnGranny]

To void using VB you can

1) Create an append query with an outer join on CustomerID from tblCustomers to tblOtherTable1. Add selection criteria on the CustomerID field in tblOtherTable1 to select records when it "is null". Append records from tblCustomers to tblOtherTable1.
2) Create a second query like above but using tblOtherTable2.
3) Create a macro which runs both of these new queries.
4) Put this new macro on the "onClose" event of the form.