Sunday, July 15, 2007

Tracking changes in Access

Here is a method to track edits in MS-Access. The goal is to record the "who what when where" of edits made in an Access database. Since Access does not have triggers like SQL Server, we will be working at the Form level.

NOTE - UPDATED 11/11/2007
added table name to tracker

To keep things readable within the blogger format, you'll need to visit three links that will display details of what is described here.

First, you will add three objects to your database:
1. A VBA module with Windows API calls to identify the user and PC name. This code has been tested with Windows 2000, XP and Vista 32-bit editions.
2. A VBA module to examine the current record & determine what has changed to the record
3. A table to store the results. For text fields, I've indicated the length in the Description. Here is a VBA module that will create the table for you.

After adding these objects, modify each form as follows:
1. Set the Tag property for the form = the name of the underlying table.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.
3. Add the Form_BeforeUpdate event and invoke the tracking code using:

TrackChanges Me

4. If you are using subforms, you'll need to perform these three steps for each subform as well.

UPDATED 09/24/2008 - IMPORTANT NOTE
You can run a Sub in Access by using either

TrackChanges Me

or

Call TrackChanges(Me)

but you cannot use

TrackChanges(Me)

that will result in a compile error.

NOTE - UPDATED 05/23/2010
tracking blank or null fields

There was an important note left out of the original post. In order to properly track when a field is blanked-out, the fields in the tracking table Field_OldValue and Field_NewValue should be set for AllowZeroLength = True. The updated "create table" code is here:

http://www.wvmitchell.com/blogger/Create_tbl__ChangeTracker.htm

if you create the table manually you will need to set that property in the design view.

118 comments:

Anonymous said...

When I go to the design view of the form and type TrackChanges Me in the before update option and then go to data view to change the data, it just gives me an error message. I was wondering what I am doing wrong in step 3?

Bill Mitchell said...

What does the message say, and what is the error number? Does it display a dialog box to Debug / End or anything like that? If you can get into Debug mode, does it highlight a line of code, if so which one?

Anonymous said...

I get a compile error. This is what I input. Thanks,

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call TrackChanges Me

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit

End Sub

Bill Mitchell said...

Take out your error handling code and then run it again. It will stop and highlight the line of code that has the problem.

Anonymous said...

This is what I put in. It gives me a compile error, syntax error for "Call TrackChanges Me".

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call TrackChanges Me

Form_BeforeUpdate_Exit:
Exit Sub

End Sub

Bill Mitchell said...

That's the problem - you can use either

TrackChanges Me

or

Call TrackChanges(Me)

when you call a Sub.

Anonymous said...

Thank you for your help. Worked like a charm.

Anonymous said...

A question on this code --- Will it track changes made when VBA code is used to add a date when a user clicks on a button? Another audit trail module could not as it was tracking changes at the control level and VBA updates do not trigger the needed event. Guess really wondering if the form_ BeforeUpdate would.

Bill Mitchell said...

No, it will not track changes made by VBA code. You would need to add to your VBA code so it writes to the change-tracking table.

Karen said...

Bill, I am the last poster. I can not get the audit trail to work. It never runs the code. I have put in the two tags on 2 forms, one very simple just for testing. I have on the BeforeUpdate event this code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
TrackChanges Me
End Sub
I have created the tbl_changeTracker and added the two modules. I have put a break point on the Code above where it should call TrackChanges but it does not. If I put a msgbox below it does call it and displays the message. I know the UserName function is working and CompName. No error messages, just nothing happening, no code break and no changes to tracking table. Any thoughts?
Karen

Bill Mitchell said...

Put your breakpoint right on the TrackChanges line, and then when it reaches that point, press F8 repeatedly to step thru my code to see where it fails. If it doesn't hit that breakpoint then something else is wrong.

Karen said...

Bill,
In trouble-shooting, I do have the code working now in a test database with just one form and one table plus your tracking table. Still don't know why the "real" database is not calling the sub but will work on that some more. I created the audit table manually and found you had used two undercore characters in table name and I had only used one. So saw it was not setting the recordset correctly and when changed to correct name the audit table did get written to. Now to get it to work in production db. I really like your code because it is so simple but gets all the controls on the form. It DOES pick up the auto-writing of today's date on the many date fields I have. I used a button to allow them to put in today's date and hoped the BeforeUpdate of the form would pick up the change in the values which it does. So I am VERY happy. Thanks so much for this code.

Karen

Bill Mitchell said...

Hi Karen, glad it worked.

My personal preference is to use the prefix tbl for regular tables, tbl_ for lookup tables, and tbl__ for settings, audit and other system type tables. Keeps them grouped together in the db window.

Anonymous said...

hi bill
ive just used your example but when i compile im getting a complile error highlighting track changes me, saying that it expected a variable or procedure, not a module.
any idea on what i could've done wrong?

Bill Mitchell said...

You can use either

TrackChanges Me

or

Call TrackChanges(Me)

when you call a Sub. I prefer the first method, but either one works. What does not work (does not compile) is

TrackChanges(Me)

Anonymous said...

Thanks! One of the first borrowed pieces of code that I tried out and that worked on the first time in my application.

Anonymous said...

Thanks Bill.
Your code works great - even got it to work with a combined key using a text and a number field! All I had to do was Dim MyKey As String instead of Long.

Happy Accesser.

Anonymous said...

I'm not an "Access" guy, just a networking one. The finance dept set up an access database with auditing and the file is 13x the size it was last year (116MB vs. 9MB). My remote location is complaining of latency when opening/editing the file. Will your way of tracking keep the file size reasonable?

Bill Mitchell said...

It is very common for an Access database to grow over time. After you make a backup, for Access versions up to 2003 you can go to Tools ~ Database Utilities ~ Compact and Repair Database. In Access 2007 click the Office button and under Manage... you'll find a similar option. That might solve your problem.

Ryan said...

I have added the modules and made the changes instructed. However, the change table will not store the value within the primary key control. I am using a combo box to pull the record but I tagged the primary key text box which merely reiterates the value in the combo box as the "PK". Why would it not store the value?

Bill Mitchell said...

Ryan, what is the purpose of the combo box, are you trying to change the value of the primary key? If you need more help, go to my web site wvmitchell.com and send me an email if you like. . .

Boyd said...

I am using the MS Access 2007 Project template which has a tasks subform.

On the project details form, the tasks subform displays as a datasheet in a tab. Changes to the main form write to the table, but changes to the subform do not work. No error, no nothing, it just doesn't write.

I've tripple checked all the steps but something isn't working. How do you suggest I go about troubleshooting?

Boyd said...

I am using the MS Access 2k7 Projects template and I am trying to get the audit trail to work on a subform.

I've followed all the steps, and it works for the main form, but not the subform. How would you suggest troubleshooting?

Bill Mitchell said...

Boyd, I would insert a breakpoint (right-click in the left margin) near the top of the code module & then make an edit in the subform - VBA will stop at your breakpoint. Then press F8 to step thru & hover over the variables to find the problem. If you're not sure what to look for, do this in the main part of the form first because you said that part does work.

Anonymous said...

I'm using this code and I love it, however it's making my db exponentially large. Is there a way to designate the destination table as residing in a different database?

Bill Mitchell said...

Yes, the tracking table can be moved to an external mdb file and then linked in to your application. I will put together a short article to do the moving & post it in a few days.

Meanwhile, make sure that you perform periodic maintenance on your file - read the post at
http://wvmitchell.blogspot.com/2009_02_01_archive.html for an explanation of compact and repair for Access.

Bill Mitchell said...

June 20, 2009 - posted code to move the tracking table to an external file - search this blog for "External Change Tracking".

Anonymous said...

Bill,
How can have the code only run when a field on the form is null?

Bill Mitchell said...

Have not tested but this should work. Locate the line:

If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

if you want to track when the old value was null, use this:

If Nz(ctl.OldValue, "") <> Nz(ctl, "") And Nz(ctl.OldValue, "") = "" Then

if you want to track when the new value is null, use this:

If Nz(ctl.OldValue, "") <> Nz(ctl, "") And Nz(ctl, "") = "" Then

Anonymous said...

Hi
I have got the code working but it does'nt record when fields are empty'd (ie hitting the delete button) or filled from scratch. It ony records if existing data is altered. I would appreciate your help

Bill Mitchell said...

It should track both of those types of change. Maybe you could set a breakpoint at the line

If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

and inspect the old & new values for the field. You could also download the code directly from the link in the above article to rule out any typing errors. HTH

Anonymous said...

I have done the above information but I dont understand the following.
1. Set the Tag property for the form = the name of the underlying table.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere.

Unknown said...

I am using your code and it works great on most of my forms. I have one form that has two tables in the record source and that seems to be causing me an issue. When it tracks a change made in one field on this form it tracks every field on the form, whether there were changes made in those fields or not. Any suggestions?

Anonymous said...

Bill, How would you get this code to work on a junction table where there are two foreign keys and neither of them are the same as the main Primary Key on a main form.

I have this code working fine with subforms of the main form where there is a link between the Primary Key on the mainform and the foreign key on the subform. Introducing a junction table, however has thrown me for a loop.

Bill Mitchell said...

Anonymous --
1. Set the Tag property for the form = the name of the underlying table. -- in design view, click View ~ Properties and click the tab Other -- locate the Tag property and set that to the name of your form -- that allows the tracking code to record the name of the form used to make the edit -- useful if more than one of your forms allows edit to that same data.
2. Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere -- place a text box on the form with Name = your primary key field, ControlSource = that field, and Tag = PK -- set Visible=No -- that is how the tracking code finds the key.

Bill Mitchell said...

sgtsortor --
Are fields from both tables available for editing? I've never used the tracking code in that scenario. It does work with form / subform combinations you just have to call the code from each form / subform. You might try putting
Debug.Print Screen.ActiveControl.Name
in the BeforeUpdate event; that event is firing multiple times so maybe that will help figure out why.

Bill Mitchell said...

Anonymous at 8:55 AM --
Look at my comment to sgtsortor above re: the debug statement might help figure things out. You might also want

Debug.Print Screen.ActiveForm.Name

since you are working with multiple forms. HTH

Bacca said...

I've developed a database at work using this form and the IT people want to move it to SQL Server.

How do I reference the database in the line:

set db = "\\database\databse.accdb "

Will I need to change any other lines?

Bill Mitchell said...

You would move the tracking table to SQL Server, then attach that table in Access. The "Set db" stays as-is, but change the next line to:

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset, dbSeeChanges)

the rest of the code should be fine.

However, it will not track NEW records properly due to the different behavior between an Access autonumber and a SQL identity - in Access the number exists as soon as you begin a new record, but in SQL the number does not exist until you SAVE the record; so new records will not have the PK.

Bacca said...

Thanks Bill, that worked, kind of for items on the mainform but any subform that triggers the code I get an ODBC 3146 error. The debugger stops at the rs!update line of code.

I'm reading the article at http://support.microsoft.com/kb/164481 and am wondering if I need to change it to something like

Set rs = db.OpenRecordset("tbl__ChangeTracker", dbOpenDynaset,
dbSeeChanges+dbRunAsync, dbOptimisticValue)

Also, can you recommend an alternative audit solution if you don't think this scales well. I didn't know the db would be such a success :)

Bacca said...

I'm with you now, is there any way to force SQL to create an identity like access does or can I change the trigger to a different event like AfterUpdate?

Bill Mitchell said...

Bacca at 7:47 - did you solve the 3146 error with the extra parameters? Also, re: scaling - did you move the tracking table into SQL or keep it in Access?

Bill Mitchell said...

Bacca at 9:20 - we use BeforeUpdate because at that point you can get both the before & after values - AfterUpdate only gives you the new (after) value. Re: numbering, you could create a lookup table that holds the last # used for each table, and get rid of the identity / autonumber. When the user is creating a new record, you update the # in lookup table, and then create a blank record with that new number.

Anonymous said...

This work very well on most of my forms.
One form, however, is based on a query on three tables (don't ask!).
I can't get this form to track - any thoughts on how to Tag?

Bill Mitchell said...

Re: 3 tables in query - which table is being edited? That's the key you want to use.

Anonymous said...

GOT IT!! Thank You!!

Anonymous said...

Would the program also track changes made to tables that are changed directly without the intervention of a form?

Bill Mitchell said...

No.

RyanG said...

I have a few fields that are set up as combo boxes. I have it this way to restrict what the user can input. The track changes table doesn't log the "old values" and the "new values" for the combo box fields. It only logs these values for text fields. Do you have any suggestions for fixing this? Thanks in advance.

Bacca said...

RE: 7:47 the dbseechanges is what was needed.

RE: 9:20 Are there any easy SQL Auditing solutions you can think of or am I going to have to crash course SQL triggers?

I'm using MS SQL Server

dancingwwaves said...

Hi, Bill!
How do I tag the "PK" when the underlying table's PK is a concatenated key? Thanks!
~Becca

Bill Mitchell said...

dancingwaves - place a hidden text box on your form and set the controlsource = the same concat field expression as your PK

Bill Mitchell said...

Bacca at 8:12 - a trigger would give you the most control over auditing but you need one for each table to be tracked. as far as learning triggers & sql in general, there is a great forum at sqlservercentral.com you should check it out.

Bill Mitchell said...

RyanG - I have seen that too: some combos will track, others won't but not sure why. The diff might be where the first column is/is not bound to the dataset.

dancingwwaves said...

Hi, Bill!

Dancingwwaves = Becca = me.
I got the concatenated tables to work.

But I still don't understand - can I get your code to track when a record is added? deleted? when a blank cell is filled? or a filled cell is changed to null?

Thanks!
~Becca

Anonymous said...

Hello i finally found what i am looking for but i am having issue getting it to work. I think my problem is this part of the VBA. I dont understand waht should be my Tag and whatshould be the PK. I would appreciate if you would be able to explain. My Access experience is very simple but this would help alot.

MyTable = .Tag

' find the primary key & its value, based on the Tag

For Each ctl In .Controls

If ctl.Tag = "PK" Then

MyField = ctl.Name

MyKey = ctl

Exit For

End If

Bill Mitchell said...

Anonymous at 7:37 --
In Access the primary key (PK) is the field that uniquely identifies each record. In the design view of a table, a gold key symbol indicates the primary key. Most of the time you would use an autonumber so Access can manage that unique number series for you.
The Tag is a property that you can set for a Form or Control. This is totally up to the developer.
The way my code works: 1) you set the Tag for the Form = the name of the table being edited, and 2) you place the primary key field on the form, you can make it Visible = False, and set the Tag to "PK" (without the quotes)tag for the Control. My code 1) reads the tag for the form & knows which table is being edited, and 2) loops thru the tags for the controls & when it finds PK, it knows that the value of that control is the primary key.

Anonymous said...

Thanks for this, Mr Mitchell, this is only method i could find that tracks changes at subform level as well as form level.

mp

Anonymous said...

I was able to use the module to create the table, but continue to receive a compile error "Expected variable or procedure, not module".

Anonymous said...

Got past the compile error. But not the code does not write the changes to the tbl__ChangeTracker table. I dedugged the code and it appears to be going thru each line of the code but not writing the changes to the ChangeTracker table. Need Help..

ms

Bill Mitchell said...

Hello Lee W,
I replied to your email but it came back...
Two things –
First, you have a dot between “TrackChanges” and “Me”, it should be:
TrackChanges Me
Also, this code should be in the BeforeUpdate event, that’s when you can access both the prior and current values of the controls. (Not the BeforeInsert event.)

Bill Mitchell said...

ms at 1:02 PM --
Good to know that you got past the compile error, but not sure why the tracking records are not being saved. Can you 1) verify that you have a reference to the DAO library and 2) make sure that you have the rs.Update statement.

Anonymous said...

Sure, how can this be verified. Side Note - the first line in the VB code "Attribute VB_Name = "A70714 track changes" had to remove. This line cause the code not to compile. Any suggestions??

ms

Anonymous said...

Mitchell,

Investigated that discovered that the Microsoft DAO 3.6 object library is referencing the wrong version file for Access 2007. I believe this is why the updates are not writing to the table. I do have the rs.update statement in the module. Trying to find the correct approach to reference the DAO library to the correct file. Any suggestions??

ms

Anonymous said...

Bill,

I am using Access 2007 and I checked the Access reference library and it displays a library reference of:

Microsoft Office 12.0 Access database engine Object Library, point to file location: c:\program files\common files\microsoft shared\office12\acedao.dll.

Tested code again, and changes do not write to the tbl__ChangeTracker table. Any ideas??

ms

Anonymous said...

Bill,

Thanks for your code, and I know that it works because I went thru debugging and can see that the code works, however something is not working with writing the updates to the table. I have found an altenate method to track changes, which is working for me. Thanks again.

ms

Anonymous said...

I copied the 3 vba object code into 3 modules as per the instructions.
Then added the TrackChanges Me to the afterupdate event of the main form, then when I make a change to data the tbl_trackchanges is created but no information is written to the table.
I don't get any error messages, so what have I done wrong.
Thanks

Freeda H said...

I love the code and it works beautifully.

But is there a code that can be inserted into the Module so that it will not track new records?

I only want to track changes to existing records. I already have the details of who created the record and when so I don't need that information in the audit trail.

Thanks in advance.

LD said...

Bill, your code works beautifully right out of the box! Thank you!

Like Freeda H, I too would like to only track changes to existing records and not track new records. Were you able to provide this code?

Bill Mitchell said...

Thanks for the nice comments. Have not written a version to skip new records but have an idea to use the BeforeInsert event. Will post an update when that's ready.

Anonymous said...

Hi Bill. I would like to track the user name (empuser) that is required for each user to login into my database. I have a login form and an ubound text box that collects the user name. I think I have to store empuser as a public variable in another module in order to add this value to the tracking table (public empuser as string). Then I think I would add a line of code in TrackChanges after rs!CompChanged = CompName(), but I don't know what that code looks like. Can you help? Much appreciated.

Bill Mitchell said...

Anonymous on 10/13/10: If you want to track the "empuser" in addition to the Windows ID then you need to add another field to the tracking table. If you call it empuserChanged then you would add this line to the VBA module:

rs!empuserChanged = empuser

Anonymous said...

I added the field to the table and I have a module with the following:

Option Compare Database
Public empuser As String

. . . but it's not writing the user name to the table. Any ideas?

Bill Mitchell said...

after this line:

rs!CompChanged = CompName()

you also need to add this:

rs!empuserChanged = empuser

Anonymous said...

. . . yes, did that already. Do I need any code in the procedure in which the username is entered, e.g. do I need to assign something or declare something? Thanks Bill.

Anonymous said...

Hello Bill,

Great code! But I have having the same problem as other. The table will track changes/edits to existing values but if I am adding to a field that is blank or deleting data so the field ends up blank I will not get the table to track these changes.

I just copied and pasted your codes but it will not track additions or deletions. I saw others ask the same question but couldn't find the best answer.

Bill Mitchell said...

re: empuser -- you said "I think I have to store empuser as a public variable in another module in order to add this value to the tracking table (public empuser as string)."
Yes, you do need to do that, in addition to adding the line of code
rs!empuserChanged = empuser

Bill Mitchell said...

re: adding to a field that is blank or deleting data -- the original article did not mention this -- Field_OldValue and Field_NewValue need to have AllowZeroLength = True, in order to track when a blank field is filled-in or a field is erased -- the download code has been updated, but if you created the tracking table manually you need to make that change.

Donna said...

Hi Bill. I'm not getting your code to compile on the statement 'Dim db as DAO.Database, rs as DAO.Recordset'. The error is 'User-defined type not defined'. What am I doing wrong? I am using Access 2007 and am a relative newbie to VB.

Bill Mitchell said...

Donna, in the Visual Basic window, click Tools ~ References; scroll down to find "Microsoft DAO x.x Object Library" (the x.x might be either 3.6 or 4.0); check the box and click OK.

Anonymous said...

Just a pointer:
In your code in:
If Nz(ctl.ControlSource, "") > "" Then ...
If you replace it with:
If Nz(ctl.ControlSource, "") > "" And Left(Nz(ctl.ControlSource, ""), 1) <> "=" Then ...
You make sure that you leave out changes from calculated fields!
Hope it's useful.

Bill said...

I have this working fine and easily however I have an option group with 2 checkbox selections. It does track a change to it fine but it also posts a record for each checkbox on each update no matter what field. Any thoughts?

Bill Mitchell said...

Change your case statement to:

Case acTextBox, acComboBox, acCheckBox, acOptionGroup

to capture changes for that control. Then, for the checkbox section try this:

If ctl.Parent = 1 Then
' it is part of option group
Else
If ctl.ControlType = acCheckBox Then
...
End If
End If

the .Parent property only exists if the checkbox is part of an option group. This code might not work as-is but hopefully it will help you figure it out.

Anonymous said...

Sir,
I have tried your code and it works great for using Forms without subforms. I've read all the comments and I'm still having issues with tracking subforms.
can you provide a vba example for audit tracking on a main form and it's subform?
Thank you very much,
Abe

Bill said...

While this process works fine, I decided I really did not want this much overhead storing records on every change. I prefer to select certain fields and set them to be tracked and ignore the other less important ones. Perhaps I will have to use before update code on each of the selected fields? Can you suggest some example to use? OR an adaptation of what you provided? Thanks a ton.

Bill Mitchell said...

Anonymous on 2/4/11 -- use the exact same technique on your subform -- tag the PK field and use the BeforeUpdate event to run the code.

Bill Mitchell said...

Bill on 2/7/11 -- we are using

If ctl.Tag = "PK" Then

to mark which is the primary key, so in a similar way you might try

If Nz(ctl.OldValue, "") <> Nz(ctl, "") And ctl.Tag = "track" Then

...or some variation. You would then Tag which fields you wish to track.

Anonymous said...

Just wanted to let you know, this is great! I have looked at a few other ways to track changes in Access and this is the easiest I've seen. And you don't have to track every field when a change is made to only one. I was very excited when I put this in and it worked like a charm.

Thank you!

Unknown said...

Hi Bill,
I am not proficient with coding and would like to get this to work. I have a database I created and after I followed your steps it does not run. Does the modules that are created have to have a certain name? I am using Access 2007 if that matters.
Thanks

mz

Helen said...

I have added the three objects (2 VBA Modules and 1 table) and modified the tag properties. I have tried both TrackChanges Me and Call TrackChanges(Me) in the form's BeforeUpdate event, and I get the message "Access can't find the object 'TrackChanges Me' (or Call etc if I use that one). When I go to the VBA screen, I can see the "Sub TrackChanges (F as Form)" etc. in Module 2 that I copied from your instructions. What have I done wrong? Should I take out the (F as Form)? My knowledge of VBA is very limited. Also, this database was converted from Access 2003 - could that be the problem?

Anonymous said...

This has been a tremendous help for me. I'm a brand new access user and have never used code before but was able to get this to work. Here's my question. I want to be able to tie the changes to a specific record so that I can query all changes made for that specific record. I've added PK to the primary key field but in the table it doesn't show the data in that field, instead it shows the name of the primary key. For example I want it to show the actual VIN # for the record but it just shows "Vin #" for all changes.

Anonymous said...

I need help with access 2007 vba. I am trying to copy a recordset field value to excel, see code below:

With rsworking3.Fields(3)
DoCmd.RunCommand acCmdCopy
With oSheet
.Range("B49").Activate
.PasteSpecial
end with
end with


osheet is set to the active sheet in the excel workbook.

The copy and pasted worked a few times, but now I am getting an error of "run time '1004' Method 'PasteSpecial of object'_Worksheet' failed. Any ideas? also I need to clear the clipboard after the first copy and paste of the recordset value.

Anonymous said...

Can you please explain that on video on youtube?

MF Truesdale said...

Hi BIll, Thanks for this code.

I would like to add another identifier to track individual records. I have this Unique identifier on the form (JACodes) -how do i get JA Codes to show up on the changes table?

Thanks

Anonymous said...

Hi,
I tried a similar code found at http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/6166807, but it do not work with multiple value list/combo, nor with option group. What about yours ? Did you check it ?

Thanks for your reply

Amz said...

This is great, works well except that i get the error 2447- invalid use of .dot - can you help please?

Bill Mitchell said...

Anonymous on 07/20/11...re: option groups, look at my comment on 1/28/11 how to track those...re: multiple value controls, I don't use 2007 or 2010 but if you find out the constant for that control type, you should be able to include that in the Case statement.

Bill Mitchell said...

MF Truesdale...if you are only tracking one table, then you could add another column to the audit table to save JACodes...otherwise you would need to write a query to join on the ID.

Bill Mitchell said...

Anonymous on 4/8/11...look at my comment above...the tracking table only saves the primary key, so you need to write a query to join from the tracking table to the table being audited. The best way to use the tracker is for every table to use an autonumber as the primary key, because it never changes, and use that to look up data changes to specific tables & fields. If the VIN # changes then that would break the link between the audit & data tables.

Mike B said...

Thank you so much for this, it works great. The one problem, however, that I do see with it is that I do not see it track changes when a deletion is made is DataSheet view. Is there any way to track a DS View deletion?

Thanks!

Mike B said...

*in* datasheet view, hehe

Anonymous said...

How can I use the track changes code to identify the user from an Access 2007 login form and not using the Windows API. I need the users to login to the application via Access login table (for validation) and then track the changes that are made per user login into the application. Is this possible?

John Desselle said...

I'm having difficulty in getting past this line of code:
If Nz(ctl.OldValue, "") <> Nz(ctl, "") Then

Gives Error 3251 - Operation not supporte for this type of object.

Anonymous said...

Hi Bill,
thanks for your code. It's working for my db! I have one question; I have several fields that are stored as a number but are visible on a form as text via a dropdown box. Your code logs the change for these fields as follows: old value 2, new value 1.
However, I would like to see the text value for example: old value "Xz", new value "Xv". Is this possible at all?
Kind regards.

Bill Mitchell said...

Anonymous on 9/20/11 - you have to write a query that joins the audit table to the lookup table if you want to see the text (display value).

Greg said...

Hi Bill,
Your code works great, many thanks!
Now, I'd like to see next to each fields of my form a text "Updated by USERCHANGED on CHANGED ON"
How could I do that? Not too good with expression builder.
Many thanks,

Anonymous said...

Hi Bill,

Fantastic code, thank you very much!

I am trying to Implement it on an Access db 2010...almost done, but I am stugling with the delete rows...any suggestion?

George

Chris said...

Bill,

Thanks so much for posting this extremely helpful and simple to use solution. It does exactly what I need to do in tracking changes in a subform, something that the MS KnowledgeBase solution did not allow me to do.

I had the same problem as one of the other users, not noticing that the table name had two underscore characters, so I was frustrated for most of a day until I figured that out, and had clear sailing after that. I'd suggest a comment line in the code to point out the fact (for those who build the table manually, as I did - since the instructions for that were simple to follow, too) that the table name has two underscore characters.

Other than that one gotcha, this was one of the easiest solutions for doing anything in Access that I've ever seen. (VBA I can grok in Excel, but Access is still Greek to me.)

Thanks again.

Anonymous said...

Bill,
Thank you very much for the code. Is it possible to write the second column of a combo box so the results in the tbl__ChangeTracker are more meaningful without having to go further look up queries? My form has 25 combo boxes! (I couldn't see this mentioned in the comment blog but I apologise if I missed it anywhere.)
Thanks again. RJ

Jason Loukes said...

Bill,
Thanks for this code! I am seeing some unusual behavior, however. I am finding multiple, duplicate entries in the tracker table from users and they are time-stamped within a minute or so each other. There is no change in the before and after values, just the time stamp.

Any idea on what would cause the event to trigger multiple times but not progress to completely updating the record?

Thanks,
Jason L

ScorpioMCN said...

Thank you Bill!!! This code is great! Works well for us.

Bill Mitchell said...

on 5/3/12 Jason said "Any idea on what would cause the event to trigger multiple times but not progress to completely updating the record?"
...The code runs when the BeforeUpdate event fires; so if the user does not complete an update, you will still have a record of the attempted update. IOW it does not actually compare if the old vs. new values are different.

Bill Mitchell said...

Way back on 8/15/11 Anonymous said...
"How can I ...identify the user from an Access login form and not using the Windows API."
If you are using Workgroup Security you can get the user name from this:
DBEngine.Workspaces(0).UserName
OTOH if you have built your own login system then you'll need to write a function that returns the user.

Unknown said...

Bill, I can get the code to work flawlessly for one table and form but when I try to apply it to another table and form in the same database I get no results in the tracking table for that form. Any sugestions.

Eric said...

Bill, I have been able to apply the code to one table and form for that table flawlessly but when I try to apply it to another form and table in the same database I get no results in the tracking table for that form. I also get no error codes. Do you have any suggestions?

Anonymous said...

Hello Bill, the code works perfectly.
but it doesn't record the changed of a combo-box that allows multiple values.

Anonymous said...

Hello Bill im using access 2016 and cant get the code to run. Im using the beforeupdate event in the form but it seems like the code never gets called. Any ideas as to what should i try?

Bill Mitchell said...

I'm using this in production with Access 2016 32-bit.

We need to find out if 1) the code is never called or 2) the code is not writing the audit trail.

For #1 you can place a breakpoint in the TrackChanges module, to find if the code is being executed. If not, you need to verify your tables & code match mine.

For #2 note that we have an On Error Resume Next (to hide any possible errors from the user) so you can try setting Break on all Errors = True to look for the problem.

Don't forget that you need to set this up too:

"Set the Tag property for the form = the name of the underlying table.
Identify the primary key for the data behind the form, and set the Tag property = "PK" (without the quotes). The field does not have to be visible on the form, it just needs to be there somewhere."

HTH