Track All Changes Made To A Database

<< Click to Display Table of Contents >>

Navigation:  Design and Tables  >

Track All Changes Made To A Database

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


       If blnCheckDiff Then

         [txtTime] = Now()

         [txtuser] = [txtCurrentUser]


         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

download Your Sample Database Is Called   "Sekhar_Changes.accdb"  and can downloaded from this page Design and Tables >>

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




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.