Solved

Create audit trail of changes

Posted on 2001-06-15
5
287 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 29
Sending email from List Data 2 51
Search Form not Querying 2 11
Acc 2010 pause execution 10 8
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now