Solved Audit trail error (1 Viewer)

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
Hi,
I'm setting up an audit trail for all elements in my DB. It should record when updates are made and store in tblAudit.
This is the main code:
Code:
Option Compare Database
Option Explicit

Const cDQ As String = """"
Public 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 id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  Dim user As Integer
 
  user = Forms!frmLogin!cmbstaff.Value
 
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "tblAuditTrail (fldEditDate, fldUser, fldRecordID, fldSourceTable, " _
           & " fldSourceField, fldBeforeValue, fldAfterValue) " _
           & "VALUES (Now()," _
           & cDQ & user & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

I have enclosed a very basic sample DB where I have applied this. The problem is:
a) It will not work for combo boxes or other controls which are not textboxes.
b) When executed in my main DB it will flag data mismatch error '13' - possibly due to a value in the control but have not managed to resolve.

Can anyone assist me here please?
 

Attachments

  • auditTest.accdb
    3.1 MB · Views: 338
Solution
Obviously I had trouble with the userName but I think that can be resolved with some playing around.
It seems to have a data mismatch type when a value is completely removed (it flags lines 150 and 240)?
You're going to have to supply a database of some sort to test more functionality. Please post in zip format.
Did you see and understand the tempVar to hold the StaffId?

You have to logon via your frmLogin in order to populate the tempVar -otherwise you may get an invalid use of Null in line 90.

I removed the HomePhone from 1 record
, and the street number and name from another in separate tests without issue??

tblAuditTrail tblAuditTrail

...

bastanu

AWF VIP
Local time
Today, 07:45
Joined
Apr 13, 2010
Messages
1,401
If .ControlType = acTextBox Then
You need to add to that your other control types ( If .ControlType = acTextBox Or If .ControlType = acComboBox Or If .ControlType = acCheckBox Then.
Cheers,
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:45
Joined
Feb 28, 2001
Messages
26,999
It is very rare that you see a meaningful change to a Combo Box (even if bound) since the combo box's .RowSource isn't necessarily the same as the form's .RecordSource and therefore you don't change the list of the combo box. Therefore, the question is what you wanted to capture from the Combo Box in the first place? A selection? That makes sense if the option was chosen to store the value, but not if it was used for one of the other two purposes. And of course if you have a multi-value list box then the meaning of auditing really gets hinky since you don't store the multiple choices anyway.

However, Vlad's comment is absolutely correct if that is what you REALLY wanted to do.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
Krayna,

I suggest you read through this post where user PSSMargaret had issues with the Audit Trail routine from Martin Green. It may be that you have already encountered and resolved some if the issues she had. If not, it may give you some additional info to consider.
Good luck with your project.
 

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
@jdraw Thank you for pointing me to that post - its very informative!
I have attempted to implement into my current project - which is quite highly developed so more time-consuming adjust (e.g. I would like to do without the 'audit' tags) but when I run the function it is not doing anything. After adding in debug statements in almost everywhere possible, I see it stops somewhere between lines 20 and 60. Can anyone suggest how I can resolve this?
In case you wish to see my code:
Code:
Sub AuditChanges(IDField As String, UserAction As String, pMyForm As Form)
10    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim jtmpRST As ADODB.Recordset    'a temp file to hold info from  potential DELETE record ---jed
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
20    Set cnn = CurrentProject.Connection
        Debug.Print cnn
30    Set rst = New ADODB.Recordset
        Debug.Print rst
40    Set jtmpRST = New ADODB.Recordset
50      Debug.Print jtmpRST
    '
60    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
70    datTimeCheck = Now()
        Debug.Print datTimeCheck
80    strUserID = Forms!frmlogin!cmbStaffID.Value
        Debug.Print strUserID
90    Select Case UserAction
    Case "EDIT"
        Debug.Print "ActiveForm is " & pMyForm.Name
100     For Each ctl In pMyForm.Controls
110         'If ctl.Tag = "Audit" Then
120             If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
130                 With rst
140                     .AddNew
150                     ![fldDateTime] = datTimeCheck
160                     ![fldUserName] = strUserID
170                     ![fldFormName] = pMyForm.Name
180                     ![fldAction] = UserAction
190                     ![fldrecordid] = pMyForm(IDField).Value
200                     ![fldFieldName] = ctl.ControlSource
210                     ![fldOldValue] = ctl.OldValue
220                     ![fldNewValue] = ctl.Value
230                     .Update
240                 End With
250             End If
260         'End If
270     Next ctl
        'added this code to record all values for record add ---jed
280   Case "NEW"
290     If show Then Debug.Print "ActiveForm is " & pMyForm.Name
300     For Each ctl In pMyForm.Controls
310         If ctl.Tag = "Audit" Then
                'If Nz(ctl.Value) <> Nz(ctl.OldValue) Then  '---jed No old value in a new record.
320             With rst
330                 .AddNew
340                 ![fldDateTime] = datTimeCheck
350                 ![fldUserName] = strUserID
360                 ![fldFormName] = pMyForm.Name
370                 ![fldAction] = UserAction
380                 ![fldrecordid] = pMyForm(IDField).Value
390                 ![fldFieldName] = ctl.ControlSource
400                 ![fldOldValue] = ctl.OldValue
410                 ![fldNewValue] = ctl.Value
420                 .Update
430             End With
                'End If                                      '---jed
440         End If
450     Next ctl
        'added this code to record all values for record delete
460   Case "DELETE"
        'If show Then Debug.Print "ActiveForm is " & pMyForm.Name   -for debugging
470     jtmpRST.Open "SELECT * FROM tmpAuditRec", cnn, adOpenDynamic, adLockOptimistic
480     For Each ctl In pMyForm.Controls
            'Debug.Print ctl.Name & "   " & ctl.Value 'For debugging the 438 error???
490         If ctl.Tag = "Audit" Then
                ' If Nz(ctl.Value) <> Nz(ctl.OldValue) Then  '---jed
500             With jtmpRST                  '---jed write temp audit record
                    '---jed preserve the recordID
510                 .AddNew
520                 ![fldDateTime] = datTimeCheck
530                 ![fldUserName] = strUserID
540                 ![fldFormName] = pMyForm.Name
550                 ![fldAction] = UserAction
560                 ![fldrecordid] = pMyForm(IDField).Value
570                 ![fldFieldName] = ctl.ControlSource
580                 ![fldOldValue] = ctl.OldValue
590                 ![fldNewValue] = ctl.Value
600                 .Update
610             End With
                ' End If                                      '---jed
620         End If
630     Next ctl
640   Case "DELCONFIRM"                    'jtmpRST  the temp table holds delete record values
        '
        '     The DELCONFIRM action happens if the user confirms the Delete.
        '     It moves the data from the temporary table to the real audit file.
        '     Then deletes data in the temporary table
        'my tests show it you use the AfterDelConfirm event, the current record has moved forward
        'and the record you would write to the log using Martin Green's code is the wrong record
        '
        'My finding is you need to use the Form delete event to get the correct record, save it to temp.
        'Then after confirm delete, write the temp data to the audit log; and delete the temp record

        'If show Then Debug.Print "ActiveForm is " & pMyForm.Name
650     With rst
660         jtmpRST.Open "SELECT * FROM tmpAuditRec", cnn, adOpenDynamic, adLockOptimistic
670         Do While Not jtmpRST.EOF
680             .AddNew
690             ![fldDateTime] = jtmpRST![fldDateTime]
700             ![fldUserName] = jtmpRST![fldUserName]
710             ![fldFormName] = jtmpRST![fldFormName]
720             ![fldAction] = jtmpRST![fldAction]
730             ![fldrecordid] = jtmpRST![fldrecordid]
740             ![fldFieldName] = jtmpRST![fldFieldName]
750             ![fldOldValue] = jtmpRST![fldOldValue]
760             ![fldNewValue] = ""   '              don't write new value for DELETED record  'jtmpRST![NewValue]
770             .Update
780             jtmpRST.MoveNext
790         Loop
            'Now empty the temp table since the audit log has been updated
800         CurrentDb.Execute "DELETE FROM tmpAuditRec", dbfailonerror
810         If show Then Debug.Print "tmpAuditRec  was deleted " & Now
820     End With
        '                  'write the temporary delete info to the real "audit log" ---jed
830   Case Else
840     With rst
850         .AddNew
860         ![fldDateTime] = datTimeCheck
870         ![fldUserName] = strUserID
880         ![fldFormName] = pMyForm.Name
890         ![fldAction] = UserAction
900         ![fldrecordid] = pMyForm(IDField).Value
910         .Update
920     End With
930   End Select
AuditChanges_Exit:
940   On Error Resume Next
950   jtmpRST.Close
960   rst.Close
970   cnn.Close
980   Set jtmpRST = Nothing
990   Set rst = Nothing
1000  Set cnn = Nothing
1010  Exit Sub
AuditChanges_Err:
1020  If Err.Number = 438 Then  'jed I receive 438 but it continues -ignores it/doesn't act on it??
'error 438 has disappeared after I removed an errant Tag ='Audit" on frmOrderSubform **Feb 19 2021**
1030    Debug.Print ctl.Name & "     " & "Error 438  being ignored -" & Now
1040    Resume Next
1050    MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure AuditChanges of Module ModAudit"
1060    Resume AuditChanges_Exit
1070  End If
End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
I ran your database from post #1. It appears to work for edits but not new records. Do you intend to log Deletes?
The sub function in your database is called AuditTrail. The function in post #6 is AuditChanges.
If you have a new database, please post it.

Also, when testing/developing, better to leave navigation and record selectors ON. I ran the code and updates based on record selector.

The "Audit" Tag was used to identify which field(s) to audit. Your set up should do all fields.

Some sample audit records:
tblAuditTrail tblAuditTrail

fldEditRecordID​
fldEditDate​
fldUser​
fldRecordID​
fldSourceTable​
fldSourceField​
fldBeforeValue​
fldAfterValue​
12​
21-Mar-21 8:16:34 PM​
3​
1​
tblStaff​
fldEmail​
JohnX#someemail.qqq​
JohnX#someemail.WAS​
13​
21-Mar-21 8:24:07 PM​
3​
10​
tblStaff​
fldFirstName​
Cody​
CodEE​
14​
21-Mar-21 8:24:55 PM​
3​
10​
tblStaff​
fldLastName​
Akbear​
Akbear Jr​
15​
21-Mar-21 8:25:24 PM​
3​
1​
tblStaff​
fldEmail​
JohnX#somTTTail.WAS​
Jd@KLM​
16​
21-Mar-21 8:25:56 PM​
3​
1​
tblStaff​
fldCity​
Casablanca Village​
Casablanca Village​
17​
21-Mar-21 8:27:57 PM​
3​
4​
tblStaff​
fldFirstName​
Abe​
Abel​
18​
21-Mar-21 8:27:58 PM​
3​
4​
tblStaff​
fldLastName​
Williamson​
Williamsonn​
19​
21-Mar-21 8:34:12 PM​
3​
4​
tblStaff​
fldStreet​
GreenApple Crescent​
GreenAppleCore Crescent​
20​
21-Mar-21 8:34:30 PM​
3​
4​
tblStaff​
fldPostcode​
zz3456​
zz34568776​
21​
21-Mar-21 8:39:53 PM​
3​
11​
tblStaff​
fldFirstName​
Al K​
Al K​
22​
21-Mar-21 8:39:53 PM​
3​
11​
tblStaff​
fldLastName​
Seltzer​
Seltzerr​
23​
21-Mar-21 8:39:53 PM​
3​
11​
tblStaff​
fldHomePhone​
1234567​
12345678​
24​
21-Mar-21 8:39:53 PM​
3​
11​
tblStaff​
fldMobilePhone​
5463​
546365​
 

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
Apologies for not attaching DB. I have implemented your version now so it will not be identical to version from post #1.
Can you see how it is not doing anything in tblAuditTrail?
 

Attachments

  • auditTest.accdb
    3.1 MB · Views: 333

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
I'm trying to follow your logic with respect to forms. I found this error when making a change to a record. So it seems Forms!frmlogin!cmbStaffId.value is not available within the audit routine.
I'll see if a tempVar could work.

Err2450.PNG
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
I set up a tempVar and I get some parts working. Part of your problem it seems was tryiing to deal with a form control that wasn't available --that errored out of the audit. even though tblStaff was being updated.

I am getting some audit records now (see below). I'm not sur why there are audit records for fields/controls that have no values.

How does the user of your frmStaff move from one record to another? How would they delete a record?

tblAuditTrail tblAuditTrail

fldAuditTrailID​
fldDateTime​
fldUserName​
fldFormName​
fldAction​
fldRecordID​
fldFieldName​
fldOldValue​
fldNewValue​
113​
22-Mar-21 12:13:18 PM​
5​
frmStaff​
EDIT​
2​
fldLastName​
Radomm​
Radom​
114​
22-Mar-21 12:13:18 PM​
5​
frmStaff​
EDIT​
2​
fldHomePhone​
1234​
12345​
115​
22-Mar-21 12:13:18 PM​
5​
frmStaff​
EDIT​
2​
fldMobilePhone​
45377​
4537788​
116​
22-Mar-21 12:13:18 PM​
5​
frmStaff​
EDIT​
2​
fldCity​
Verona​
117​
22-Mar-21 12:13:18 PM​
5​
frmStaff​
EDIT​
2​
fldPostcode​
Edit​
118​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldTitle​
119​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldFirstName​
Ima New​
120​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldLastName​
Record​
121​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldHomePhone​
122​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldMobilePhone​
123​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldPersonalEmail​
124​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldHouseNumber​
125​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldStreet​
126​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldCity​
Boston​
127​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldPostcode​
128​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldStaffID​
12​
129​
22-Mar-21 12:14:43 PM​
5​
frmStaff​
NEW​
12​
fldWorkPhone​
 

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
At the moment there is no option to delete records as it will only be allowed after a fixed duration of time - so it will be more automated.
User can only use the staffList form to move between records so it will not be possible to update more than one record at a time.
Maybe a clean-up process at the end of the audit procedure could remove the unnecessary rows.
Would you be able to post an updated version of my DB please?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
Yes I can attach my testing of your database. It uses Tag="audit" on textbox,checkbox, combo and listbox. I added a routine to put this tag on frmStaff controls.

I also modified your frmStaff to show Navigation controls and record selectors so I could move between records for editing etc. You know your intended logic of form flow better than I do.

It also uses a tmpVarHold the StaffID from frmLogin. It is Dimmed at the top of Form Login code
Dim tmpVarHold As TempVars 'tmp var to hold the user Login name

I have adjusted the code slightly to ensure only fields/controls whose values have changed or are NEW are logged to auditTrail.
 

Attachments

  • ForKrayna.ZIP
    189.7 KB · Views: 338

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
No worries - I isolated this from a much larger system so it probably won't all make sense from there.

It works very nicely in the test DB but when I implemented into the main DB it flags up several errors. Obviously I had trouble with the userName but I think that can be resolved with some playing around.
It seems to have a data mismatch type when a value is completely removed (it flags lines 150 and 240)?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
Obviously I had trouble with the userName but I think that can be resolved with some playing around.
It seems to have a data mismatch type when a value is completely removed (it flags lines 150 and 240)?
You're going to have to supply a database of some sort to test more functionality. Please post in zip format.
Did you see and understand the tempVar to hold the StaffId?

You have to logon via your frmLogin in order to populate the tempVar -otherwise you may get an invalid use of Null in line 90.

I removed the HomePhone from 1 record
, and the street number and name from another in separate tests without issue??

tblAuditTrail tblAuditTrail

fldAuditTrailID​
fldDateTime​
fldUserName​
fldFormName​
fldAction​
fldRecordID​
fldFieldName​
fldOldValue​
fldNewValue​
163​
22-Mar-21 7:39:21 PM​
frmStaff​
EDIT​
1​
fldHomePhone​
1234567​
164​
22-Mar-21 7:41:41 PM​
5​
frmStaff​
EDIT​
15​
fldHouseNumber​
278​
165​
22-Mar-21 7:41:41 PM​
5​
frmStaff​
EDIT​
15​
fldStreet​
Main Street​

I also did some additional testing with your sample database to get the Delete operational(see attached).

Will need more comprehensive database from you to investigate issues.
 

Attachments

  • SomeMOreAuditInfo.txt
    2.9 KB · Views: 321
Last edited:
Solution

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
I am trying to implement the audit trail into a DIFFERENT SYSTEM and having some trouble yet again!
The 'EDIT' and 'NEW' operations seem to be functional however the 'DELETE' is throwing an error which will not close or cancel. Can anyone pinpoint why this will not work?
(Again this is only a small component of my database...)

EDIT: I have continued debugging and found that the gRecID must be of type Long. However I still get stuck on line 615 - which I cannot locate!
 

Attachments

  • auditDELtest.accdb
    976 KB · Views: 298
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
I believe the issue is your use of a split form. When I try to delete a record, I get into an error loop and have to kill access to gain control.
Suggest you use a regular form and try again or wait for someone more familiar with split form events/structure.
 

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
Thanks for looking into it. However I tried the same process on a single form with similar results :confused:
I know it is a reliable piece of code - just not sure what I have missed in my implementation??
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:45
Joined
Jan 23, 2006
Messages
15,364
I haven't worked with split forms and I think this may be the issue with your attempts.
There is a setup/user manual in the zipfile here. It's been quite a while since I wrote and tested the code.
Is there special reason for using the Split form?
If you have requirements - your processing logic - and sample database (without the split form), I'll look into it.
But as I said, it's been a while so would have to "relearn/familiarize".
Good luck.
 

Kayleigh

Member
Local time
Today, 14:45
Joined
Sep 24, 2020
Messages
706
The audit trail has worked beautifully for a couple of months. However we recently realised it is not flagging checkboxes at all.
Can you help resolve this issue?
 

Users who are viewing this thread

Top Bottom