I have managed to get the code (I am using the "An Alternative Audit Trail Routine for Recording Additions, Edits and Deletes" section) supplied from https://www.fontstuff.com/access/acctut21.htm working to create an audit trail entered into tblAuditTrail, however my form includes a couple of Option Buttons that are causing me an error.
The error is "You entered an expression that has no value."
I take this is because the code looks at the .Value portion and as the Option Button is part of an Option Group, the Option Button does not have a Value, but does have a .OptionValue. Is my thinking correct?
My Option Group only has 2 Option Buttons, so I guess this might open up the methods in which we can address it by amending the code? Although I will be looking to increase this to 3 or 4 as my database develops.
Thanks.
The Code:
The error is "You entered an expression that has no value."
I take this is because the code looks at the .Value portion and as the Option Button is part of an Option Group, the Option Button does not have a Value, but does have a .OptionValue. Is my thinking correct?
My Option Group only has 2 Option Buttons, so I guess this might open up the methods in which we can address it by amending the code? Although I will be looking to increase this to 3 or 4 as my database develops.
Thanks.
The Code:
Code:
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