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?
Audit Trail
- Thread starter libby32
- Start date
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?
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
Registered User.
- Local time
- Today, 08:33
- Joined
- Jun 8, 2002
- Messages
- 6,193
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...
I have uploaded a newer version in the post below...
Last edited:
M
mrabrams
Guest
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
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
M
mrabrams
Guest
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
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
Registered User.
- Local time
- Today, 08:33
- Joined
- Jun 8, 2002
- Messages
- 6,193
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
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
M
mrabrams
Guest
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
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
Old registered user
- Local time
- Today, 12:33
- Joined
- Feb 22, 2002
- Messages
- 9,402
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
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
ColinEssex
Old registered user
- Local time
- Today, 12:33
- Joined
- Feb 22, 2002
- Messages
- 9,402
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
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 appreciativeCol
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????
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????
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
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
Last edited:
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
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
Groundrush
Registered User.
- Local time
- Today, 12:33
- Joined
- Apr 14, 2002
- Messages
- 1,376
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
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
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
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
Registered User.
- Local time
- Today, 12:33
- Joined
- Apr 14, 2002
- Messages
- 1,376
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...
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...
