Access 2010 - copy from one table to another

When I change a value in table 1 I want to automatically copy the changed record to a new line in table 2.

How is this done?

Thx in advance.

Regards
Lars
LarsDyrbyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
2010 includes the new Data Macro that can help to do this, but if you're always doing this through a Form, then you could use straight SQL and VBA.

In the Form's BeforeUpdate event, do this:

Currentdb.Execute "INSERT INTO YourTable (Col1, Col2, Col3) VALUES(" & Me.tx1 & "," & Me.tx2 & "," & Me.tx3 & ")"

If any of those are Text fields, you'll have to surround them with single or double quotes as I did below for tx1. It here are any Date fields, you'd use the hash mark (#), as I did below for tx3:

Currentdb.Execute "INSERT INTO YourTable (Col1, Col2, Col3) VALUES('" & Me.tx1 & "'," & Me.tx2 & ",#" & Me.tx3 & "#)"

If you'd prefer to use Data Macros, see this article:

http://www.databasejournal.com/features/msaccess/article.php/3905921/Leveraging-Data-Macro-in-Microsoft-Access-2010.htm
0
 
LarsDyrbyAuthor Commented:
I used the solution in the Data Macro link in the bottom of the answer.
Worked beautifully.

Thx Scott

Regards
Lars
0
 
Dale FyeCommented:
Just a side question here, why?

Unless the 2nd table is some form of audit trail, I question the necessity (or the logic in) of storing duplicate information in multiple tables.
0
 
LarsDyrbyAuthor Commented:
...which exactly is the purpose of table 2.

Regards
Lars
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.