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 appeneded 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)
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
Public Function GetUserID() As String
GetUserID = Environ("Username")
Private Sub RecordChange(strField As String)
'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
If strOld = strNew Then
Call LogChange(Me.txtID , "frmYourFormName", strField & " changed from " & strOld & " to " & strNew)
strOld = ""
strNew = ""
Private Sub txtYourField _Enter()
strOld = Nz(Me.txtYourField, "")
Private Sub txtYourField _Exit(Cancel As Integer)
strNew = Nz(Me.txtYourField, "")
Call RecordChange("Your Field OR a name descriptive of the field")
Private Sub cboYourField _Enter()
strOld = Nz(Me.cboYourField .Column(1), "")
Private Sub cboYourField _Exit(Cancel As Integer)
strNew = Nz(Me.cboYourField .Column(1), "")
Call RecordChange("Your Field OR a name descriptive of the field ")
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")