Data entry to multiple tables

[Mike]

I need to enter data into multiple tables from the same field in one
form. Is this possible? If so, how do I accomplish it.
Example: I need to enter changes to a customer master table (name,
address, city, state, etc) but I need to have a historical table that
keeps track of the past history of the account. So when I edit and
update the customer master information, from a form, I want that
record to be entered into the historical table, as well, as a new
record, without having to retype the same information into different
fields.

Thanks for the help.

[TC]

You could use an INSERT INTO sql statement to copy the current (unchanged)
record from the form's base table, into the history table:

(untested)

dim db as database, sql as string
sql = "INSERT INTO tblHistory (CustID, CustName, CustDOB)" & _
" SELECT CustId, CustName, CustDOB" & _
" FROM tblFormsBaseTab le" & _
" WHERE CustId = """ & me![CustId] & """"
set db = currentdb()
db.execute sql, dbfailonerror
set db=nothing

The problem is, where to put that code?

Form_BeforeUpda te is too early, because the update might be rejected with a
missing required value, or whatever. Form_AfterUpdat e is too late, because
by then, the changes have been copied into the form's base table - Catch 22!

I would be inclined to put it in BeforeUpdate, but include a "pending" flag
in the history record. Then you could erase that flag from AfterUpdate. This
isn't bulletproof - it does open you to the small risk of the system failing
before you erase the pending flag - but it does have the virtue of
simplicity.

HTH,
TC
"Mike" <mi********@dey .com> wrote in message
news:3e******** *************** ***@posting.goo gle.com...

I need to enter data into multiple tables from the same field in one
form. Is this possible? If so, how do I accomplish it.
Example: I need to enter changes to a customer master table (name,
address, city, state, etc) but I need to have a historical table that
keeps track of the past history of the account. So when I edit and
update the customer master information, from a form, I want that
record to be entered into the historical table, as well, as a new
record, without having to retype the same information into different
fields.

Thanks for the help.