There are different ways to do audit trails in Access. Up until Access 2010, these had to be implemented on the Form level. But Access 2010 has Data macros which operate on the Table level, so they will capture if someone bypasses your security (see my blog on Login Security using VBA) and makes changes in the table itself.
Setting up such an AuditTrail is a little more cumbersome but does provide a greater level of protection.
The first step is creating a table to hold the Audit Trail information. The following shows the design of this table:
The fields are pretty obvious. This table records the table and field that was changed, the RecordID (PK Value) of the changed record, the old and new value and who changed the record and when.
The next step is to open the table you want to audit in Design Mode. From the Table Design ribbon pull down the list of Data Macro events and select After Update event as shown below:
This will open the Macro Design environment where you can build the macro.
The screen shot below shows the macro commands to cover one field:
The first command is an IF command that is triggered if the field value changes. It uses the Updated function with the field name as the argument. The next step is the Create a Record command which uses the argument of tblAuditTrail. This will be the name of your Audit Trail table.
From there you add a SetField command for each field in your Audit Trail table (except for the autonumber PK, which is filled in automatically). The first field is the Tablename which is hardcoded. The next is the RecordID which is filled in by a reference to the fieldname. Then the ChangeBy field is populated with the ID of the user. In this example I am using the fOSUsername() function found at http://www.mvps.org/access (see Note). Next, I populate the FieldName, which is again hardcoded. Following are the Old and New values. I use the Old command with the fieldname as the property to capture the previous value. Then reference the tablename.fieldname. Finally, I set the ChangeDate to Now().
To place an Audit Trail on other fields, you can collapse the If and then copy and paste to add another set. Change the fieldname information as needed.
Save the macro and close the editor.
You can test by making changes to the fields you have place an audit trail on and then checking tblAuditTrail to make sure the changes were recorded.
You can also capture Deletes similarly using the After Delete event and writing all the fields to a deleted records table. Again using Create A Record and SetField for each field.
I have a sample file (see link below) that contains a table with Audit Trail and the Audit trail table as well as a form to edit the audit trail table and modules with the needed functions. Note: Both Front end and back end need to be in the same folder.
Note: If you use my Login Security techniques or some other way of capturing the current user, you may need to create your own function to replace fOSUsername(). Following is an example:
Public Function fUser()
fUser = Forms!frmLogin!cboUser
You can assign to fUser whatever you use to capture the current logged in user.
Limitation: This does not work with certain data types. An error 20342 is thrown with description The Updated function is not supported for memo, rich text, hyperlink, OLE Object, multi-value, or attachment fields. If you have one of those data types, then I would recommend using a VBA alternative (see Comments). In the case of multi-value or attachment data types, you can replace then with a child table.