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 tblFormsBaseTable" & _
" WHERE CustId = """ & me![CustId] & """"
set db = currentdb()
db.execute sql, dbfailonerror
set db=nothing
The problem is, where to put that code?
Form_BeforeUpdate is too early, because the update might be rejected with a
missing required value, or whatever. Form_AfterUpdate 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.google.c om...
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.
