• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4363
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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