Audit Trail (1 Viewer)

[filfil]

Hi, I'm using the audit trail. it was working fine until I added more fields in the sql db that is connected to my MS Access db. the new fields are not tracked by audit trail! I have no idea why is that!..
sometimes I get the following error when trying to save changes done in the current record: "Error 0 - Reserved Error"

Can someone help me?

Thanks in advance.

 

[filfil]

it is fixed. there was a control that is related to another table, and this was causing the problem.

 

[khwaja]

I am using this code in MDB file quite succesfully. However, when I create a mde file for users, then changes made by them in the in the form are not written back to the audit table. Is there some sort of bar in this code preventing mde file to be audited? MDB works fine. Also, yes/no fields changes are not captured by this code. Could you confirm if this is expected.

 

[Dazzy]

Hi

Thanks for this.

I am a beginner when it comes to database deisgn and VBA code, I set up a rudamentory DB for work a few years ago to hold all participant details and training details across all our programmes. I have been since adding various reports etc at the managements discreation.

Some staff are now being judged on the accuracy of the information stored in the DB and they came to me and said they swore they entered stuff correctly but the next day it was wrong. So I set up a little trap, after the staff member had entered the data, i took a screenprint showing time, date etc, and low and behold when we checked the next day it was changed.

If staffs jobs are on the line I dont think the way the DB is currently set up is a fair judgement.

The DB is set up to use a single login so there was no way to track what happened, but I now want to set up this Audittrail with some sort of multi user login. I found reference to this post earlier in this thread http://www.access-programmers.co.uk/forums/showpost.php?p=175590&postcount=4 and I really like how it works as Access Security is way beyond me at the moment.

Can anyone help me in getting the Audittrail catch the usernames from that?

 

[gemma-the-husky]

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

 

[khwaja]

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

 

[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