Audit Trail

[Dazzy]

if you dont login to access with access security every user will be logging in as "Admin". You can easily use a function to pick up the windows login, as an alternative.

However you need to get to the bottom of what is going on - having multiple users sharing a single login is not advisable, and may even be causing these errors.

You should split your database for multiple users

Could you start a new thread for this, as this is a very old long thread

Hi sorry about delay in replying.

Although I designed the DB I never placed the single login on it, it was one of our technicians and they just used the set password option in Access.

This resulted in only one person allowing to open the DB in edit mode at any given time. The problems I outlined, I know how these are occuring I just need to trap the individual who is editing deleting the records.

I know it may be hard for you to understand this but the management style where I work is so outdated you wouldnt believe your eyes, never a word of thanks, always dwell on negatives etc

The office structure is so outdated too, their is no user accounts on the PC's for Windows they all login to the standard account and share each others machines. So tracking the enviroment user isnt an option for me.

I will start a new thread on this when I get a chance.

All the best and thanks for your reply

 

[irish634]

Could I request the author of audit trail to answer me on the issue I raised in regards to MDE file?

I'm not sure if you are using my code example or not, but my example works fine in MDE format (see attached).

View attachment Audit_Tracking.zip

http://www.access-programmers.co.uk/forums/newreply.php?do=newreply&p=860840

 

[khwaja]

Thanks will check and give it a go.

 

[RCheesley]

I got irish634's code working on my form perfectly - it's worth mentioning that the primary key of your table needs to be called RecordID for it to work, and this needs to be on the form you're using. I have it as a hidden field, but the field was called SUIID so it didn't work initially!

Now working on the best way to display this in a form!

Ruth

 

[RCheesley]

I'm also now getting the compile error: invalid use of property - any ideas what's going on? I even get it if I remove (Me.Form, [RecordID]) [NB this is using the table method of storing the data]

I did have this working OK but now it seems to be bombing out :|

On another note, is it possible to have this log if there are new records, as well as those which have changed?

Ruth

 

[Access2007User]

What is the main purpose of audit trail?

 

[RCheesley]

If you read through the post it is somewhat evident? It does what it says on the tin, creates an audit trail of records being changed by user/PC/date/field changed etc.

 

[irish634]

I got irish634's code working on my form perfectly - it's worth mentioning that the primary key of your table needs to be called RecordID for it to work, and this needs to be on the form you're using. I have it as a hidden field, but the field was called SUIID so it didn't work initially!

Now working on the best way to display this in a form!

Ruth

You can modify the code to reflect the ID in your table. It doesn't necessarily have to be named RecordID. Just make sure the name in the code match the names in the table.

I'm also now getting the compile error: invalid use of property - any ideas what's going on? I even get it if I remove (Me.Form, [RecordID]) [NB this is using the table method of storing the data]

I did have this working OK but now it seems to be bombing out :|

What line is being highlighted on the compile error?

On another note, is it possible to have this log if there are new records, as well as those which have changed?

Ruth

Yes - Remove this part of the code:

    If frm.NewRecord = True Then
        Exit Function
    End If

 

[ade7985]

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

-----------------------------------
hello Lyn

I followed your code after make some changes i get an error:

'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") 'get the users login name
sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions

'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 & "Changes made on " & Now & " by " & sUser & ";"

compile error on 'If MyForm.NewRecord = True Then'.. help

Thanking you in advance,

 

[ghudson]

Dang, this old dog is still alive?

Removing the ' in front of the 'Set MyForm = Screen.ActiveForm line should fix your problem.

 

[ade7985]

Dang, this old dog is still alive?

Removing the ' in front of the 'Set MyForm = Screen.ActiveForm line should fix your problem.

Thank you,
Yup, I just wake this forum alive again.. but thank you again, I didnt expect reply so soon. this is good stuff audit trail and this is my first access db working on. I might have some more questions. Thank you

V/R,
Alejo

 

[ade7985]

Dang, this old dog is still alive?

Removing the ' in front of the 'Set MyForm = Screen.ActiveForm line should fix your problem.

Hello ghudson,

AuditTrail work perfectly on single form. How do you configure using tab control part? I have it place my form on top of tab control on forms. it won't not update audit when i tested on tab control.

Thank you,

 

[ade7985]

I use RecordID as Long Integer (Auto Number). Is yours the same? Do you have a RecordID on your form? Do you have the "Microsoft DAO 3.6 Object Library" Reference enabled?

Here is a copy of mine. Take a look and see what's different.
View attachment 23926

Sir,
I tried your sample audit trail and it work perfect but only problem i have is when my form are on top of tab control it does not work. I have five form and i use tab control to use page by page. what are setting for tab control able to do audit. Thanks

 

[ade7985]

Does the attachment I put in work for you guys? It works here.
Something has to be different. If you want attach yours and I'll look

Hi,

I tried your audit sample and i get error on 'Call AuditTrail (Me.Form, [Record]). I can't figure out where is causing error

 

[ade7985]

hello,
Is there a way to audit one field in a table? For example, one table has 4 fields and i only want to track one field instead 4 field. Your help is appreciated. Thank you.

 

[china99boy]

Hi guys, I know this is an old post, but I have been using the audit trail coding for a couple years now, and just noticed that after the intial record is entered and further changes are made, the audit trail repeats the previous date,time and user. It doesn't reflect current date, time and user. It does show the correct information changed, but not the date/time and user ID. Does anyone know why this is so? Below is an example of what is recorded in the audit trail. Today, I made a change to this record and as you can see it doesn't reflect the correct date,time, or user.

New Record added on 3/2/2010 5:06:21 PM by s16028n;
DATE IMAGED: Was Previoulsy Null, New Value: 3/10/2010
IMAGE SYSTEM: Changed From: 0, To: -1
tbAuditTrail: Changed From: New Record added on 3/2/2010 5:06:21 PM by s16028n;
DATE IMAGED: Was Previoulsy Null, New Value: 3/10/2010
IMAGE SYSTEM: Changed From: 0, To: -1, To: New Record added on 3/2/2010 5:06:21 PM by s16028n;
DATE IMAGED: Was Previoulsy Null, New Value: 3/10/2010
IMAGE SYSTEM: Changed From: 0, To: -1
BrNo: Changed From: 40, To: 43

 

[MochaTrpl]

Sir,
I tried your sample audit trail and it work perfect but only problem i have is when my form are on top of tab control it does not work. I have five form and i use tab control to use page by page. what are setting for tab control able to do audit. Thanks

I am also interested in how this can be set up for tab control. Works great on a single form.

Thanks!

 

[ranjeewaf9]

Hi,

When I put the code in the following thread by irish634 I get the error, "Microsoft Office Access can't find the macro 'Call AuditTrail(Me.'. I've also attached a screenshot of the error.

http://www.access-programmers.co.uk/forums/showthread.php?t=157566

What should I do to rectify this? There are no other modules in the database and it works fine. I need to have an audit trail (prefarably on another table) so that I can track who changes its records when and what has been changed. Thanks for the help.

 

[boblarson]

Hi,

When I put the code in the following thread by irish634 I get the error, "Microsoft Office Access can't find the macro 'Call AuditTrail(Me.'. I've also attached a screenshot of the error.

http://www.access-programmers.co.uk/forums/showthread.php?t=157566

What should I do to rectify this? There are no other modules in the database and it works fine. I need to have an audit trail (prefarably on another table) so that I can track who changes its records when and what has been changed. Thanks for the help.

Sounds like you put the code in the event PROPERTY and not the VBA window where it should be. See here for where to put code for events.

 

[ranjeewaf9]

Hi Bob,

Thanks for the reply. When I put the VB code VB Editor I get a runtime error 2465. Pls. see the attached image. When I click debug it goes to the VB editor but I can't find anything wrong in the expression. I've attached an image of the Code as well. This was the code mentioned in the link too.