Audit Trail Code (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 15:51
Joined
Feb 8, 2013
Messages
121
Good Afternoon All;

I have created an audit trail procedure for an access database, for a form based on a query, unfortunately it is giving an error when running, but does record the changes to the audit table, my setup is as follows:

I have a table called: tbl_audit which contains the following fields:

Audit_id – AutoNumber
Datetime – Date/Time field with a default value set to: =Now()
User_name – Text
Form_name –Text
Field_name –Text
Original_value –Text
New_value – Text
Record_id – Number

I have a module called: Audit with the following code:

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 tblAUDIT", cnn, adOpenDynamic, adLockOptimistic
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
![form_name] = Screen.ActiveForm.Name
![record_id] = Screen.ActiveForm.Controls(IDField).Value
![field_name] = ctl.ControlSource
![original_value] = ctl.OldValue
![new_value] = ctl.Value
![user_name] = strUserID
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![user_name] = strUserID
![form_name] = Screen.ActiveForm.Name
![Action] = UserAction
![record_id] = 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



And on my form I have the following events set:

Before Update:

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

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

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

End Sub


After Del Confirm:

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

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

The code does capture the changes to the audit table (tblAUDIT); but gives an error when running:

Error!
Operation is not supported for this type of object

I can’t see where this is going wrong, your help as always is appreciated.
Kind Regards
Tor Fey
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:51
Joined
Jul 26, 2013
Messages
10,354
Could you edit that and put it in code tags please? (The # control in the editor)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
Temporarily comment out the "On Error..." line and run the code. You should be given the option to debug when you get the error, and see what line is throwing it.
 

isladogs

MVP / VIP
Local time
Today, 15:51
Joined
Jan 14, 2017
Messages
18,186
Also, please add the error number to the error handler code and say what error number you get.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:51
Joined
Jan 23, 2006
Messages
15,364
See this post for a modified version of the Martin Green Audit material that works with form and subform and includes a sample.

If you want to see the dialog that led to this, see this thread, specifically the issues identified by PSSMargaret.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:51
Joined
Feb 28, 2001
Messages
26,999
I'm going to make a side bet on this one.

If you do as Paul (PBaldy) asks, it will highlight this line:

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

The problem is this segment:

Code:
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

If I'm right, the error it throws comes about when one of those "For Each ctl" steps comes across a type of control that doesn't have an "Old Value" but DOES have a .Value associated with it. You need to be sure when you tag the control to be audited that it has a .OldValue associated with it. If the control happens to be unbound for any reason, it WON'T have a .OldValue even if normally it WOULD have one. And there are valid reasons why you would have an unbound control on a form, so this is not a criticism.
 

Tor_Fey

Registered User.
Local time
Today, 15:51
Joined
Feb 8, 2013
Messages
121
The_Doc_Man ;

You are indeed correct, it is error at this point with the following error code:

Code:
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

  • operation is not supported for this type of object 3251

The form I am auditing is bound to a query, and doesn't contain any unbound fields, so I am still at a loss :(

Regards
Tor Fey
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
While it's in debug mode, type this in the Immediate window and see what control is the problem:

?debug.print ctl.name
 

isladogs

MVP / VIP
Local time
Today, 15:51
Joined
Jan 14, 2017
Messages
18,186
Also you haven't specified values to use if nulls occur in
Code:
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then

Use something like
Code:
If Nz(ctl.Value,"") <> Nz(ctl.OldValue,"") Then

or perhaps
Code:
If Nz(ctl.Value,"") <> "" And Nz(ctl.OldValue,"") <>"" And _
Nz(ctl.Value,"") <> Nz(ctl.OldValue,"") Then

Also, perhaps not relevant for those controls you've tagged as "Audit", but not all controls have 'values' e.g. line; page break; rectangle (box)
That could explain the "Operation is not supported for this type of object" error
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:51
Joined
Feb 28, 2001
Messages
26,999
Paul's advice will tell you what control you want to examine. Ridders points out correctly that NZ might help protect you in certain cases. But it is most likely that a control you THINK has a .OldValue does not, in fact, have one.

It might be tedious to find it, but you can open the LOCALS window, find the form (hint: in that display, it is called Me), then find the controls (hint: The collection is called Controls.) For each control you can see the type of control that it is. If you click on the PLUS sign in the tree view, it will expand and you can then see the properties of that control as implemented. Click on the MINUS to collapse that control. Keep on expanding each control until you find the one that Paul's suggestion identified for you. Look at that control's properties carefully because you will see immediately whether it has a .OldValue in the first place.


By any chance do you have an OptionGroup control on this form? Because the option buttons don't have an .OldValue but the option group as a whole DOES. Or if you just accidentally marked a control with AUDIT when it shouldn't have been (like a label or a rectangle or a line), that would do it as well.
 

Tor_Fey

Registered User.
Local time
Today, 15:51
Joined
Feb 8, 2013
Messages
121
Good Afternoon all;

Sorry for being late getting back to you, I have been on leave.

I wanted to thank everyone who has helped on this, it was very much appreciated, I have now identified the field causing the error, it was a bound YES/NO field, which after some consideration doesn't need auditing now.

Do you think my code will work for sub forms, or is there a way to reference the sub form from the main form in my code?

Kind Regards
Tor Fey
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:51
Joined
Feb 28, 2001
Messages
26,999
If you try to generalize your code by making it a subroutine in a general module, you can cause it to audit the contents of each form based on essentially the same form event. If you are going to audit in the BeforeUpdate event (or the AfterUpdate event or the Unload event...) just use the same event in each case. Doing it that way, IF the sub form is doing something that causes an update, the event fires in the sub form. In the strictest sense of the word, the parent and sub will fire INDEPENDENTLY in the sense that two distinct events will occur - but they will fire for the same causal event so they are not totally independent. On the other hand, if it happens that the parent has no changes but the sub does, it is ENTIRELY possible for only the sub's event to fire.

Instead of using Me.Controls, you can call the subroutine with Me as the argument representing the form and have a formal argument of type Access.Form as the subroutine placeholder.

Declare via:

Code:
Public Sub AuditForm( fForm as Access.Form, etc. etc. )

...
    For Each ctl in fForm.Controls ...

Call via:
Code:
    AuditForm    Me, etc., etc.
 

Tor_Fey

Registered User.
Local time
Today, 15:51
Joined
Feb 8, 2013
Messages
121
Good Afternoon All;

I wanted to thank everyone that has helped on this, I have the code working as I expect now.

Kind Regards
Tor Fey
 

BWP

Registered User.
Local time
Today, 15:51
Joined
Jan 31, 2018
Messages
17
moved this to another new thread instead
 
Last edited:

tucker61

Registered User.
Local time
Today, 08:51
Joined
Jan 13, 2008
Messages
321
By any chance do you have an OptionGroup control on this form? Because the option buttons don't have an .OldValue but the option group as a whole DOES. Or if you just accidentally marked a control with AUDIT when it shouldn't have been (like a label or a rectangle or a line), that would do it as well.

Sorry to hijack someone else's post but I have the issue mentioned when trying to track the changes in the option group. I am struggling to select the option group as a whole to apply a tag, any advice ?
When I tag the option buttons I get error 2427. You entered a expression that has no value.

Thanks in advance.
T61


Sent from my SM-T715 using Tapatalk
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
Select the box around the options. If you didn't change it, it's named Frame1 or something like that.
 

tucker61

Registered User.
Local time
Today, 08:51
Joined
Jan 13, 2008
Messages
321
Select the box around the options. If you didn't change it, it's named Frame1 or something like that.
Thanks. Could not see the box it was hidden and no border.. sorted now.

Sent from my SM-G950F using Tapatalk
 

Users who are viewing this thread

Top Bottom