Audit Trail

[reclusivemonkey]

In all honesty, I dont need this but have been talking about this with my tech team and they seem to think its impossible - either way, its simple curiosity.

Any "tech team" that tells you something is impossible are a liability.

 

[JohnD]

There a decent bunch - they just havent have a clue when it comes to access. They are working on a new DB that is using something besides Access.

 

[nateobot]

The Audit Trail has been working great in my currently messed up DB

I did like a few suggestions said, I have the Audit Trail that can be turned on by an invisible command button that then prompts a PW. After the correct PW it opens the Audit Trail for view. It then can be closed by another invisible cmd button - works like a charm.

My question to add to this thread is this...Can the Audit Trail or something of the likes, track when someone opens up the DB and what forms they have accessed (or tables / queries etc). So essentially it doesnt only track changes of the data, but it tracks the actuall user.

In all honesty, I dont need this but have been talking about this with my tech team and they seem to think its impossible - either way, its simple curiosity.

John D

Not sure about the query and table access, but you could put a function/sub that writes to your audit trail table on each form_open and run on autoexec. Like I said I am not sure how you would execute it when a table or query is opened.

 

[JohnD]

I would assume that you would create an entirely different table to run this function considering its not record specific?

 

[ghudson]

You have a major design flaw with your application if your users are allowed to access the db window and select the db objects that they want to open/edit/rename/delete/etc.

Also, tables and queries do not have events [like the OnOpen event] that you can trigger code from.

 

[JohnD]

Come to think about it Ghudson, Im not an administrator on the main DB here at the college but can go into design view and edit the forms, run my own queries etc. The only thing that I cant do, is edit the linked tables.

....thats just scary

 

[JC3]

Memo field limit

This Audit Trail has been working for a few months on my DB but I am now getting

Could not find Field 'Description'

As far as I can see this is due to the large size of the Audit Trail field which in some cases can be as high as 20,000 characters.

Has anyone got any better solutions to this?

JC

 

[Poptartme]

Audit Trail table changes

I want to be able to audit trail any changes in SQL server table. How do I go about doing this?

I would like to be able to run a query on a daily/weekly basis to find out what was added/modified/deleted from the this table.

Thank you,

Poptartme.
P.s the reason I have to do this is some people are making changes to customer oders etc and when I confront them, they deny they had anything to do with it. I need to police these people....help!!!!

 

[RDSJMS]

GHudson, your Audit Trail code is great! Thank you for saving me from many sleepless nights.

My database has a form for easy creation of a same-as-except record. The user performs a find on the target record then uses a "Duplicate Record" command button and proceeds to edit the appropriate fields, then uses a "Save Record" command button.

Naturally, what happens is the Audit Trail from the source record is copied into the Audit Trail of the new record. What I can't figure out is how to not copy the source records' Audit Trail, but rather simply have "New Record added on <date> <time> by <user>" put into the new records' Audit Trail.

Any and all help is greatly appreciated.

 

[Teflontel]

User Log

I looked at DanG's DB and was impressed by his UserLog. I have tried to get it to work in a very simple DB so that I can understand it. I'm sure that I've copied all the code correctly but zilch. Can anyone tell me where I've gone wrong.
Many thanks
Tel

 

[ReggaeJam]

Trouble with Subform Audit code

I'm having trouble with making Ghudson's Audit trail code work with a subform. I've tried Lynn's solution, but I'm having trouble with thecode. It is giving me an invalid use of property error. Further, it will not let me update some records as it gives me an error since the form is based on a query with multiple tables in it. What am I missing?

Thanks.

 

[ReggaeJam]

Ok, correction on my earlier discovery. The error code 3251 appears because of the lookup fields linked from another table. My solution is to rename the controls of all the lookup fields. eg. Lookup1, Lookup2,... Then slight modification is made to the AuditTrail function as shown below to solve this problem:

If ctl.Name = "AuditTrail" Or ctl.Name Like "Lookup*" Then GoTo TryNextControl 'Skip AuditTrail or Lookup field.

I really liked this solution, but I am receiving three errors.

1) Subform, for some reason Lynn's solution is not working for me
2) I need to track my lookup controls. Will this code work with the lookup fields or what is a better way to modify it so it will?
3) This is related to the subform problem. I keep running into an invalid property error when I try to implement Lynn's solution.

Thanks for your help.

 

[ReggaeJam]

I tried it and It worked I'm not sure why I can't get it to work in my database.
But for now I'll continue with it as a Audit_Trail1 as I've already got a lot of work done.

On another note Has anyone tried using this with tabs it seems it works fine on tab 1 but on tab 2,3,4... If I make a change it records I made a change but not what it was and I get an error 3251-Operation is not supported for this type of object.

I hope some one has a solution

Thanks
Joshker


Thanks Joshker, That seemed an unfair solution lol. It works though

 

[ReggaeJam]

MS Get Form Class

When someone feels like explaining this question to me, please send a response to my message box. Thank you. I'm not sure why the person did not put his code out for everyone to see? I'm not sure if he was ashamed of it or not. Thank you


Audit Trail 3251 error. Operation is not supported.


rob1234
Registered User Join Date: Aug 2005
Posts: 23


Alright, I solved it by creating a recordsetclone in the form_current and then creating a function getControlValue in the form class that would take the name of the control and return the value from the recordsetclone (this only worked if the controls had the same names as the fields in the tables). Then value = MyForm.getControlValue(ctl.name). Finally, I replaced all the ctl.oldvalue with value. Plus some other minor edits.

 

[gilescis]

To make things a little more complicated, What happens if a record is deleted, then the audit trail for that reord is gone also. Can the audittrial field be in a seperate table so it does not get deleted ?

Great job on this BTW

 

[epicmove]

zoom...

ghudson,

Audit trail looks great, think I will integrate it into my current project

I am trying to use a similar zoom function to what you have used to show a memo field. I tried using your code:

Private Sub Event_Details_DblClick(Cancel As Integer)

On Error GoTo Event_Details_DblClick_Err
    DoCmd.RunCommand acCmdZoomBox
Event_Details_DblClick_Exit:
    Exit Sub

Event_Details_DblClick_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Event_Details_DblClick_Exit
End Sub

But I get the error "Procedure Declaration does not match description of event or procedure having the same name"

Any ideas? Do I need to change anything on the memo box itself?

Thanks

 

[china99boy]

Hi, I am using the AuditTrail sample from this forum and I get a run-time error 3251. Operation is not supported for this type of object. Then it stops at

If ctl.Value <> ctl.OldValue Then

It looks like It work perfect when I add a new record, it put the changes in the AuditTrail field. But when I go back and try to edit an exsiting field in wont allow the Beforeupdate. I am using Access 2002. How can I fix this.

Thanks In advance.

 

[china99boy]

Problem solved! The error code 3251 appears because of the lookup fields linked from another table. I followed the solution posted by "Hooi"

Rename the controls of all the lookup fields. eg. Lookup1, Lookup2,... Then slight modification is made to the AuditTrail function as shown below to solve this problem:

If ctl.Name = "AuditTrail" Or ctl.Name Like "Lookup*" Then GoTo TryNextControl 'Skip AuditTrail or Lookup field.


Now it works like a charm.

 

[kdt]

hi

sorry for bringing this post back, but it's exactly what I was looking for , well not EXACTLY. I have the same problem with trying to maintain an audit trail for a subform which is part of the main form. Using the excellent code provided here and following the suggestions to make it compatible with the subform, it doesnt work.

Can anyone suggest what might be wrong and what I can do to fix it

Thanks

 

[jcs]

working module with subforms

I have spent considerable time trying to follow the links on this page to get an audit module that works with subforms. The following is what I finally got working. The main theing that i changed was in the following statement:

Public Function Audit_Trail(MyForm As Form)

MyForm as Form must be exactly as you see it.

Also make sure you get all the module and control names right. I am posting all of the code below because frankly i can't remember if i changed anything else, but I don't think so. There are 3 modules below, dAuditTrail, the berforeupdate code and the doubleclick code for viewing the audit field.
Good Luck..

dAuditTrail   module
******************************
Option Compare Database

Option Explicit

Public Function Audit_Trail(MyForm As Form)

On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592
    
   ' Dim MyForm As Form
    Dim ctl As Control
    Dim sUser As String
   ' Set MyForm = Screen.ActiveForm
  '  sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with 

workgroups.
    sUser = Environ("UserName")
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been modified.
    MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & Now & " " & sUser & ";"
    
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        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 & ": From: " & ctl.OldValue & ", 

To: " & ctl.Value
            '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 & ": From Null, To: " & ctl.Value
            '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 & ": From: " & ctl.OldValue & ", 

To: Null"
            End If
    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
    If Err.Number = 64535 Then 'Operation is not supported for this type of object.
        Exit Function
    ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
        Beep
        MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function
*********************************



Before update module in forms
*************************************
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err
    
   Call Audit_Trail(Me)
   
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit

End Sub
***************************************************



Doubleclick module in tbAuditTrail controls
***************************************************
Private Sub tbAuditTrail_DblClick(Cancel As Integer)
On Error GoTo tbAuditTrail_DblClick_Err

    Beep
    DoCmd.RunCommand acCmdZoomBox

tbAuditTrail_DblClick_Exit:
    Exit Sub

tbAuditTrail_DblClick_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume tbAuditTrail_DblClick_Exit

End Sub