Audit Trail (3 Viewers)

[libby32]

I am new to being a DBA. There are users keying into a database I created until 3 a.m. How I can review data that they have entered after I leave at 5 pm until 3 a.m. to pick up on possible errors? Or should I need to do this?

 

[Rob.Mills]

You can setup tables to track who adds records, who changes them and who deletes them. They're complicated to setup. If you can avoid by setting input masks, formats and validation rules, that would be much simpler.

You can setup a function pretty easily that will add to each record who created it. Does everyone have their own user id's for their workstations?

 

[ghudson]

Try my attached sample. It is based on the How to Create an Audit Trail of Record Changes in a Form . I recently had a reason to use it so I had to figure out how it worked and altered it to my liking. I also made some changes to the original code to report Null values. Please post back if you have any suggestions. Thanks!

I have uploaded a newer version in the post below...

 

[mrabrams]

That is awesome ghudson.

I tried adapting it to a small db of my own. When setting the breakpoint at Private Sub Form_BeforeUpdate(Cancel As Integer) and 'F8' ing through the code, I got an error at the line:

Set MyForm = Screen.ActiveForm
jumps to:
If Err.Number <> 64535 Then 'Operation is not supported for this type of object.

I seem to remember this happened before, somewhere, but I cannot figure out what needs to be done to correct it.

Do you have any ideas?

Thanks - you did a great job with your demo !!

Michael

 

[ghudson]

Doh!!! I just noticed what you were trying to do. You can not run the code from a module. You have to run the code with a form active on the screen...ie...Set MyForm = Screen.ActiveForm

HTH

 

[ghudson]

Here is the new and improved version of my Audit Trail sample. Please post back if you have any suggestions. Thanks!

 

[mrabrams]

This is wonderful ghudson !!! I've seen a bunch of audit trails, but this one is so clean. You did a great job.

Do I have this straight - the AuditTrail field in the table only holds record of the "New Record", and the form's textbox tbAuditTrail holds the changes?
I made changes in a field or 2, and the changes were recorded in tbAuditTrail, but not in the table.
Is this correct?

If so, is there a way to hold only the changes in the table?
(The reason I ask, is that I already account for who created a new record and date/time.)

I really need to track just the changes.

Again, a fantastic job.

Michael

 

[ghudson]

Thanks for the compliments. I had tried the Access 97 version of the Audit Trail code from Microsoft last year and gave up after many frustrating nights of wasting away on something that confused the heck out of me. I am glad I took notice of a few recent posts that stroked my curiosity.

Everthing is stored in the AuditTrail field in the tEmployees table in my sample. The control source of the tbAuditTrail text box is the AuditTrail field in the tEmployees table.

You can not see all of the data in the AuditTrail field in the tEmployees table "from the surface" because there are line breaks and maybe a lot of data in the AuditTrail field in the tEmployees table. To see the data in the AuditTrail field in the tEmployees table, left click your mouse is the AuditTrail field in the tEmployees table and press the Shift F2 keys to open the Zoom box.

Rem out the
Rem MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"line if you do not want to record the new record info. That way it is still there if you ever need it for the function will end on a new record because of the Exit Function on the next line.

HTH

 

[mrabrams]

It's there!!

You know, I 've been following these boards for a couple of years now, and see your name a lot. I am fascinated at not only how much knowledge you have (and the others too !) but that you are so willing to share your knowledge.

Again, thank you for your time and patience. See ya 'round the Access boards.

Take care!

Michael

 

[ColinEssex]

Ghudson -

Thanks loads for the Audit Trail code. Its really good.

I had to amend it a bit because it was firing when I scrolled from one record to the next because its on the before update event and logging the userName and date to the memo field even though no changes were made.

Now thats done it works fine....excellent

Thanks again

Col

 

[ghudson]

ColinEssex,

Can you post some more details. I can not reproduce the (writing of the user name and data) event when no changes are made to the current record just by scrolling through the records.

Thanks!

 

[ColinEssex]

Hi Ghudson

I figured that (according to the Access help) the BeforeUpdate event runs when you scroll to the next record and it was posting this line -

MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

to the AuditTrail memo field even if no changes were made to my form.

So I commented that bit out and copied it into your select case code like this

If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value & " by " & sUser & "; " & Now
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value & " by " & sUser & "; " & Now
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null" & " by " & sUser & "; " & Now
End If
End Select

and it works beautifully
I am much appreciative

Col

 

[Traden]

thanks to you guys

Thanks a lot, that audit trail works perfect, and i was going nuts when i tried to code it!

 

[aneats]

Audit trail

re. ghudson's audit trail code...
this code worked well for me in access 2000 Form and tracked any changes made brillantly, so I thought all my probs were solved. Not so.

My problem is I am trying to do the same thing in an Access 97 form - the form is called Payments and Receipts Main Form and contains a subform called DAP3 Subform which tracks the payment of grants. The subform is set to a continuous form and contains all the payments for which I want to track the changes.

It does not seem to work. Is it because it is a continuous form and does not know how to track the changes in each part?

I keep getting an error message saying it can not find the field name tbAuditTrail and I have copied the code exactly.

Can anyone help????

 

[Lyn Mac]

I had that problem earlier. I found out that when a form with subform is displayed and edits are being done to the subform the active form is the parent form and not the subform.

This seems to be the effect that this line in the Audit Trail had:

Set frm = Screen.ActiveForm

My solution:

1. Pass the form as an argument of the function

Function AuditTrail(frm as Form)

2. Comment out these lines in the code

'Dim frm as Form
'Set frm = Screen.ActiveForm

3. Call the function in the BeforeUpdate event of the form and or subform as
follows:

Call AuditTrail(Me)


HTH,

Lyn

 

[Kevin_S]

Hi lyn Mac,

I have a few follow-up questions to your suggestions on using the Audit Trail with subforms so I hope you don't mind revisiting this...

I tried to implement your solution but when I commented out the two lines:

Dim frm as from
Set frm = Screen.ActiveForm

Then the code errored in every instance where it has frm in it... i.e. frm!AuditTrail = frmm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"

how do I pass the variable so that this error does not occur..?

Thanks in Advance....
Kevin

 

[Kevin_S]

Nevermind

I was being thick headed again - I got it working and it is a great solution to make the Audit Trail even more useful...

Thanks to all who took the time to read this post!
Kevin

 

[Groundrush]

User Identification

Hi

Does anyone know how to change the user names

I have tried out this great Audit trail sample and all the entries have the same name "Admin"on the changed records

I am not using Access security with workgroups

Thanks in advance

 

[Kevin_S]

Groundrush,

In order to change the user name you have to have some form of security or a FE/BE app set up. Do you use custom/personal security? If you do then you should be able to capture the current username from this... or... is you application split fe/be with a larger db (like SQL Server, ORacle, other?) if so you can capture user names/server roles thorugh pass-through queries and use those user names in the audit trail.

If you do not have any security setup (your own or Access/Other) then all users will be seen as 'Admin' because when they open the db they are viewed by Access as the administrator because they have full admin privledges.

HTH,
Kevin

 

[Groundrush]

Thanks for your reply

I don't have any security on the database apart from the disable bypass key.

Each user has a copy of the front end on their machines that is linked to the back end on the server.

Users have a Novell password to get through and a few also use the windows password option

would a passthrough qry be complicated to do?
I have used passthrough qry's before, but I wouldn't know where to start in this case.

by the way

I started a new thread as I thought this one was dead.........oops


http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=50716


thanks for your time...