Hi William,
Ok, glad you're making progress.
We need to add one more field to the audit trail table which will be a lookup field to the ID value of the first table. We will use this to relate all changes made back to its original record.
So if your first table has ID as the primary key, create a new field in the audit trail field called something like Table1ID (name as you see fit) and select the Lookup field option. In the lookup wizard dialog select the first table, choose any display value you want but you can just select the ID field from the first table as your display value too. Save those changes now to the audit trail table.
There is no need to have an old and new time stamp field in the audit trail table per your question from the last post.
Now we need to define the data macro logic to record any data changes.
Follow these steps.
1. Open the master table in design view. In my example, it is Table1.
2. Click the On Update button in the Ribbon to define logic for this event. Access will fire this logic whenever data is updated in Table1. Access will now open the macro design window.
3. Essentially what we want to do (in English) is the following:
- Set a local variable to the ID value of the current record. We will push this value into the lookup field of the audit trail table to relate it back to this one.
- Set local variables to the old value and new values of each field in the master table. You use the OLD property to capture the value before it was changed.
- Create a new record in the audit trail log and use the SetField action to record all of this data into the appropriate tracking fields.
- The time stamp field in the audit trail table will be recorded automatically using the Now() default value so no need to write anything to that field in the audit trail log.
Does all of that make sense?
4. OK, let's get started.
I've attached a screenshot of what the data macro logic will look like to the bottom of this post. Take a few minutes and just study this to understand the macro logic in place and it how it relates to my explanation above. Note that I've added in extra macro comments to further explain. Keep in mind here that my field and table names are for just an example. You'll have to adjust for your specific app.
5. You can either type/input that into the empty macro window manually or just copy and paste the below logic I have posted here as an example to get you started and see how it should look. If you start with my example logic, just adjust the table and field names appropriately. To copy and paste this logic, do the following:
- Copy the logic below (it is in XML format) to your clipboard. Be sure you start exactly with < character below and the ending > character and copy that all to your clipboard.
- Click on the macro design surface away from any other objects - just click the empty white surface so your focus is there.
- Now press CRTL+V to paste all that macro logic from your clipboard onto the macro design window.
- It should just all appear now magically in your window. Let me know if this step doesn't work.
Here is the logic:
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro Event="AfterUpdate"><Statements><Comment>Record all changes to the audit log table.</Comment><Comment>Grab the ID value to relate it back using the lookup field.</Comment><Action Name="SetLocalVar"><Argument Name="Name">varRecordID</Argument><ExpressionArgument Name="Value"><Expression><Original>[ID]</Original><Identifier Name="ID"/></Expression></ExpressionArgument></Action><Comment>Set local variables to the old and new values in each field.</Comment><Action Name="SetLocalVar"><Argument Name="Name">varField1Old</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Field1]</Original><Identifier Name="Old.Field1"/></Expression></ExpressionArgument></Action><Action Name="SetLocalVar"><Argument Name="Name">varField1New</Argument><ExpressionArgument Name="Value"><Expression><Original>[Field1]</Original><Identifier Name="Field1"/></Expression></ExpressionArgument></Action><Action Name="SetLocalVar"><Argument Name="Name">varField2Old</Argument><ExpressionArgument Name="Value"><Expression><Original>[Old].[Field2]</Original><Identifier Name="Old.Field2"/></Expression></ExpressionArgument></Action><Action Name="SetLocalVar"><Argument Name="Name">varField2New</Argument><ExpressionArgument Name="Value"><Expression><Original>[Field2]</Original><Identifier Name="Field2"/></Expression></ExpressionArgument></Action><Comment>Create a new record in the audit log table using these variable values.</Comment><CreateRecord><Data><Reference>Table1AuditTrail</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">[Table1AuditTrail].[Table1ID]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varRecordID]</Original><Identifier Name="varRecordID"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Table1AuditTrail].[Field1Old]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varField1Old]</Original><Identifier Name="varField1Old"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Table1AuditTrail].[Field1New]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varField1New]</Original><Identifier Name="varField1New"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Table1AuditTrail].[Field2Old]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varField2Old]</Original><Identifier Name="varField2Old"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Table1AuditTrail].[Field2New]</Argument><ExpressionArgument Name="Value"><Expression><Original>[varField2New]</Original><Identifier Name="varField2New"/></Expression></ExpressionArgument></Action></Statements></CreateRecord></Statements></DataMacro></DataMacros>
6. Once you adjust the logic for your field and table names, save the macro changes. If you make a mistake with field and table names, Access will display an error message when you try to save the macro logic and inform you that it could not find something. You won't be able to save until you correct everything.
7. Save the master table after you saved the macro changes.
8. Now test it out by switching to datasheet view for the master table right within client and update an existing record. After you make a change and save it, open up the audit trail table and verify a new record was created there and it displays the old and new values from each field.
That should do it. Let me know how it goes and if you run into any issues.
--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior SDET - Access Test Team - Microsoft Corporation
Author -
Microsoft Access 2013 Inside Out
Author -
Microsoft Access 2010 Inside Out
Co-author -
Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info:
http://www.AccessJunkie.com
----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
