Banking

A simple solution for tracking changes to Access data

When you have a lot of users changing data, it's easy for things to go wrong. But if you keep an audit trail of data input changes, you'll be able to determine which operators are consistently making mistakes (or not). You can also quickly fix problems before erroneous data wreaks havoc. This easy-to-implement solution will help you monitor who's changing data and when.

This article is also available as a PDF download.

Your data is important. In fact, it's critical to your business. Entering, storing, and maintaining valid data is the most important job of any database. When you're the only one using the database, you can limit mistakes. However, when multiple users are changing data, a lot can go wrong. You might want to keep an audit trail of data input changes. In other words, you might want to know who's changing data and when. Tracking changes has two benefits:

  • You can quickly determine which operators are consistently making mistakes (or not making mistakes).
  • You can quickly undo mistakes before your queries and reports start churning out erroneous data.

The solution in this article is not a true audit solution. That type of comprehensive tracking requires a major development thrust. You'll find this solution easy to implement and more than adequate for most applications. However, it won't keep inappropriate users out of your system—you need security for that. In addition, a sophisticated user will know how to open the table and remove records that track their mistakes—although the missing primary key values should be a clue to you that records are missing. The easiest way to keep users out of the Audit table is to move it to a secure backend and link to it.

Building the audit components

You don't need individual tables for each data entry form or each data table. One table stores every change to any table. Refer to Table A to create a table named Audit. You can alter the example table to suit your needs. You probably won't want to remove any of the example table's fields, but you might want to add a few new ones. If you do, remember to update the SQL INSERT INTO statement in the auditing subprocedure, which you'll add later.

Table A: This table stores data changes.

Field

Data Type

Explanation

EditRecordID

AutoNumber

Audit's Primary Key.

EditDate

Date/Time

The date and time change is made.

User

Text

The user who made the change.

RecordID

Text

Value that uniquely identifies the changed record.

SourceTable

Text

Table or query.

SourceField

Text

The field changed.

BeforeValue

Text

The original value before change.

AfterValue

Text

The new value after change.

Close the Audit table and launch the Visual Basic Editor (VBE). Insert a standard module, and enter the following Const statement and subprocedure:

Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

 

Save the module as basAuditTrail and close the VBE.

You can call the subprocedure from any data entry form. We used the AutoForm wizard to create an example form based on the Shippers table in Northwind (the sample database that comes with Access). You can use any bound form, as long as it allows edits. To follow our example, open the Shippers form's module in the VBE and enter the following statement to call the auditing subprocedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, ShipperID)
End Sub

Now, this is where you'll need to customize the example procedure to work with your forms. The call passes two object variables to AuditTrail(): the form as a Form object and the control that contains the data that uniquely identifies the record, as a Control object. The Me identifier handles the form. ShipperID is the Shippers table's primary key (and is an AutoNumber field). Use a single-field natural primary key or an AutoNumber field for this purpose.

Once you save your form, you're ready to start tracking edits (AuditTrail() doesn't track new records, just changes to existing data). With the example form in Form view, change the last digit in the phone number for Speedy Express from 1 to 6, as shown in Figure A, and press [Tab]. In the next record, add the word Service to the shipper's name, change the last digit in the phone number to 8, and press [Tab]. (You can't change the ShipperID value because it's an AutoNumber. In a production form, users wouldn't even see this value.)

Figure A

Change an existing value in the example form.

Pressing [Tab] moves to the next record, which executes the form's Before Update event. That event procedure passes the two objects to AuditTrail(): the form and the data (as a control object) that uniquely identifies the current record. The For...Each statement cycles through the form's Controls collection. When the current control is a text box control, the procedure compares its old and new values. If the values are the same, there's no change to track, and VBA skips to the next control. When the values differ, the procedure executes an INSERT INTO statement that adds a new record to the Audit table, tracking that change.

As is, the procedure tracks only changes to text box and memo controls. If you need to track changes in other types of controls, change that If statement to a Select Case in the form adding all the controls necessary:

Select Case ctl.ControlType
  Case acTextBox, acCheckBox, acOptionGroup, ...

You probably noticed that the procedure passes old and new values as strings, regardless of their data type. That shortcut makes this procedure easier to implement. After all, you don't really need to maintain data types for auditing purposes.

The Debug.Print statement allows you to see the evaluated INSERT INTO statement in the Immediate window. If you experience problems, this is a useful debugging tool. Simply copy the evaluated statement from the Immediate window into the SQL window (Query Design) and run it. Access will give you much better error clues than VBA.

Our procedure contains a minimum of error handling. You'll want to customize the procedure to meet your needs.

Using the Audit table

The table tracks every change, building a historical perspective of what's happening to the data. Each record identifies the user who made the change, the date the user made the change, and the actual change, as shown in Figure B. The table is likely to grow quickly, so filters or queries will probably be the most efficient means of using the tracking records. Usually, you can limit the result to a specific record or user.

Figure B

The table tracks changes to the data.

 

 

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach her at ssharkins@setel.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

56 comments
SMsurv
SMsurv

I am using this to track changes made to incident reports and it works wonderfully with one exception... If the field that has been changed has quotation marks ("") in them, I get a syntax error, “Syntax error (missing operator) in query expression”. Does anyone have a solution for this other than prohibiting the use of quotations all together? Thanks!

WimDC
WimDC

it seems that with cascading changes (= changes in more than one field of the same record), not all the changes are saved.

In my case, the first field is a combobox, the second a date and the third a memo-field.

When I change all 3, only the changes in the combobox are saved

MRLWhite
MRLWhite

When I try to run the code with one form I get an error message “operation is not supported for this type of object” The VBA debug highlights the If statement line in the code; If .Value .OldValue Then So it seems there is something about the change in value it can't handle, however the same code works Ok with most other forms I have tested it with.

rcmccracken
rcmccracken

This is awesome! Thanks for the help!

SheaJeff
SheaJeff

I had to modify the username part to use my own function, but it works fantastic. I created a crosstab query to view the results.

duvivierb
duvivierb

This code is fine but do not work where changes are made in option group controls and in list/combo with multiple value choice. How to improve the code to do it ? Thanks for your replies.

NetworkingWizard
NetworkingWizard

Hi, im new to MS access Database, but i want this feature added to my database, is it posible to run on MS Access 2007? and is there someone here who can demo this using MS Access 2007, that would be much appreaciated.

hartsyb
hartsyb

Hi, I have managed to get all the objects I require added to the audit table except those belonging to a subform which adds a link to an attachment for the desired record in the database. The form has a text box with the hyperlink in and three buttons: add, open and delete, for each attachment. I wish to track which attachments are amended or deleted from the record. Any ideas? Thanks

mkobulni
mkobulni

The code was exiting the subroutine due to a control which triggered the "operation is not supported for this type of object". I had to add a line of code to check for visible, enabled and locked fields. Here is the line of code.. If ctl.Visible And ctl.Enabled And Not ctl.Locked then Here is the line of code in context. For Each ctl In frm.Controls With ctl 'Avoid labels and other controls with Value property. If .ControlType = acTextBox Or .ControlType = acComboBox Or .ControlType = acListBox Then If ctl.Visible And ctl.Enabled And Not ctl.Locked Then If .Value .OldValue Or (IsNull(.OldValue) And Not IsNull(.Value)) Or (IsNull(.Value) And Not IsNull(.OldValue)) Then varBefore = .OldValue varAfter = .Value strControlName = .Name 'Build INSERT INTO statement. strSQL = "INSERT INTO " _ & "tblAudit (EditDate, User, RecordID, SourceTable, " _ & " SourceField, BeforeValue, AfterValue, ComputerName, UserDomain, UserProfile) " _ & "VALUES (Now()," _ & cDQ & CurrentUser() & cDQ & ", " _ & cDQ & recordid.Value & cDQ & ", " _ & cDQ & frm.RecordSource & cDQ & ", " _ & cDQ & .Name & cDQ & ", " _ & cDQ & varBefore & cDQ & ", " _ & cDQ & varAfter & cDQ & ", " _ & cDQ & Environ("ComputerName") & cDQ & ", " _ & cDQ & Environ("UserDomain") & cDQ & ", " _ & cDQ & Environ("UserProfile") & cDQ & ")" 'View evaluated statement in Immediate window. Debug.Print strSQL DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True End If End If End If End With Next Set ctl = Nothing Exit Sub

mkobulni
mkobulni

I have been using this solution for almost a year and it has worked nicely. I recently implemented this code in a new database. On my form (based on one query) I have about 42 controls. I have 13 tabs on this form. My problem is that the SQL insert statement only gets generated for 30 of the controls. 12 are ignored. The 12 that are ignored reside on 6 different tabs. I ran the documenter for this form so I could check the properties of the fields that work and the fields that do not work. I also compared the properties of the tabs that work and the ones that do not. I can't see any difference in the properties. I tried generating a new justified data entry form with all fields from the query and called the audittrail function from the before update event for the form. I still had a problem with these fields. Then I generated a new data entry form with only 2 fields. I placed the primary key and a new date field, "PAPRTrainingCompleted". PAPR was being overlooked on my production form. When I triggered the before update event to call the audittrail for this form, the SQL statement was generated successfully. Is my problem too many controls on one form?

SY110457-23858834669828733604668220383035
SY110457-23858834669828733604668220383035

Hi, Could you help me write a simple event code to track the date changes made to a record in Date_Attend field to a table called StudentDataChange. The fields contained can only be Stud_ID, Date_Attend and new field called DateOfChange? Thanks. Auto# Stud_ID Stud_Name Date_Attend 1 1234 Robert Smith 12/1/2010 2 2345 Jane Doe 11/30/2010 3 3456 Liz Allen 11/15/2010 4 4567 Doreen Jackson 11/1/2010

BoWhite
BoWhite

Hello, I used this successfully on forms where the data is only based on a table. However when the data comes from a query when the code evaluates a field that comes from a related table you get error 3251. Check out http://kbalertz.com/207836/Error-OldValue-Property.aspx for detail on this issue The solution to this is that you must setup a clone record set in the OnCurrent operation of the form and then draw the old values from this record set. That works when you type in the field actual name such as rs![OrderName] but the problem is that this code is made so you don't have to type in each control on each form. Therefore I want to dynamically set the field name in the rs!FIELDNAME. How is this done I have tried rs!(ctl), I have tried concantenating a string and using that variable FIELDNAME="["&ctl&"]" I have even tried FIELDNAME="rs!["&ctl&"]" nothing seems to work. It must be possible. here is the current code I have This is in the AuditTrail code of the form itself: >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> For Each ctl In frm.Controls With ctl 'Avoid labels and other controls with Value property. ControlName = ctl.Name If (.ControlType = acTextBox) Or (.ControlType = acComboBox) Or (.ControlType = acOptionGroup) Or (.ControlType = acCheckBox) Or (.ControlType = acMemo) Then 'Or acListBox Then If .Enabled = True Then varBefore = rs!(ControlName) ' rs![InstallationPosition] works so the record set seems OK If .Value varBefore Then 'varBefore = rs!.OldValue varAfter = .Value strControlName = .Name 'Build INSERT INTO statement. >>>>>>>>>>>>>>>>>>>>>>>>>>>>> This is in the very top of the code for the form >>>>>>>>>>>>>>>>>>>>>>>>>>>> Option Compare Database Dim rs As Recordset >>>>>>>>>>>>>>>>>>>>>>>>>>>> This is in the Form Current code >>>>>>>>>>>>>>>>>>>>>>>>>>>> Private Sub Form_Current() Dim rs As Recordset Set rs = Me.RecordsetClone rs.Bookmark = Me.Bookmark End Sub >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Sniper-BoOyA-
Sniper-BoOyA-

Is it possible to only ask for a reason, when the record in question, has no value.. Because it makes no sense to ask people for a reason, when they just add data.. Cheers!

adrianlall
adrianlall

if you replace If .Value .OldValue Then with the following code: If (.Value .OldValue) Or ((Not IsNull(.OldValue) And IsNull(.Value))) Or ((IsNull(.OldValue) And Not IsNull(.Value))) Then it will now track changes in values, changes where a null value is now replaced with a value or if a field's value is now replaced with a null Hope this helps.

waltjr50
waltjr50

I have implemented the above tracking and it works great. I have it call from "before update" on the form. However, it does not track changes to the subform. When I try to call the module from the subform I get an error "type mismatched". how do I tract the subform changes?

elminiawi
elminiawi

1000000000 Thanks you are the man

fdickman
fdickman

This works fine for me on any kind of control. However, I have the problem with changing something to a null value, and I can't figure out from what's been posted how to deal with this. Any further clarification would be greatly appreciated!

Datawrecker
Datawrecker

I've managed to get this code running on a fairly basic database. What's annoying me is that it falls over so easily. I share Irprogram's problem of not being able to get this to work with text boxes and combo boxes simultaneously, I cannot get the code for this functionality (supplied in the article) to run. The other issue I'm having which seems a bit odd is that it throws up an error message when a date field is changed (Error 3251 again). The date field on the form is just a text box, so why doesn't the formula just treat it as text?

kacharris
kacharris

would this work for MS SQL? If not any suggestions?

justinhyp2342
justinhyp2342

I get a syntax error (missing operator) in query expression. This seems to happen in the fields with more text than the others. Anyone have any ideas how to fix this? Why it is happening? thanks!

Irprogram
Irprogram

Ive been trying to use this for the last week, whenever I use the code for Text OR Combo Boxes it works like a charm! But if I tell it to track both of them, Text AND Combo. No matter the order, if its Case or a Listing I get a "Runtime Error 3251 Operation Not Supported" and it will still record the text, never the combobox. (Access 2000)

bigIIHII
bigIIHII

how can we make it track new records too ?

djmadie.m
djmadie.m

Can someone help me with changing this code to update for a List Box? I've tried changing "acTextBox" to "acListBox", but this doesn't work. Thanks!

pcgotoguy
pcgotoguy

I love this code and am very thankful to whomever posted it. There is one thing I did notice about it though that I wish someone would fix. When a user makes "any" changes to existing data they are prompted to save changes and if they choose "no" it posts whatever changes that took place in the field to the Audit table... As if the changes really did take place! This is not really necesarry seeing that no changes really were saved to the database! If anyone has a fix please email me: pcgotoguy at gmail.com

suckerpunchnz
suckerpunchnz

Hey. I would like my audittrail to track the access user name logged in, rather than the user logged into the pc. Is anyone able to help me please? Thanks heaps. Phil.

bbuchert
bbuchert

I need to track CheckBoxes on a form where the values have changed from being check or unchecked. This code doesn't seem to allow for that? I tried changing the "If .ControlType = acTextBox Then" to "If .ControlType = acCheckBox Then" but it gives me the following error: Object doesn't support this property or method (Error 438) Anyone have any ideas as to how to track value changes for checkboxes on a form as well as text boxes, comboboxes, etc..? Any help would be greatly appreciated.

plunavat
plunavat

Hi Susan Harkins, The article has really helped me a lot. Thanks for the same. The solution you mentioned tracks the RecordID(Primary Key). The code works fine with table having single Primary Key. Could you please let me know what would be the changes in the code if the table is having two or more Primary Key. Thanks in advance Regards Pranav C Lunavat

libraguy
libraguy

Sorry folks, I am new to Access Database and I am very interested with the audit database. I try to follow the step here but it's not working. Please help. Some question as below: 1. The Audit Table should be created as the new MDB or it's had to be in the same database? 2. How can I check the "Me identifier"?

CEHinman
CEHinman

I'm having a problem with it... Added it to a DB I have seemed to be working fine... BUT.... Was causing major headaches when I went in to add and edit this DB.. Then after I had finished.. I keep getting error message...."error "Operation is not supported by this type of object. 3251.........." This error stops after removing the code for the audit trail... Anyone have any ideas why????

nadine
nadine

I pasted this code into my database and got a ByRef Argument Type Mismatch Error. The Help screen seemed to suggest that type was important but I do not know where the problem is. As you can probably guess, I am new to VBA.

Tony Hopkinson
Tony Hopkinson

in your access aplication. If so just adapt the provided solution. ie detect the the date has changed and insert a record in to another table with the data you need. If it can be changed from 'anywhere' then you are SOL and need to pick a proper DBMS that implements triggers as your backend. e.g. SQL Server. Or you change your schema and take DateAttend out of whereever it is and have an AttendanceDates Table, which might make more sense.

ProfessorKaos64
ProfessorKaos64

I get object required 424 error on going to the next record/saving...

robwaltersii
robwaltersii

If Nz(.Value, "") Nz(.OldValue, "") Then

waltjr50
waltjr50

Change your if statement from: If .Value .OldValue then To: If (.Value .OldValue) Or (Not IsNull(.OldValue) And IsNull(.Value)) Then

JLAProgramming
JLAProgramming

If you are using MS SQL as a backend DB, place a trigger on the table and be done with it! You can track all Inserts, Updates, and Deletes regarless of the form, ctrl type, and the accuracy will be 100%.

justinhyp2342
justinhyp2342

I had some quotes in my fields. For measurements I had 7"x6". VB doesn't like that. So I replaced " with IN. Now, can I track when a field is deleted all together?! It doesnt track THAT change! A mighty big one.

jubiiab
jubiiab

It's a old question but since you haven't wrote the answer i guess you haven't found it out yet??? I just downladed this audit and had the same problem. You just go to vba code and find: & cDO & environ("username") & cDQ & ", "_ 'replace it with this: & cDQ & CurrentUser() & cDQ & ", " _ That will work! ;)

bbuchert
bbuchert

I am trying to use the Select Case option that was described below the Main code posted in the "A simple solution for tracking changes to Access data" artical. The strange thing here is the .ControlType value is returning a value of "100" instead of "109" for a textbox or "106" for a checkbox? After doing some research I found out that the acLabel constant is "100". I'm not making any changes to a label on my form? Does this make any sense?

jstribling
jstribling

The original code from the article is looking only at textboxes -- you will need either adapt this code to work with checkboxes or write a second procedure to handle checkboxes separately ( I would probably just add some code to handle the checkboxes) I hope this helps --

jstribling
jstribling

bbuchert, It seems that you may be looking at the wrong thing. Here is a little sample that shows a textbox on a form to display the value displayed by the checkbox: Private Sub Form_Current() If Me.chk1.Value = 0 Then Me!txtCheckBoxStatus = "The value displayed by the checkbox is 'False'." ElseIf Me.chk1.Value = -1 Then Me!txtCheckBoxStatus = "The value displayed by the checkbox is 'True'." End If End Sub Of course, the checkbox is bound to a Yes/No data type field in the forms's record source If you test for the value of the checkbox then you should be OK. I hope this helps --

shimmer2
shimmer2

Can anyone tell me if this code works when a value has been removed and the field is blank?

cheryl.monroe
cheryl.monroe

I'm getting this same problem in one database I'm implementing this solution in... but I didn't get it the first time I did it in a more complicated database (which works beautifully!). I cannot for the life of me find the problem. I not really a VBA programmer and will be taking a class soon. Any other hints on this error message?

nadine
nadine

User Error - that is all it was. The ID number needed to have exactly what was in the properties box for that ID. In this case tablename_ID. Also I discovered that I needed to add the code to call the subprocedures to my subforms as well. Thanks for the code. It is more efficient IMHO than adding code to each control.

jstribling
jstribling

I modified the original article's code as an example -- this is probably not the way I would do it in "real life", but it might help to make things a little clearer: Sub AuditTrail(frm As Form, recordid As Control) 'Track changes to data. 'recordid identifies the pk field's corresponding 'control in frm, in order to id record. Dim ctl As Control Dim varBefore As Variant Dim varAfter As Variant Dim strControlName As String Dim strSQL As String On Error GoTo ErrHandler 'Get changed values. For Each ctl In frm.Controls With ctl Select Case .ControlType Case acTextBox If .Value .OldValue Then varBefore = .OldValue varAfter = .Value strControlName = .Name 'Build INSERT INTO statement. strSQL = "INSERT INTO " _ & "Audit (EditDate, User, RecordID, SourceTable, " _ & " SourceField, BeforeValue, AfterValue) " _ & "VALUES (Now()," _ & cDQ & Environ("username") & cDQ & ", " _ & cDQ & recordid.Value & cDQ & ", " _ & cDQ & frm.RecordSource & cDQ & ", " _ & cDQ & .Name & cDQ & ", " _ & cDQ & varBefore & cDQ & ", " _ & cDQ & varAfter & cDQ & ")" 'View evaluated statement in Immediate window. Debug.Print strSQL DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True End If Case acCheckBox If .Value .OldValue Then varBefore = .OldValue varAfter = .Value strControlName = .Name 'Build INSERT INTO statement. strSQL = "INSERT INTO " _ & "Audit (EditDate, User, RecordID, SourceTable, " _ & " SourceField, BeforeValue, AfterValue) " _ & "VALUES (Now()," _ & cDQ & Environ("username") & cDQ & ", " _ & cDQ & recordid.Value & cDQ & ", " _ & cDQ & frm.RecordSource & cDQ & ", " _ & cDQ & .Name & cDQ & ", " _ & cDQ & varBefore & cDQ & ", " _ & cDQ & varAfter & cDQ & ")" 'View evaluated statement in Immediate window. Debug.Print strSQL DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True End If End Select End With Next Set ctl = Nothing Exit Sub ErrHandler: MsgBox Err.Description & vbNewLine _ & Err.Number, vbOKOnly, "Error" End Sub I hope this helps...

jubiiab
jubiiab

you can have null values. But you need to make small changes in the code.

henley12
henley12

I'm getting an error 'You entered an expression that has no value. 2427'. Any idea what that means?

ProfessorKaos64
ProfessorKaos64

This was a great help! Could you help me in adding a section for comboboxes/listboxes??? Thank you!

shahid_L
shahid_L

Please add below line code after acOptionGroup, acComboBox to fix this error: Select Case ctl.ControlType Case acTextBox, acCheckBox, acOptionGroup, acComboBox If .ControlType = acTextBox Then 'THIS IS THE CODE

Editor's Picks