audit trail

[PSSMargaret]

I had tried using data macros from ScottGem at https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/ and a few others but they required that you implement the data macros on each individual field which, like Allen Browne's audit trail, I thought was too much. To date, the databases that I've developed are relatively small and I strive for simple solutions which is why I liked Marten Green's audit log. However, even that is growing in complexity now but I'm still very thankful for your help.

If you should figure out a simpler method, I would love to learn about it. I'm still a newbie with VBA and am in the process of reading a few books. Still haven't found a great book yet. Let me know if you know one.

Thanks again. Take care.

Margaret

 

[jdraw]

I was just looking at Scotts audit trail using datamacros but I see you have seen it.

Some of these are complex because they do several things. The one by Dane, mentioned in previous post, was done for access 97-2000 and I've seen a reference that it works up to 2007.

For vba the best source I can suggest is a series of Youtube videos by Steve Bishop.

More vba on youtube by codekabinett Better vba

You never responded to my question about the
USysApplicationLog and a data macro AuditLog.AuditLogMacro
Were you aware of them in the test database? Did you create them?

 

[PSSMargaret]

Thanks for the links to the VBA code. I'll also look at Dane's post. Yes, I had tried to implement data macros and I deleted them. I guess some info remained in that table.

 

[PSSMargaret]

jdraw,

I don't think I ever responded to your other question below.

I'm still not following exactly what you are doing in this
Quote:
For curiosity sake, I clicked the Record Selector in the form for a record and hit the delete key and received this error message "The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."

I have attached a picture to show you what I'm doing. I'm selecting the Record Selector for a record and then pressing the delete key on the key board which will delete a record. When I delete a record this way, I'm getting the error message above. A user would never delete a record this way, but I was curious about the error message.

 

[jdraw]

Thanks for the picture--that clears things up a little.

It seems -and this is just a best guess on my part- that since there are some event procedures that deal with a record deletion and confirmation before and after that some of these events are invoked. But because you are deleting via a record selector and the DELETE keyboard key, some other event is triggered --I get an error on my Me.Requery (not allowed in a transaction). Now behind the scenes Access may be doing some sort of transaction logic--that's a guess and I haven't found anything to confirm or deny my guess. Perhaps another reader has more info and will share.

You could eliminate the record selectors from the Form and that may make the record selector approach a non-issue.

I see what you mean by seeing #Deleted on the form. Seems there must/should be a way to close then reopen the form with some code, such that the user is not aware of and doesn't see the #Deleted record on the form.

 

[PSSMargaret]

Correct, I would never leave the record selector on a form but was just curious about the error message.

Take care and thanks again for all of your help.

 

[jdraw]

You are welcome. I just hope it was helpful.
Good luck with your project.

 

[jdraw]

Additional info
I commented out the event procedures I had in the sample database.
Then added new event procedures to print out the name of each event as it was executed.
Then chose record 5198 to be deleted via record selector and DELETE key.

Here is the sequence of events.

Form_Activate()  About to delete 5198 using delete key   -03-Apr-2017 10:03:03 AM
Form_Delete(Cancel As Integer)
Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Form_AfterDelConfirm(Status As Integer)

This deleted 5198 and left the form pointing to 5199 ( the next record in the recordsource).

Here are the event procedures I had for the form to see what happens.

'================================see what events happen when deleting by record selector and delete key====
Private Sub Form_Activate()
Debug.Print "Form_Activate()" & "  About to delete 5198 using delete key   -" & Now
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
Debug.Print "Form_AfterDelConfirm(Status As Integer)"
End Sub

Private Sub Form_AfterRender(ByVal drawObject As Object, ByVal chartObject As Object)
Debug.Print "Form_AfterRender(ByVal drawObject As Object, ByVal chartObject As Object)"
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Debug.Print "Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)"
End Sub

Private Sub Form_DataChange(ByVal Reason As Long)
Debug.Print "Form_DataChange(ByVal Reason As Long)"
End Sub

Private Sub Form_DataSetChange()
Debug.Print "Form_DataSetChange()"
End Sub

Private Sub Form_Delete(Cancel As Integer)
Debug.Print "Form_Delete(Cancel As Integer)"

End Sub

Private Sub Form_ViewChange(ByVal Reason As Long)
Debug.Print "Form_ViewChange(ByVal Reason As Long)"
End Sub

Messages are generated by Access asking to confirm the delete request. I chose Yes.

Bottom line--whatever Access does using the click record selector and use DELETE keyboard key, it does ask you to confirm the delete, it does delete the record, and it refreshes (open/Close??) the form so the deleted record is not shown.

However, it does not write to the auditlog, but I didn't add any code to the event procs.

 

[jdraw]

Margaret,

If you are still following this, I have reviewed and adjusted the audit log.
I think it now :
- shows the details for Adds and Deletes,
-writes the appropriate record number/id to the log, and
- it keeps the form position after delete, and
- will write a log record without error when using record selector and delete key

I have included the database and some notes in the attached a zip.

 

[PSSMargaret]

Yes, I'm still following. I deleted a record using the Record Selector and it deleted two records. Again, I would never have someone delete a record using the record selector so I don't think it matters but wanted you to know.

Would this code also handle and record changes if the form had a subform. I believe Arnel's updated code from Martin Greene's took care of that but am not sure.

Margaret

 

[jdraw]

Thanks for the feedback. I'll take a look.

The AuditLog code in the test database you sent me --the original - had information about the changes made by Arnelgp. It was that code that I modified to write details of NEW and DELETE actions to the log. I did not make any changes related to Forms/subforms.

Other than the record selector issue you mentioned, does the revised database work as you need?
Form keeps position etc.

UPDATE:

A new version of the zip file (database and notes) has replaced the original in Post#29. This revision confirms the fact that using the record selector and delete key on keyboard removes 2 records.Since that approach was not needed by the poster, I have not reviewed or resolved it.

My testing with the delete button on the form and the revisions made to the Audit routine indicate that it is working. I have recommended that record selectors on the form be set to NO to eliminate using that technique.

 

[PSSMargaret]

I will give it a full test in the AM. Can't get to it right now.

 

[PSSMargaret]

jdraw,

I tested it and everything seems good. Thanks again.

 

[jdraw]

PSSMargaret,

RE: Audit Trail

I have reviewed the way Access Deletes records, and have adjusted the basAudit routine. It now works using the Delete button or the record selector and delete key. See the notes in the attached zip for more details. In the notes I have included facts that seem to explain why the wrong record was being logged in the original. And it was a combination of my approach and the underlying delete mechanism that caused the 2 records to be deleted using the record selector-delete key.

The new approach which uses a temp audit table when processing a record Delete also uses a new action DELCONFIRM. If you review the frmMain and its related code, you will see the event procedures and calls to the audit routine.

I looked at the changes made by arnelgp and they include the form name in the function call and it writes the form name to the audit log. It does check to see if a control on the form is a subform, but it does not have any code to process it.

For anyone else reading this, I recommend you use the materials in this zip rather than the one in an earlier post in this thread.

Good luck with your project.

 

[PSSMargaret]

Sorry I did not reply sooner. Deep in a project. Thanks for your updated code.

 

[jdraw]

You are welcome. It was a learning experience.
Good luck with the project.

 

[PSSMargaret]

jdraw,

How are you? Just wanted to give you an update and ask a few questions. I'm nearing completion of my project and have implemented the audit trail code with your updates. It works perfectly. I would like to slim down the code that is not needed on some forms.

If a form doesn't have a delete function, I believe I can remove the below sub procedures, correct?

1. Private Sub btnDel_Click()
2. Private Sub Form_Delete (Cancel As Integer)
3. Private Sub Form_AfterDelConfirm (Status As Integer)
4. Private Sub cmdDelete()

Actually, your notes that accompany #4 above state that this code is not used in the audit log so I believe this can be removed altogether. Let me know if this is correct.

Just trying to tidy up the code pages.

Also, there is code for Debug.Print statements. What does this do?

Thanks. Hoping to include this in all of my database.

Margaret

 

[jdraw]

Margaret,

You're talking about post #34,right?

None of the functions with XX in the name are used.
I set a global variable called ShowDebug. If it is TRUE, then debug.print statements will appear iin the immediate window. If it is set to FALSE, these debug prints will not be sent to the immediate window.

The debug.prints do not take any significant space, but can be very helpful when debugging.

I would not remove anything.

Also, I will be looking at adjusting the audit materials to work with subforms. I was looking at it about week ago using a different application set up and was having issues with ActiveForm and ActiveControl and ActiveScreen which Martin used. When I tried with subforms, I found that the ActiveScreen did not work as described and that the Mainform was always active. Even when I deleted all subsubform records and went to the subform to delete it, it was not the activescreen and gave an error because t did not have focus. I have been traveling, but will get back to it.

Glad your project is going well. I will post the subform stuff if/when I get it to work.

Check the DebuggingTips link in my signature for more info on Debug.Print. It is a very useful tool.

 

[PSSMargaret]

No, I think I went back to the version in Message #29. While I was curious why the audit log didn't include records deleted by selecting the record selector and then pressing the delete key, I want as little code as possible to accomplish a task and knew I wouldn't be leaving any record selectors in the forms. Since the original code provided by Martin Green has blossomed into large amounts of code that I don't fully understand, I may hold off using it until I understand it better. I'll keep an eye out for your future posts.

Safe travels.

 

[static]

You have 3 events, delete, beforedeleteconfirm and afterdeleteconfirm.

The delete event fires for each record being deleted in the form.

If your form is continuous (displays multiple rcords at once) users can select multiple records and hit delete and the Delete event will fire multiple times, once for each record.
So if you wanted to build up a list of deleted records you'd do it there.

The confirmation events fire after the delete event, so you either commit or discard your changes based on the users response.