Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Create audit trail of changes

Posted on 2001-06-15
5
Medium Priority
?
304 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 400 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 59
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 59
ID: 11358670
Miguel,

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

Jim.
0

Featured Post

Technology Partners: 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 shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

571 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