Option Button Values causing issues with Audit Trail code (1 Viewer)

Cark

Registered User.
Local time
Today, 07:22
Joined
Dec 13, 2016
Messages
153
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:

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
 

June7

AWF VIP
Local time
Today, 06:22
Joined
Mar 9, 2014
Messages
5,425
Which is the OptionGroup control? Which line throws the error?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:22
Joined
Jul 9, 2003
Messages
16,245
Just an observation, I don't think that audit code you have there will work on subforms , might be worth checking out if you intend to add subforms to your database.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:22
Joined
Jul 9, 2003
Messages
16,245
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?

Yes you're on the right track, the solution is to remove "Audit" from the Tag of your "Option Buttons" and only have "Audit" as the Tag on the "Option Group". You don't need the "Audit" Tag for each Option Button, just the "Option Group" Frame itself.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:22
Joined
Jul 9, 2003
Messages
16,245
Also your option group frame must be linked to a field in a table. Otherwise it won't show any changes because in effect, no change takes place!
 

Cark

Registered User.
Local time
Today, 07:22
Joined
Dec 13, 2016
Messages
153
Thanks all. Yes the next thing I need to tweak is the Sub-Forms aspect of it.

Thanks for the reference link and hopefully I should be able to tweak it without any further issues.

I had completely forgotten that Option Groups are treated (believe it or not) as a Group...
 

Users who are viewing this thread

Top Bottom