Getting Error 91 on Audit trail (1 Viewer)

RickHunter84

Registered User.
Local time
Today, 01:28
Joined
Dec 28, 2019
Messages
85
Hello forum friends,

I hope everyone is doing fine.

I'm trying to get pass over an error I'm encountering within my module. When function is called to record the entry in the tblAuditTrail, i get an error on
![FieldName] = ctl.ControlSource - Error 91 - Object Variable or With block variable not set

any idea what could be causing this error?

Here is my code:

Code:
Public Function AuditChanges(RefID As String, UserAction As String)
On Error GoTo auditerr

Dim db As Database
Dim rs As Recordset
Dim ctl As Control
Dim UserID As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblAuditTrail", adOpenDynamic)

UserID = DLookup("[ShortName]", "[tblUser]", "[LcompanyID_FK]=" & TempVars("gtvCompanyID").Value & " and [UserID]=" & TempVars("gtvUserName").Value)

Select Case UserAction
    Case "New"
        With rs
            .AddNew
            ![DateTime] = Now()
            ![UserName] = UserID
            ![FormName] = Screen.ActiveForm.Name
            ![Action] = UserAction
            ![RecordID] = Screen.ActiveControl.Parent.Form(RefID).Value
            ![FieldName] = ctl.ControlSource
            .Update
        End With
    Case "Delete"
        With rs
            .AddNew
            ![DateTime] = Now()
            ![UserName] = UserID
            ![FormName] = Screen.ActiveForm.Name
            ![Action] = UserAction
            ![RecordID] = Screen.ActiveControl.Parent.Form(RefID).Value
            .Update
        End With
    Case "Edit"
 
            For Each ctl In Screen.ActiveForm.Controls
                If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Or (ctl.ControlType = acCheckBox) Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rs
                            .AddNew
                            ![DateTime] = Now()
                            ![UserName] = UserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveControl.Parent.Form(RefID).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![newValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
            
End Select

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

auditerr:
    MsgBox Err.Description & " : " & Err.Number
    
End Function

As always, any help or clue would be greatly appreciated.

Rick
 

Minty

AWF VIP
Local time
Today, 05:28
Joined
Jul 26, 2013
Messages
10,346
I would add a
debug.print ctl.Name

To see which control is giving you the error.
It sounds like you are referring to a control without a control source (e.g. it's unbound, or a command button etc.) hence the error.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:28
Joined
Oct 29, 2018
Messages
21,322
Hi. Has that code worked before? I don't see a Set ctl = something before the line !FieldName=.
 

RickHunter84

Registered User.
Local time
Today, 01:28
Joined
Dec 28, 2019
Messages
85
Hi. Has that code worked before? I don't see a Set ctl = something before the line !FieldName=.
The code works when is an update event but gives the error when is a new entry, what would you set ctl value to? set ctl = ?

Rick
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:28
Joined
Feb 28, 2001
Messages
26,946
Actually, that is the wrong question. It's your code... to what should it be set in that circumstance? What were you INTENDING to log? We can't answer that question - but you can.

Take a look at your code for update events and note that you have a loop over all controls and that is how ctl is set for that case. So there, your answer is "look at everything." But for the new case, what is different? (Obviously, other than being in the "new" category, what is different?)
 

RickHunter84

Registered User.
Local time
Today, 01:28
Joined
Dec 28, 2019
Messages
85
Actually, that is the wrong question. It's your code... to what should it be set in that circumstance? What were you INTENDING to log? We can't answer that question - but you can.

Take a look at your code for update events and note that you have a loop over all controls and that is how ctl is set for that case. So there, your answer is "look at everything." But for the new case, what is different? (Obviously, other than being in the "new" category, what is different?)
Thank you for that Doc :) - sometimes we need to step back and rethink.

Have a nice day!

Rick
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:28
Joined
Feb 28, 2001
Messages
26,946
No problem. Glad to help. And you are quite right. Sometimes when we get too close, we can't see the forest because all those damned trees are in the way.
 

Minty

AWF VIP
Local time
Today, 05:28
Joined
Jul 26, 2013
Messages
10,346
The code works when is an update event but gives the error when is a new entry, what would you set ctl value to? set ctl = ?

Rick

Just to clarify this, as no control is referenced I would simply use
![FieldName] ="New Record Added"

Or remove the field name from the new record adding code.
 

Users who are viewing this thread

Top Bottom