By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Data entry to multiple tables

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.