Access Diva Log Field Changes

Log Field Changes...

While I tend to use Allen Browne's Audit Trail for most databases, sometimes I come across a situation where I need something a little different. This is for those situations...

2. Copy the below into a Module, remember not to name the Module the same as the Function, and save. I keep it in my modUtilities Module which is placed in my Model Database.

3. Go the Forms VB Editor that contains the fields you want to log and copy/paste the below into the VB Editor. (Make sure to change the control names to match your own!)

4. In the On_Enter and On_Exit (or GotFocus and LostFocus) of the Event Procedures of your controls place...

For a TEXT BOX...

For a COMBO BOX that has the Bound Column set to 0 (zero)...

Other ways in which you can use LogChange()...

a. Records UNDONE by User...

b. A group of records newly appended to a table...

The difficult I do immediately, the impossible takes a little bit longer.

1. Create tblLog...

Can be either TEXT or NUMERIC depending on the Data Type of the ID's you want to store.

Function LogChange(lngField As Long, strForm As String, strNotes As String)
'From http://www.access-diva.com/ 
 
     Dim strSQL As String
 
     strSQL = "INSERT INTO tblLog ( lID, lLogDate, lUserID, lNotes, lSystemForm )" & _
               " SELECT " & lngField & " AS ID, Now(), GetUserID(), '" & strNotes & "', '" & strForm & "'"
     CurrentDb.Execute strSQL, dbFailOnError
 
 
End Function

Public Function GetUserID() As String
     GetUserID = Environ("Username")
End Function

Private Sub RecordChange(strField As String)
'From http://www.access-diva.com/ 
     'This first *If Statement* only needs to be added if the field you are using to identify the record is NOT the Primary Key and can be NULL.  If it is NULL
     'no change will be logged.
     If Len(Me.txtID) = 0 Or Me. txtID = Null Then
      Exit Sub
     End If
 
     If strOld = strNew Then
       Exit Sub
     Else
       Call LogChange(Me.txtID , "frmYourFormName", strField & " changed from " & strOld & " to " & strNew)
     End If
 
       strOld = ""
       strNew = ""
End Sub

Private Sub txtYourField _Enter()
     strOld = Nz(Me.txtYourField, "")
End Sub
 
Private Sub txtYourField _Exit(Cancel As Integer)
     strNew = Nz(Me.txtYourField, "")
     Call RecordChange("Your Field OR a name descriptive of the field")
End Sub

Private Sub cboYourField _Enter()
     strOld = Nz(Me.cboYourField .Column(1), "")
End Sub
 
Private Sub cboYourField _Exit(Cancel As Integer)
     strNew = Nz(Me.cboYourField .Column(1), "")
     Call RecordChange("Your Field OR a name descriptive of the field ")
End Sub

DoCmd.RunCommand acCmdUndo
Call LogChange(Me.cboID.Column(1), "frmYourForm", "Changes were UNDONE by user")

If Not rstMatched.EOF And Not rstMatched.BOF Then
     rstMatched.MoveFirst
     Do Until rstMatched.EOF
       Call LogChange(rstMatched![ID], "frmYourForm", "Your Message Goes Here")
     rstMatched.MoveNext
Loop
End If

VBA

Tips (Main)

Home