Track all changes made to a record in Microsoft Access

By Nirmala Sekhar

Very often, we need to audit all changes made to a record. This is useful for sensitive data or in cases where accountability needs to be established. System developers may want to provide this facility to protect their own back, especially for system options that totally change system behaviour. You need to

> Store all changes made, and
> Ascertain who made each change and when

Using the example of a student's form, we would need

> An additional table - tblStudentChanges.

> Two extra fields (Usercode and DateofChange) in both tblStudent and tblStudentChanges

> Use a password driven system or turn on Access built-in security to ascertain who the current user is.

Store all changes made

When both the tables have identical field names, this process is very simple. Use the AfterUpdate event of the form, to insert a new record in the tblStudentChanges table, whenever a change is made.

     Private Sub Form_AfterUpdate()
     Dim db As Database

         Set db = CurrentDb
         db.Execute "INSERT INTO [tblStudentChanges] " _
    	 & " SELECT * FROM [tblStudent] WHERE " _
    	 & " [tblStudent].[StudentID]=" & Me![StudentID] & ";"
         Set db = Nothing
     End Sub

Ascertain who made each change and when

In the sample database, the student form receives the usercode of the current user through the OpenArgs property. Use the FormOpen event to set it up.

     Private Sub Form_Open(Cancel As Integer)
     'if you are using Access passwords to identify
     '   current user, use CurrentUser() Access function

     [txtCurrentUser] = Me.OpenArgs

     End Sub

In the BeforeUpdate event of the form, check to see if any real changes have been made. You can use the OldValue property of controls to test for changes. If no changes have been made, simply cancel the update. If changes have been made, update the Usercode and DateofChange fields in the record.

       Private Sub Form_BeforeUpdate(Cancel As Integer)
       On Error Resume Next

        ' some controls may not have the Tag property ,
	' hence the resume next

       Dim blnCheckDiff As Boolean
       Dim ctl As Control

       blnCheckDiff = False
       For Each ctl In Me.Controls
           If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
              blnCheckDiff = True
           End If
       Next
       If blnCheckDiff Then
               [txtTime] = Now()
               [txtuser] = [txtCurrentUser]
       Else
               Cancel = True
       End If
       End Sub

Display Changes

Create a separate form based on the tblStudentChanges table. The form in the sample db displays the records in reverse chronological order, but you can change that, if needed. Make sure the form is a read-only and pop-up as well. Provide a command button to close the form and display the original Student's form again.

In the Student's form, create a command button that will display the Student Changes form for the current student. Make sure that all current changes have been saved before you open the Student Changes form.

     Private Sub cmdChange_Click()
         Dim stDocName As String
         Dim stLinkCriteria As String

         stDocName = "frmStudentChanges"
         
         DoCmd.RunCommand acCmdSaveRecord
         ' the forced save of the current record
         ' will ensure that the current changes
         ' are reflected in the new form to be opened.
         
             stLinkCriteria = "[StudentID]=" & Me![StudentID]
         Me.Visible = False
         DoCmd.OpenForm stDocName, , , stLinkCriteria
     
     End Sub

  Your Sample Database Is Called   "Changes.zip"

Sample database contains a Student Details form and a command button to view all changes made to a student's record.

If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.

 

Author Bio:

Nirmala Sekhar is a software consultant working from Singapore.

Alternatives From Garry

Rather than saving the full record to another table, you can add 2 fields to the current table such as SystemUsername (text 50) and RecordChanged (date) and log the person who made the last change to that record.  In the before update event, add the following code.   This is less onerous to manage. 

Private Sub Form_BeforeUpdate(Cancel As Integer)
      '  Log the user 
      details to the table  

  Me!SystemUsername = User_FX

  Me!RecordChanged = Now()

End sub

User_FX is a Toolbox function that retrieves the NT/Win XP user name.  This will store that person who has logged for that records.  You do not need to add the fields to the form to make this work.

Related Documents at VB123

Preparing To Upgrade Access Tables to SQL Server 2005/2008

Confirm Access Record Changes
Create Table Query and More Hidden Access SQL Queries
The Access Workbench includes full source code for the functions above