Audit Trail

[hhzyousafi]

I am using the following code from the Fontstuff website to keep an audit trail in my application. I have been able to substitute the field names with my field names and the audit trail for new records is being maintained; however the audit trail for records I "edit" are not being maintained. Also how can I maintain the Table Name in the audit trail? The code I am using is as follows:

Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![UpdatedOn] = datTimeCheck
![UpdatedBy] = strUserID
![FormName] = Screen.ActiveForm.Name
![ActionTaken] = UserAction
![TableName] = SourceTable
![EmployeeID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![UpdatedOn] = dastTimeCheck
![UpdatedBy] = strUserID
![FormName] = Screen.ActiveForm.Name
![TableName] = SourceTable
![ActionTaken] = UserAction
![EmployeeID] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub

I have tried using "SourceTable" to get the Table Name in the audit trail table; however for some reason it isn't working the way I want it to work.

 

[pr2-eugin]

I prefer Allen Browne's Audit Log.. As it records table name. Editing someone's code is always harder, than finding something that will work as per requirement..

 

[hhzyousafi]

Thank you for the prompt response Paul. I was able to modify the code from Fonstuff and make it work as per my requirement. Allen Browne's work is awesome but perhaps it would be overkill for a small timer like me!