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
       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
     Do Until rstMatched.EOF
       Call LogChange(rstMatched![ID], "frmYourForm", "Your Message Goes Here")
End If
Tips (Main)
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Record Deletions
Check for Duplicate Values
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
Looping Records to Send eMail
Check if Table Exists
Insert (or Remove) Blank Line
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.