Solved

Create audit trail of changes

Posted on 2001-06-15
5
291 Views
Last Modified: 2006-11-17
I want to create an audit trail of changes that takes place on a single field on a form.  I have a field called "StartDate" and need to know when someone changes it.

I would like to capture the previous value and also the new value that was entered, the primary key, and the user ID.  Any idea or sample code on how to do this?  

thanks
0
Comment
Question by:sopheak
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Accepted Solution

by:
abaldwin earned 100 total points
ID: 6196405
I would do it something like this

Create a table called tblAudit
Fields- OldValue, NewValue, DOC, User
DOC(DATE OF CHANGE)

Create a global variable in a module called varCurrValue

On the Got_Focus event of the startDate field put the following code

varCurrValue = StartDate


On the Lostfocus or AfterUpdate event of the field if the value in the text box does not equal the varcurrvalue variable then run an append query using the varcurrvalue, startdate, Now(), and the currentuser to fill the fields in the new table.

the code would look something like this

dim db as database
dim stsql as strign
set db = currentdb
stsql = "INSERT INTO tblAudit(OldValue, NewValue, DOC, User) SELECT " & varCurrValue & ", " & StartDate & ", " & Now() & ", " & CurrentUser & ";")

db.execute(stsql)


I believe CurrentUser will give you the signed on user.  You may wish to increase the functionality by grabbing some other code or making them enter a password to effect the change.

Andy
0
 
LVL 57
ID: 6196435
Andy gave you the first part, but you need to extend that a bit further.  Besides changes to fields, you really need to capture record additions and deletions as well for a complete audit trail.  

Unforunatly, with Access/Jet, that's not as easy as it sounds because you can't do anything at the engine level.  Everything needs to be done through forms so you can trap the events.

Access/MSDE (SQL) is a different story as you can use triggers to get the added/deleted records easily.

Jim.
0
 

Author Comment

by:sopheak
ID: 6196638
thanks much,  since users only enter data through forms, this will work fine.

0
 

Expert Comment

by:mesteban
ID: 11352494
I just tried this since I also need this (audit trail of three specific fields in a table, all data entry via forms).  I tried it one field "Pax" after creating a new table tbl_Audit_Trail containing the fields IDRecord (autonumber), User (text), Date_of_Change (date/time), Old_PAX (Number), New_PAX (Number).  I created a module containing the following:

Option Compare Database

Public varCurrVal As Variant

In the Got_Focus event of Pax, I entered:
varCurrVal = Pax

In the After_Update event of Pax, I entered

Private Sub Pax_AfterUpdate()
Dim db As Database
Dim stsql As String
Set db = CurrentDb
stsql = "INSERT INTO tbl_Audit_Trail(Old_PAX, New_PAX, Date_of_Change, User) SELECT " & varCurrVal & ", " & Pax & ", " & Now() & ", " & CurrentUser & ";"

db.Execute (stsql)

End Sub

I had to delete the closing parentheses in comparison to your example above or else the editor would not let me save.  I had to change strign to string.  When I make a change to Pax, I get the error:

Runtime Error '3075':
Syntax error (operator missing) in the expression '19.06.2004 23:17:21'.

What am I doing wrong???

Also, what procedure should I follow if I want to track any changes to the fields Arrival_Date and Departure_Date in the same fashion (they are in the same table as Pax)?  Can I just copy the code once we fix it?

Thanks for your help.

Miguel
0
 
LVL 57
ID: 11358670
Miguel,

Open a new question and point a link back to this question for reference.

Jim.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question