Access 2013 Audit Trail VBA Error "Operation is not supported for this type of object"

[geospatial]

I have 3 tables and a form being used for this Audit Trail. After I have an update and move to another record or close the form I get the error "Operation is not supported for this type of object". To be fair even with the error the audit trail actually works fine, I just dont want to have the error show up every time we make a change. I did not write the original code. I got it from Audit Trail in Access Forms in 6 Steps - How To Create Audit Trail so there are some things I was not familiar with and why they are in the code.

tblAuditTrail, tblEmployees, and tbSections.

tblAudit Trail

• ChangeID (AutoNumber)
• DateTime (Date/Time)
• UserID (Short Text)
• FormName (Short Text)
• FieldName (Short Text)
• OldValue (Short Text)
• NewValue (Short Text)
• Action (Short Text)
• RecordID (Short Text)
• FormRecordID (Short Text)

tblEmployees

• IDEmployees (AutoNumber)
• employeeFirstName (Short Text)
• employeeLastName (Short Text)
• employeeMiddleInitial (Short Text)
• employeePhone (Short Text)
• IDSections(Number, Foreign Key)

tblSections

• IDSections (AutoNumber)
• SectionName (Short Text)

I have frmEmployees with the employeeFirstName, employeeLastName, employeeMiddleInitial, employeePhone, and SectionName fields

In a standard module I have the following VBA.

Option Compare Database
Option Explicit
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
        ![FormName] = Screen.ActiveForm.Name
        ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
        ![FieldName] = ctl.ControlSource
        ![OldValue] = ctl.OldValue
        ![NewValue] = ctl.Value
        ![UserID] = strUserID
        ![DateTime] = datTimeCheck
        ![Action] = UserAction
        ![FormRecordID] = Screen.ActiveForm.CurrentRecord
    
    .Update
    End With
    End If
    End If
    Next ctl
    Case Else
    
    With rst
    .AddNew
        ![DateTime] = datTimeCheck
        ![UserID] = 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

For Before Update I have

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo errHandler

If Me.NewRecord Then
Call AuditChanges("IDEmployees", "NEW")
Else
Call AuditChanges("IDEmployees", "EDIT")
End If
Exit Sub

errHandler:
MsgBox "Error" & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

End Sub

For After Delete and Confirm I have

Private Sub Form_AfterDelConfirm(Status As Integer)
On Error GoTo errHandler
If Status = acDeleteOK Then Call AuditChanges("ContactID", "DELETE")
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
End Sub

 

[JonXL]

It looks like it fails at

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

Likely working with a control that doesn't support a Value.

When the error throws and while it is in break mode, open the Immediate Window and type ?ctl.Name to see which control is being acted on.

As welshgasman suggested, you'll probably need to verify the control type before checking for its value.

 

[geospatial]

Likely working with a control that doesn't support a Value.

When the error throws and while it is in break mode, open the Immediate Window and type ?ctl.Name to see which control is being acted on.

As welshgasman suggested, you'll probably need to verify the control type before checking for its value.

Thanks. I didnt know about the Immediate Window thing. It is failing on Section Name...is that because it is a relationship to another table, where all the other fields are in the tblEmployees?

 

[JonXL]

It is failing on Section Name...is that because it is a relationship to another table, where all the other fields are in the tblEmployees?

What type of control is it?

 

[geospatial]

Its a Combo Box. Has 2 columns, one with the name of the Section the employee is in, and one with the SectionID, which is what is bound to the tblEmployees.

 

[Micron]

Pretty sure a combo has an OldValue property but only if bound (that's true of any data control). If not bound you can't use it. I can't recall for sure if it raises that error, but I think it does. So is the offening control bound?

 

[JonXL]

Its a Combo Box. Has 2 columns, one with the name of the Section the employee is in, and one with the SectionID, which is what is bound to the tblEmployees.

I'm just kind of guessing here, but when you get the error, you can also try ?ctl.Value and ?ctl.OldValue to see which of these is responsible for the error (it will throw the same error from the Immediate Window). If those are both good, then you can try them wrapped with Nz() (?Nz(ctl.Value) and ?Nz(ctl.OldValue)).

 

[geospatial]

In the combo box properties I do have it being bound. I do have another issue, which might be causing the overall issue. When opening the form and trying to add a new user it no longer allows me to do it and I get Cannot add record(s); join key of table 'tblEmployees' not in recordset, so I am researching that issue, to see if it would also be causing the other issue.

 

[geospatial]

I got it figured out. Somehow the control source of the combo box got changed to the tblSections instead of the IDSections field in the tblEmployees table. I appreciate everyone's help.