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

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
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.
VBA Code:
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
VBA Code:
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

VBA Code:
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

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It looks like it fails at
VBA Code:
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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
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

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Sep 2, 2008
Messages
290
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,038
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Active Member
Joined
Sep 2, 2008
Messages
290
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

Active Member
Joined
Sep 2, 2008
Messages
290
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,100
Messages
5,622,710
Members
415,922
Latest member
gemmatay88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top