audit trail

[PSSMargaret]

Thanks for the explanation. For this database, there is no need for a users to be able to delete more than one record at a time.

 

[static]

However many records you delete, the process is the same.

 

[jdraw]

Re: audit trail- handles forms and subforms

Margaret,

Here is a revised version of the Audit trail material that now supports subforms.
It is based on the Martin Green sample, but has been adjusted to place the proper record Id on the audit log when a record(s) is deleted. It also works with subforms.

The database is in Access 2010, the document/material is in Word 2010.

This is a sample application with form and subforms that uses the revised AuditChanges subroutine to create an audit trail for New, Edit and Delete actions. Those controls on form/subform that have their Tag value = Audit, will be recorded on the Audit log.

 

[DBApprentice]

Re: audit trail- handles forms and subforms

Margaret,

Here is a revised version of the Audit trail material that now supports subforms.

I have just downloaded the file and the ModAudit is returning a compile error if you debbug: "User-defined type not definined", highlighting on

Sub AuditChanges(IDField As String, UserAction As String, pMyForm As Form)

 Dim cnn As ADODB.Connection

Do you know how to fix it or have you addressed this already? If so, can you point me the corrected file?

Regards,

 

[jdraw]

Diogo Cuba,

I have downloaded the zip and tested the database. I found the user defined error. I added a reference to deal with the ADO. Then retested the database by altering a record. I also updated the word docx file to show the References being used in the sample database.

I am attaching a revised zip file showing some new audit record @Jan 14 2019, the revised database and the revised word doc.

Good luck with your project.

UPDATE:FEB 19 2021
I have updated this zip file -database and related word document. The random error 438 reported previously has been resolved(hopefully). There was an errant Tag="Audit" assigned to a subform.

 

[DBApprentice]

Diogo Cuba,
...
I am attaching a revised zip file showing some new audit record @Jan 14 2019, the revised database and the revised word doc.
...
Good luck with your project.

Thanks a lot jdraw!

I think it is odd that he is maintaining the code there after so many people using it and many has contacted him to ask for the correction.

Does anybody here know why Martin Green has not updated his article about the Audit Trail?

EDIT: I decided to email Martin asking him to update the article and pointed this thread as a reference for the solution to the DELETE event recording the next record rather the the current record. Not sure if he wil do it, but I feel I made my part.

 

[DBApprentice]

Diogo Cuba,
...

Good luck with your project.

jdraw,

I have just tested the code on my project and it is working! I appreciate a lot the efforts you put on to make this fix possible.

I just would like to check with you if I can remove the debbuging infomation from the code. Such as:

Public gRecID As Integer
Public show As Boolean
Public Sub SetMyGlobal(SelectedOption As Integer)
10  If SelectedOption = 1 Then
20      show = True
30  Else
40      show = False
50  End If
60  gRecID = 0
End Sub

And these:

    Case "EDIT"
        'If show Then Debug.Print "ActiveForm is " & pMyForm.Name  --for debugging
...
280   Case "NEW"
290     If show Then Debug.Print "ActiveForm is " & pMyForm.Name
...
460   Case "DELETE"
        'If show Then Debug.Print "ActiveForm is " & pMyForm.Name   -for debugging
...
           'Debug.Print ctl.Name & "   " & ctl.Value 'For debugging the 438 error???
490         If ctl.Tag = "Audit" Then

I also noticed some comments that I think I can remove from my code, is that right?

310         If ctl.Tag = "Audit" Then
                'If Nz(ctl.Value) <> Nz(ctl.OldValue) Then  '---jed No old value in a new record.
 
...
 
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

 

[jdraw]

You can remove anything you want. I included debug statements that you can turn on/off since they can be helpful to follow the logic.
My sample database was developed because of the issues people were having with the example by Martin, and the difficulty with ActiveScreen ActiveForm and subforms.
I'm sure there are other audit logging examples using a similar approach with form events.

I have a different example using data macros that may be of interest or reference. It does not depend on table events.

 

[DBApprentice]

I have a different example using data macros that may be of interest or reference. It does not depend on table events.

Thanks, jdraw. I will take a look at the Data Macros. Personally, I have seen a lot of people saying we should avoid macros like the plague, but I have not studied them enough to understand the reason for such infamous.


Sent from my iPhone using Tapatalk

 

[jdraw]

Yes, I agree with avoiding macros generally, but data macros are different.
You should read the entire thread regarding the best use of macros.

 

[DBApprentice]

You can remove anything you want. I included debug statements that you can turn on/off since they can be helpful to follow the logic
...

But if I remove those specific pieces of code will I break the runtime?



Sent from my iPhone using Tapatalk

 

[jdraw]

I have no idea about runtime. I suggest you make a backup, then make a version for your specific requirement and test it. Let us all know what you find.

 

[DBApprentice]

I have no idea about runtime. I suggest you make a backup, then make a version for your specific requirement and test it. Let us all know what you find.

Ok, I will work on it and get back to you.


Sent from my iPhone using Tapatalk

 

[DBApprentice]

I have no idea about runtime. I suggest you make a backup, then make a version for your specific requirement and test it. Let us all know what you find.

Hey, jdraw!

I removed the debugging and the code is working.

After reviewing the code and trying it out on my own DB I noticed that 3 controls names were not being collected, after revising your code I realized that the isse was that you use this:

ctl.ControlSource

I changed to collect the 3 unbound controls names.

ctl.Name

Here is my (First time ever!) 2 cents on your code for the n00bs that won´t figure it out so easy why some controls are missing their names on the Audit Trail. Maybe I will get my very fiurst "thanks" too on this forum!

Unless you have a drawback by using this tweak and crush my 2 cents.


Thanks for the big help on this code!

 

[isladogs]

OK I've obliged on your blatant request for your very first thanks at AWF.
But its well deserved. I'm sure there will be many more!

 

[DBApprentice]

OK I've obliged on your blatant request for your very first thanks at AWF.
But its well deserved. I'm sure there will be many more!

Thanks, Colin!

 

[DBApprentice]

Hey Guys!

I have tried to apply this Audit Trail to form that has the go to Prev/Next records and I noticed a couple of odd things:

1. If I put the calling code on BeforeUpdate Event of the form it is being called twice, the first one when I save it and the second one when I click on the next/prev records. Sometimes it was writing the EDIT three or four times if I run into another error.

2. Since the BeforeUpdate even of the form will be triggered twice I decided to call it from the cmdSave because I don´t have Add/Delete buttons in this form I decided to use just this:

Private Sub cmdSave_Click()
    DoCmd.RunCommand (acCmdSave)
    Me.cmdUndo.Enabled = False
    Call AuditChanges("CardID", "EDIT", Me)
End Sub

It saves just once as expected, but it is raising another problem: After saving the edition and clicking on the Next/Prev buttons raises "Error 2105 - You can´t go to the specified record"

I suspect the function is making me loose the conection to the underlying query and therefore I can´t navigate trhough.

One of the thoughts I had was to move the "EDIT" part of this Audit code to the cmdSave button click event.

Does anybody have a suggestion for solving this?

Does my thinking about moving it is right?

 

[DBApprentice]

Seems I have got the code to work straight now!

I left the Call on the Before Update event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
        Call AuditChanges("CardID", "EDIT", Me)
End Sub

And I have also changed the code from the cmdSave:

Private Sub cmdSave_Click()
    ' SAVE THE RECORD
    DoCmd.RunCommand acCmdSaveRecord
    Me.cmdUndo.Enabled = False
End Sub

I am not sure if the cmdSave was messing somewhere else and raising the 2105 error.

But now all works fine as if I move next/prev records won´t write again the EDIT on the tbl2AuditTrail anymore.

It is true what some people say: the simpler the better!

Thanks!

 

[jdraw]

For anyone following this thread, and the issues with the Audit routine by Martin Green/fontstuff, and attempts at solution here and in other threads, I suggest you look at the revision by theDBGuy to the original code here..

 

[LilyK]

Hello, thank you so much for this thread, its soo good. But i have problem, it appeared "run time error: 13 type mismatch.

Here is the code
Private Sub Form_Delete(Cancel As Integer)

10 gRecID = Me.Reference_Number
20 If show Then Debug.Print Me.Name & " Form_Delete(Cancel As Integer)" & " gRecID is " & gRecID
30 If show Then Debug.Print "Calling Audit to prepare to DELETE ID =" & gRecID
Call AuditChanges("Reference_Number", "DELETE", Me)

End Sub

Reference_Number is primary key for my subform.


2. when i tried to delete record, i click yes. the record did not appear in tempAudit Rec. However when i click no, it appeared.



Helpppp

thank you in advance