Solved

Create audit trail of changes

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

685 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