2011-09-13

Microsoft Access 2010 - Record level edit tracking fields

This code works great in order to add some tracking fields to updates made to a Microsoft Access 2010 table. It calls a function through a DLOOKUP into a view (vgetUserID), that returns the windows network ID of the logged in user.


xml version="1.0" encoding="utf-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
       <DataMacro Event="BeforeChange">
              <Statements>
             
             
                     <ConditionalBlock>
                           <If>
                                  <Condition>IsNull([Old].[RecordInsertUserID])Condition>
                                  <Statements>
                                         <Action Name="SetField">
                                                <Argument Name="Field">RecordInsertUserIDArgument>
                                                <Argument Name="Value">DLookUp("UserID","vgetUserID")Argument>
                                         Action>
                                  Statements>
                           If>
                           <Else>
                                  <Statements>
                                         <Action Name="SetField">
                                                <Argument Name="Field">RecordInsertUserIDArgument>
                                                <Argument Name="Value">[Old].[RecordInsertUserID]Argument>
                                         Action>
                                  Statements>
                           Else>
                     ConditionalBlock>
                    
                    
                     <ConditionalBlock>
                           <If>
                                  <Condition>[RecordInsertTimestamp]<>[Old].[RecordInsertTimestamp]Condition>
                                  <Statements>
                                         <Action Name="SetField">
                                                <Argument Name="Field">RecordInsertTimestampArgument>
                                                <Argument Name="Value">[Old].[RecordInsertTimestamp]Argument>
                                         Action>
                                  Statements>
                           If>
                     ConditionalBlock>
             
             
                     <Action Name="SetField">
                           <Argument Name="Field">RecordEditTimestampArgument>
                           <Argument Name="Value">Now()Argument>
                     Action>
                     <Action Name="SetField">
                           <Argument Name="Field">RecordEditUserIDArgument>
                           <Argument Name="Value">DLookUp("UserID","vgetUserID")Argument>
                     Action>
                    
                    
              Statements>
       DataMacro>
DataMacros>

No comments:

Post a Comment