I am currently following this guide on how to create an audit trail system for my database.
http://www.fontstuff.com/access/acctut21.htm
I am not too knowledgable with VBA and macros. My current hangup is the attached dialog box.
Now, the guide tells me this:
IMPORTANT: The example shows "EmployeeID" as the parameter value. Change this to the name of the field that identifies the current record, usually the Primary Key field although you can use any field that uniquely identifies the record.
I have Call AuditChanges("ID") set to the form's BeforeUpdate parameter.
All of my tables have ID as the primary key set to autonumber. I am under the assumption this should monitor all of the fields named ID in these tables. So I rename EmployeeID to ID, but get the same error with the updated table name.
The guide also says to compile the following to check for errors.
Code:
Sub AuditChanges(IDField 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")
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
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
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 did so, nothing happened. Now the option is greyed out.
Any help would be much appreciated.