Audit trail in Access 2013

[cdgeer]

Has anyone gotten an audit trail to work in Access 2013? I have tried Allen Brown's, a Microsoft one and one from Martin Green. I cannot seem to get any of them to work. After compiling with no errors, nothing gets populated in the Audit table.

If you have made one work please let me know so I know which one to focus on. Thanks!

 

[cdgeer]

Just to add: I tried the Martin Green Audit trail, followed the instructions to the letter and I keep getting a "Class Not Registered" error.

Here is the code I'm using:

Option Compare Database

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
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = 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
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = 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


And I'm calling the AuditChanges Sub from the following:


Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("Record_No", "Delete")

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("Record_No", "New")
Else
Call AuditChanges("Record_No", "Edit")
End If


End Sub

 

[genomon]

Couple ideas:
Have you checked a reference in the project library?
If there is a class module or add-in involved, to access its methods you may need to Dim yourObj As New yourObj Class .

Beir bua agus beannacht!

 

[cdgeer]

The instructions said to reference the Microsoft ActiveX Database Object 2.8 Library which I did. I'm not sure if there is anything else I should try.

-Carl

 

[cdgeer]

Sorry, MS ActiveX Data Object 2.8 Library