Track all changes made to a record in Microsoft Access

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

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

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