how to append a record to a archive table after a change in acces.

B

BloaTEDboB

Im creating a batch record tracking DB and i need to create a Previous
locations table where the previous locations of the batch record is recorded,
how do i append the new record to the histoty table automaticlly after i have
changed it so i can track its movement.
 
A

Allen Browne

Access does not handle this kind of logging for you. If all changes are made
through the form, you can use the events of the form to achieve it.

Use the AfterUpdate event of the form to write the record to your history
table. Unfortunately, the OldValue is no longer available at that time, so
you will need to save the OldValue to a variable in the BeforeUpdate event
of the form so you can access it in Form_AfterUpdate.

Example:
1. In the General Declarations section of the form's module (at the top,
with the Option statements), enter:
Dim bHasMoved As Boolean
Dim vOldLocation As Variant

2. In the Form_BeforeUpdate event:
If Me.Location = Me.Location.OldValue Then
bHasMoved = False
Else
bHasMoved = True
vOldLocation = Me.Location.OldValue
End If

3. In the Form_AfterUpdate event:
Dim strSQL as String
If bHasMoved Then
strSQL = "INSERT INTO MyHistoryTable ...
dbEngine(0)(0).Exexcute strSQL, dbFailOnError
bHasMoved = false 'reset the variable.
End If

If you are not sure how to get the SQL string, mock up a query using some
literal values, make it the Append query (Append on Query menu in query
design), switch to SQL View (View menu), and the generate your string to
match what you see there, but concatenating the literal values into the
string.

There is a comprehensive example of code that uses these form events to log
inserts, and deletions, as well as edits in this link:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
 
Top