Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4294
  • Last Modified:

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
0
LarsDyrby
Asked:
LarsDyrby
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now