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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
It would help to know what the "operation" is, that is, where in the code the error comes up.
 

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
I put in the paragraph that it shows when going to a new record or when exiting out of the form.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880
Have you by chance completed a tag for a control that cannot have a Value.?

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
                        ![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

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
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880

ADVERTISEMENT

From anothe forum where someone was asking the same thing, perhaps check the control type?
Code:
For Each ctl In Me.Controls
   Select Case ctl.Type
     Case acTextBox, acListBox, acCheckBox, etc.
       If ctl.ControlSource <> "" Then
         If ctl.Value <> ctl.OldValue Then
'          this control has changed
         End If
       End If
     Case Else
   End Select
Next ctl

HTH
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
I put in the paragraph that it shows when going to a new record or when exiting out of the form.
I've looked 3x and cannot see where you've indicated what line in what procedure it fails on. Maybe
Screen.ActiveForm.Controls(IDField) should be Screen.ActiveForm.Controls("IDField") as I've never seen a .Controls("myControl") style of reference that worked without quotes.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
439
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Maybe Screen.ActiveForm.Controls(IDField) should be Screen.ActiveForm.Controls("IDField") as I've never seen a .Controls("myControl") style of reference that worked without quotes.

It looks like it's a variable:

VBA Code:
Sub AuditChanges(IDField As String, UserAction As String)
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
I agree because it's defined in the function as such. However, I think it would be = Screen.ActiveForm.Controls(" ' " & IDField & " ' ")
(I have included extra spaces so the single quotes can be seen easier)
or similar. I've used the Controls collection syntax before with variables and it required concatenation. Maybe not exactly as I have it, but similar.
 

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
I've looked 3x and cannot see where you've indicated what line in what procedure it fails on. Maybe
Screen.ActiveForm.Controls(IDField) should be Screen.ActiveForm.Controls("IDField") as I've never seen a .Controls("myControl") style of reference that worked without quotes.
Sorry I misunderstood what you were asking originally, which is what my response was to. It is not showing me what line. I am guessing that has to do with the error handling that was put in the code. I will try and comment those out for the time being and see if it will reload with the debug mode.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,906
Messages
5,621,581
Members
415,846
Latest member
nigeywigey

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