Temp = RecSet!AuditTrail
Temp = Temp & "Edit" & "|" & ReturnUserName & "|" & Now() & "|"
CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID=" _
& Me.ID)
This code sets up two variables using theDimstatement, one as aRecordsetobject and
one as a variant. The reason that variant is used and not string is because initially this object
has to be loaded with a null value and a string will not take a null.
TheRecordsetobject is then set to point to the Customers table using the current record
ID. So long as the ID field is included in the data source for the form, you can refer to it
using theMeobject, even if it is not actually a field on the form.
The existing value of the AuditTrail field is loaded into the variable Temp (this is the point
at which it may contain a Null value).
The name of the Action (in this case, Edit), the user name, and the current date and time
are all concatenated onto Temp using vertical bars as separators.
Temp now holds the latest details of the audit trail plus the previous details. AnExecute
statement using an update query is then used to write back the new value of the AuditTrail
field, and theRecSetobject is closed.
You also need to add this same procedure to theBeforeInsertevent. To get to the
BeforeInsertevent, right-click the form and select Build Event. Click Code Builder in
the pop-up window and the VBE window for the form will be displayed. Click Form in the
top-left drop-down of the module window and click BeforeInsert in the top-right drop-down
of the code module. Change the action in the concatenated string to Insert instead of Edit.
Open the Customer List form in View mode and make a few changes to the data. Open the
Customers table and look at the AuditTrail field. You will see that the information is starting
to build up as to who did what and when.
You will now see why a memo field was used for the AuditTrail field and not text. Text
only allows 255 characters and this would soon be exhausted in a database with plenty of
updates. If you find you have to use a text field for the AuditTrail, do not concatenate with
the previous value—only store the current value:
Dim Temp As Variant
Temp = Temp & "Edit" & "|" & ReturnUserName & "|" & Now() & "|"
CurrentDb.Execute ("update Customers set AuditTrail='" & Temp & "' where ID=" _
& Me.ID)
One of the problems you may have noticed is that if the user deletes a record, then all of
your wonderful audit trail disappears together with the record. The way around this is not to
allow users to delete records by setting the form property “Allow Deletions” to No.
Chapter 25: Creating Audit Trails on Tables 291