Access Audittrail

[andrew.abaye]

Good Morning Folks,

I have implemented an Audit Trail for my Access Database following an example I saw on Tech Republic. It is working well for other forms except for my payroll form. It gives an error: Operation is not supported for this type of object. Number" 3251 as attached even though the data get saved in the Audit Trail Table. I have also posted my VBA codes here:
Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)
' Track changes to data.
' recordid identifies the PK field's corresponding
' control in frm, in order to identify the record.

Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String

On Error GoTo ErrHandler

' Loop through each control in the form.
For Each ctl In frm.Controls
With ctl
' Check if the control is a TextBox, CheckBox, OptionGroup, Currency, or Number.
Select Case .ControlType
Case acTextBox, acCheckBox, acOptionGroup, acComboBox, acCalculated, acCurrency, acNumber
' Check if the value has changed from the old value.
If .OldValue <> .Value Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name

' Build the INSERT INTO statement.
strSQL = "INSERT INTO ztblDataChanges (EditDate, [User], RecordID, SourceTable, SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now(), " _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & strControlName & cDQ & ", " _
& cDQ & Nz(varBefore, "") & cDQ & ", " _
& cDQ & Nz(varAfter, "") & cDQ & ")"

' View evaluated statement in Immediate window.
Debug.Print strSQL

' Execute the SQL statement.
CurrentDb.Execute strSQL, dbFailOnError
End If
End Select
End With
Next ctl

' Clear the control variable.
Set ctl = Nothing
Exit Sub

ErrHandler:
' Display the error message.
MsgBox "Error: " & Err.Description & vbNewLine & "Number: " & Err.Number, vbOKOnly, "Error"

' Ensure warnings are turned back on in case of an error.
DoCmd.SetWarnings True
End Sub

 

[theDBguy]

I think the best way to troubleshoot that is by stepping through the code. Do you know how to use a Breakpoint?

 

[andrew.abaye]

I think the best way to troubleshoot that is by stepping through the code. Do you know how to use a Breakpoint?

No Please

 

[June7]

Review https://www.techonthenet.com/access/tutorials/vbadebug2010/debug01.php

 

[The_Doc_Man]

To amplify just a little, the reason you are being asked about using the debugger is because you have multiple places in your code where that error could occur, so we need to know which line of code trips the error. The debugger would allow you to know that.

However, I'll make a guess that it is somewhere in your loop that starts with "for each ctl in frm.Controls" because there is a chance that among the controls that would make it past your CASE filter, (I'm betting) one of them doesn't actually have an .OldValue property.

A place to drop a breakpoint in that loop is on the "IF .oldvalue <> .value" statement. The breakpoint will break BEFORE you execute that statement. While execution is paused in the break, you can hover the mouse cursor over variables and properties named in the code to see their values before proceeding through the breakpoint. When you hover over something that doesn't have a .OldValue, you will know it because of what the hovering pop-up tells you.

That property's absence can occur, for example, with unbound controls even if they DO have a .Value statement. However, there is another possibility, because some of the items in that CASE will probably be OK, but acNumber is not a type of control; nor is acCurrency or acCalculated. Those are data types, so the case statement does not know them as such. I'm thinking CASE actually wouldn't care, but because Microsoft enumerations restart at either 0 or 1 (depending on what is being enumerated), one of the "data type" acxxxx symbols might overlap with a type of control that would never have a .OldValue property.

 

[CJ_London]

I agree with doc - the last three are not control types

Your code is debugging the sql string - look in the immediate window - you should be able to work out how far the code has got

In addition, turn on set warnings and disable the error handler- when the error is generated it will take you to the line generating the error