Solved

Create audit trail of changes

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculation in Access 5 26
Change date stored as "short text" YYYYMMDD to MM/DD/YYYY? 27 28
Trying to force an answer in a combobox 7 18
Visible ppty on rpt 7 7
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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