Getting Error 91 on Audit trail

[RickHunter84]

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:

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]

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]

Hi. Has that code worked before? I don't see a Set ctl = something before the line !FieldName=.

 

[RickHunter84]

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]

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]

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]

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]

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.