Tag: 2016

When a field is updated, I want a macro to add information about that change to an audit table. All I need is what changed (table, record, field), the old and new values, and the date/time of the change.

I’ve tried updating via form vs table, changing data types, creating named macros rather than using After Update, and a bunch of other tweaks, and I’ve referenced various official and unofficial documentation/forums/tutorials (including ScottGem’s “Audit Trail using Data Macros”, Access programming articles at the Microsoft support site, and several entries at databasedev.co.uk).

I’m troubleshooting this issue by using simplified data in a new, blank database to avoid leftovers from previous attempts:
tblAudit has one manually-entered record with fields for ChangeID (AutoNumber PK) and TableChanged, RecordChanged, FieldChanged, OldValue, NewValue (all Short Text), and ChangeDate (Date/Time).
tblVendors has two records of sample data over three fields (VendorID as AutoNumber PK, plus VendorName and Phone as Short Text). I added an After Update macro through the Table tab’s After Events section (below). This time around, I haven’t changed anything else.

When I change the first record’s VendorName field, I expect the macro to create a corresponding record in tblAudit, but I don’t see any changes. What am I missing?

Code formatted for legibility:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
        <DataMacro Event="AfterUpdate">
            <Statements>
                <ConditionalBlock>
                    <If>                    
                        <Condition>
                            Updated("VendorName")
                        </Condition>                        
                        <Statements>                        
                            <CreateRecord>                          
                                <Data>                              
                                    <Reference>
                                        tblRevisionLog
                                    </Reference>                                    
                                </Data>                             
                                <Statements>                                
                                    <Action Name="SetField">                                    
                                        <Argument Name="Field">
                                            tblRevisionLog.TableChanged
                                        </Argument>
                                        <Argument Name="Value">
                                            "tblVendors"
                                        </Argument>                                     
                                    </Action>                                   
                                    <Action Name="SetField">
                                        <Argument Name="Field">
                                            tblRevisionLog.RecordID
                                        </Argument>
                                        <Argument Name="Value">
                                            (tblVendors).(VendorID)
                                        </Argument>
                                    </Action>
                                    <Action Name="SetField">
                                        <Argument Name="Field">
                                            tblRevisionLog.FieldChanged
                                        </Argument>
                                        <Argument Name="Value">
                                            "VendorName"
                                        </Argument>
                                    </Action>
                                    <Action Name="SetField">
                                        <Argument Name="Field">
                                            tblRevisionLog.OldValue
                                        </Argument>
                                        <Argument Name="Value">
                                            (Old).(VendorName)
                                        </Argument>
                                    </Action>
                                    <Action Name="SetField">
                                        <Argument Name="Field">
                                            tblRevisionLog.NewValue
                                        </Argument>
                                        <Argument Name="Value">
                                            (tblVendors).(VendorName)
                                        </Argument>
                                    </Action>
                                    <Action Name="SetField">
                                        <Argument Name="Field">
                                            tblRevisionLog.ChangeDate
                                        </Argument>
                                        <Argument Name="Value">
                                            Now()
                                        </Argument>
                                    </Action>
                                </Statements>
                            </CreateRecord>
                        </Statements>
                    </If>
                </ConditionalBlock>
            </Statements>
        </DataMacro>
    </DataMacros>