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.