Audit Trail

[Dom Fino]

G Hudson,
I have found this post to be very helpful. It has certainly made this newbie's job much easier.

I have successfully incorporated your code plus the edits from Lyn Mac. Now I want to make one more modification but need some help.

I want to hide the text box on the forms and subforms. I plan to do this by simply turning the visible property to No.

I then want to put command button on each form and subform to open the zoom box which displays the audit trail. I see you have this capability with the on double click event but do not know how I can activate the on double click action with a command button.

Any help or suggestions will be greatly appreciated.
Thanks,
Dom

 

[ghudson]

I then want to put command button on each form and subform to open the zoom box which displays the audit trail. I see you have this capability with the on double click event but do not know how I can activate the on double click action with a command button.

Open the form in the design mode, left click the tbAuditTrail text box, open the properties box, select the Event tab and notice that the On Dble Click field has [Event Procedure] which means that there is VBA associated with the double click event of the tbAuditTrail text box. Click the elipses [...] button beside the [Event Procedure] and it will take you right to the code of that event.

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

If you are going to do this with a command button then you will have to set the focus to that text box first. Like this...

    Me.YourTextbox.Setfocus
    DoCmd.RunCommand acCmdZoomBox

 

[Dom Fino]

GHudson,
Thank you so much. I did as you suggested and it works perfectly. Thank you again for creating such a useful tool.

Merry Christmas and have a safe and healthy New Year.
Dom

 

[ghudson]

Your welcome!

 

[mohsinhq]

Thanks for the Audit trail GHudson. Invaluable!

Quick question - When i look at the table view the first line in the audit trail is always blank so i have to use the cursor arrow on the audit trail field to check which records have been edited.

Is there anyway to remove this blank line at the start??

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

i had the same problem. Solved it by changing currentuser to Environ("Username") which gives the username of the person logged in, and not the group.

 

[Dom Fino]

I have been adding this audit trail to my application with some of the modifications suggested by others on this thread. The audit trail works great on one form but I receive a compile error when I try to add the functionality to another form.

I receive an error: Compile Error Invalid use of property on the Call AuditTrail(Me) line. I read the help for this error but I have no clue as what it means or how to fix it. Can anyone help me figure out why the same Call statement works fine on one form but not another?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [txtProjectEndDT] < [txtProjectBeginDT] Then
   MsgBox "Project End Date Cannot Be Less Than Project Begin Date"
   txtProjectEndDT.SetFocus
   Cancel = True
End If
    DoEvents
    [B]Call AuditTrail[/B](Me)

End Sub

 

[dougm21]

I have used GHudsons code and it works great. however I would like to know how I can modify it to add a new record but only add the field that was changed and not the whole record?

Doug

 

[LaBelette]

Call AuditTrail(Me)

Try AuditTrail Me

Call function is no more user in VB, it is kept only for backward compatibility.

By the way, I'll work on a version of AuditTrail code that will audit the fields in all the subform of a form. I'll probalby post it here after I contacted the person who have the intelectual propriety of the code.

 

[billcute]

Call AuditTrail(Me)

To All,
I want to thank everyone and most especially "gHudson" for his "AuditTrail sample DB.

I downloaded the sample Db and implemented changes discussed by: "CollinEssex" dated 03/19/04 @9;55 am and that of "Lyn Mac" dated 05/08/2003 @ 11:29 PM.

Both discussions were comrehensive and user friendly. Thanks

After adding Lyn's suggested after Update code --> Call AuditTrail(Me).

I received this error:
Compile Error: "Invalid Use of property"

Can anyone tell me why I am getting this error message ?. I have not made any other changes other than the ones decribed above.

Thanks
Bill

 

[Crilen007]

Wow, thats looks like a lot of effort.


Uhm, I didn't have time to read all the posts, I read the first two, and if you are just trying to have the persons name and time, cant you just set the default value for username to Eviron("UserName") and the time to the current time?

 

[scottfarcus]

Audit Trail works beautifully, thank you! Simple, but very effective.

However, can anyone provide a solution to this issue:

When updating data in a combo box (Autonumber field as the bound column) what is the most efficient way to alter the Audit Trail so that it reads:

Changes made on 2/26/2005 9:42:47 AM by Farcus, Scott;
Organization: Changed From: Acme, To: Microsoft

Instead of:

Changes made on 2/26/2005 9:42:47 AM by Farcus, Scott;
Organization: Changed From: 4, To: 8

I can think of ways to do it, but none seem very efficient. Thanks in advance!

 

[GrahamUK33]

Can anyone please tell me how I can use the user name from the Logging On.zip (http://www.access-programmers.co.uk/forums/showthread.php?t=64532&page=1&pp=15) to populate the user name in the Audit Trail, as it keeps coming back saying that the changes where made from ‘Admin’ rather than the user.

 

[LaBelette]

Can anyone please tell me how I can use the user name from the Logging On.zip (http://www.access-programmers.co.uk/forums/showthread.php?t=64532&page=1&pp=15) to populate the user name in the Audit Trail, as it keeps coming back saying that the changes where made from ‘Admin’ rather than the user.

Just to be sure, you're talking of the Access User, not the Windows User, right?

In that case, it will be CurrentUser.

In the other case... well I don't have a clue, but its probably possible using an API.

 

[LaBelette]

By the way, I'll work on a version of AuditTrail code that will audit the fields in all the subform of a form. I'll probalby post it here after I contacted the person who have the intelectual propriety of the code.

Good news!!!
I made an Audit in an Access database that does all I stated above and much more! It's in beta test, so It might need some work, but I'ts mostly done. I still need to write a documentation.
And it appears that I have the intellectual property for this code!!! Its good to be our own boss!!!
It works a little like AuditTrail, but offers much more flexibility. If someone is interested, you can email me at julien.berube@gmail.com and I can send you an example once its done and documented.

 

[ghudson]

as it keeps coming back saying that the changes where made from ‘Admin’ rather than the user.

User CurrentUser() if you are using Access security.

User Environ("UserName") if you want the users network ID.

 

[Joshker]

I receive an error: Compile Error Invalid use of property on the Call AuditTrail(Me) line.

I received this error:
Compile Error: "Invalid Use of property"

I was gettting the same message as Dom Fino and billcute
Compile Error: "Invalid Use of property".

The Only working solution I could come up with was to rename the function. It's now called Audit_Trail1.

It wasn't working in the Database I created but it was working in the sample.

So I created a new Table and form in the sample Database and It didn't work.
I reanamed the Function and It worked so I renamed it in the Database I created and it worked.
So then for the sake of curiosity I compacted and repaired the database. to besure that all temp/del information was gone, I then Changed the name back to Audit_Trail and again the same error. The only thing I didn't try was creating a new database from scratch. However I don't think that would have solved the problem.

I think this problem may be associated with Access 2003 which is what I'm using.

Regards
Joshker

 

[ghudson]

I conveted the old Audit Trail sample to Access 2003 and everything works okay for me. Try it...

 

[Joshker]

I conveted the old Audit Trail sample to Access 2003 and everything works okay for me. Try it...

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

 

[Joshker]

Boy oh boy do I feel dumb

The Problem I was having had nothing to do with the Tabs.
I was getting the error on controls when Enabled=No
So I added a simple If statement to take care of that.

Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        [B]If ctl.Enabled = True Then[/B]
        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!Audit_Trail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed 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!Audit_Trail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & 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!Audit_Trail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
            End If
        [B]End If[/B]
    End Select

Joshker

 

[dajabo]

how do I get this working??

Er, excuse my stupidity, but I don't know how to get AuditTrail to work in my DB!

I have imported the module dAuditTrail, but how what do I do now?

I haven't a clue!!