Tracking Changes to Data in Microsoft Access - Audit Trail - Log User Activity - Record Edits

Do you want to keep a log of any changes that are made to records in your database? In this video, I'll show you how.
Allison from Tennessee (a Gold Member) asks, "I've got a few different users who work with my database. Is there a way I can track what information they change?
LEARN MORE:
Append Query: 599cd.com/append
SQL Basics: 599cd.com/sql
Security Seminar: 599cd.com/secu...
BONUS FOR CHANNEL MEMBERS:
Silver Members and up get access to an EXTENDED CUT of this video which covers additional examples. That video covers tracking changes to just sensitive fields, such as "Customer ID 4 credit limit changed from $5000 to $9000." We'll also build a simple user combo box on the Main Menu so you can track WHICH user made changes to records.
MEMBERS ONLY VIDEO:
• Tracking Changes to Da...
BECOME A MEMBER:
/ @599cd
MEMBERS TOPICS:
OldValue Property: 599cd.com/oldv...
Double-Double Quotes: 599cd.com/XCQKJ
ADDITIONAL RESOURCES:
FREE Customer Template Database: 599cd.com/XCDT
FREE Access Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/Tech...
Learning Connection 50% OFF: 599cd.com/Conn...
Survey: 599cd.com/survey
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/Acce...
Twitter: / learningaccess
Email Me: [e-mail]
en.wikipedia.o...
products.offic...
microsoft.com/...
ADDITIONAL KEYWORDS:
log user activity
track changes in field data
audit trail
recording edits
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@stephenheusz3236]

Glad that you left the error in. I found that and the reason for the error, the most enlightening aspect of the video.

[@599CD]

Yeah, some people complain, but MOST people prefer when I leave my goofs in the videos. If I mess up, you'll likely mess up too. :)

[@kiwi-ross]

Thank you! I didn't know it would be this easy, and I've always wanted to know how to track changes!

[@599CD]

Glad it was helpful!

[@minarendra]

Thank you for this Tutorial, Never Thought its that easy to track records. Also Thanks a lot for Explaining Logic to put code on before update event.

[@599CD]

You're welcome!

[@Jojosmith342]

Thank you Richard for the honest mistake (on purpose) to save our time. GREAT TEACHER AS ALL TIME !

[@599CD]

Glad it was helpful!

[@jupiter2479]

You can still do that with a macro and one addition query. Set all *fields and =form.key for key in the table. Works without vba. But the downside is a lot of query. You can hide them though.

[@599CD]

Indeed

[@jamesrequilme]

Hi Richard, may i know why I am getting this prompt? "You are about to append 1 row(s). Once you click Yes, you can't use the Undo command to reverse the changes. Are you sure you want to append the selected rows?" I prefer not to have this prompt. How to disable it?

[@599CD]

Turn off warnings for action queries, either in your Access settings or with Docmd.Setwarnings in your code. I've got a video on this coming.

[@chadtrainer123]

Just the answer sought. Excellent and simple!!!! Thx huge help.

[@599CD]

Welcome

[@kamleshjain6872]

Amazing. Simply amazing. Small step but explained so beautifully. 🙏✔️💐

[@599CD]

Thanks a lot 😊

[@kamleshjain6872]

@@599CD hi... I tried above method, but i get this error "Run time error 3825" ..... Multi valued field.Can you help please ? Thanks in advance.

[@yveschoppy2010]

I have implemented this. But when I share the database in sharepoint thus code gives an error. How can that be solved?

[@HeyTezza]

This works awesome, I made mine based on a query that also pulled in the windows username who made the update, thanks heaps, was trying before update but it never worked properly, thanks heaps!

[@599CD]

You're welcome

[@dagsgaz]

Ooooo nice, How did you add that one?

[@patrickvancooten7186]

Thank you for your tutorials, it has been a great help. Do you have a tutorial on tracking or recording equipment maintenance dates, where you can look up the history of specific units? I have an annual preventative maintenance done on my equipment and would like to learn how to design a lookup table for each unit. Thank you

[@599CD]

You could very easily add that to: 599cd.com/Asset

[@prateeksharma5662]

@@599CD waht if i want to create a history record only when the record status column is changed in the main table how can I achieve that?

[@prateeksharma5662]

i got the solution to my problem ... I will leave the code here if anyone wants the help Private Sub Form_beforeUpdate(Cancel As Integer) Dim ststatus As String Dim ststatus1 As String ststatus = Me.Case_Status.OldValue ststatus1 = Me.Case_Status If ststatus ststatus1 Then DoCmd.RunSQL "Insert Into case_history select * from cases " & _ "Where case_id=" & Case_ID End If End Sub

[@Ariel-xe4oi]

Hello - I keep trying this and get "Run-Time error '3825': SELECT* cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field". What does this mean?

[@599CD]

HI Ariel. I answered you via email, but I'll post it here for everyone else's benefit: You've got a multi-valued field in one of your tables... where you can pick an option from a list of options. Sorry, but there isn't any way around it that I know of. This is why in my Beginner classes from day one I tell people NEVER to use those. Multi-value fields are evil.

[@synergybydesign6783]

thanks for keeping it simple!

[@599CD]

You bet!

[@johnbuchman632]

Hello. Excellent video! I believe I have adapted the code properly for my tables and fields, but when I try and commit a change a box pops up and tells me Run-time error '3134' with Syntax error in INSERT INTO statement. Any ideas?

[@599CD]

Let's see your full SQL statement.

[@timothyweed1333]

@@599CD I'm having this issue as well in my DB DoCmd.RunSQL "INSERT INTO TrackChanges-RFECases SELECT * FROM RFECases " & _ "WHERE ID=" & ID Table that holds the Data is RFECases and the the table I'm trying to copy changes into is TrackChanges-RFECases Customer ID is just ID

[@Jojosmith342]

Hi Richard, hope you're well. My form has 2 subforms. Can we track changes of the 3 forms including the main form that contains 2 subforms? thanks a lot

[@599CD]

Sure

[@mikewashington4188]

I love it. Then I would hide the ChangeLog table so ordinary users cannot see it; for additional security measures with only the owner having access to the table. Use it as a recovery mechanism.

[@599CD]

Good plan! In fact, you could run an event on your admin copy of the database to copy down the "shared" version of the log table to your backup copy. This way the users have no chance to get to the older history.

[@mikewashington4188]

@@599CD Great idea. Thanks

[@apolloFear]

Couldn't get this method to work at all. Was attempting to use it on an existing db that had primary field id name with spaces, that used strings. After numerous attempts to pass the field with spaces (Breaker ID), and hours of googling with no working solution I gave up. How do I pass a non numeric ID to the WHERE clause, when the field name in question has spaces???

[@apolloFear]

Solution: Dim MyStr As String MyStr = " ' " & Forms![myForm]![Field I want] & " ' " Wasn't aware the where = expression expects a string in single quotes.

[@599CD]

Yes indeed. Better to use double-double quotes.

[@599CD]

Double Double Quotes: 599cd.com/DoubleDouble

[@MrAimalsultani]

Thank you! and Well-Done Dir, Sir

[@599CD]

You're welcome

[@rajshekharkategar]

The problem is, records are saved in table only when form is closed. If u click on save button and check in table, the changes won't reflect. U have to close the form after clicking on save button to see the changes in table.

[@599CD]

I get a ton of questions every day, and I don't have time to answer them all here on KZbin. Feel free to submit your question on my website at: 599cd.com/AskYT

[@reyanthonysaguid1154]

Thank you for this tutorial. Can I use this on a continuous subform?

[@599CD]

Should work. Try it. :)

[@vladimirlaurent3404]

Thanks for the video Richard. In some programs like quickbooks, there is a checkbox to activate the modification traceability function. How with a checkbox can we do this?

[@599CD]

That's more than a simple answer. 599cd.com/Ask

[@TheBrittnyda]

Aviation industry here. I need to create a database that tracks our jobs and how they're billed because none of them are billed the same way. We've got about 500 jobs (trips) per year. When an aircraft owner flys his plane, easy breezy. But, the planes are leased & I need some sort of tracker that the accounting department can review and edit. The way we code bills is different for each client & it's all based on the "negotiated" sale/lease of the plane. I need to go into a database, type in the trip number and boom! It tells the entire accounting department how to bill, how to code, etc...need dates, trip numbers, entity owners, passengers, FAA trip, then I don't know what to do from there???

[@599CD]

I've never built anything for the aviation industry like this before. Email me what you need and maybe (BIG maybe) I'll put something together. [e-mail]

[@LHPottery]

I don't know why, but that SQL will not work for me. I have checked it over and over and can find no errors, I have retyped it to no avail, I even declared a string variable for it and called it in the RunQuery command. Nothing worked.

[@599CD]

Interesting. I'd need to see it to give you more help. Post in the Forum: 599cd.com/AF

[@zahinkohestani9926]

Hi, first of all thank you for your time and all the lessons. I am a beginner, with no knowledge of Access of SQL. I was trying to set up a data base for a company I am working for. While trying to create a backup table for the data, while doing all that you have done, I get the message (syntax error (missing operator) in query expression). I wondered if you or anyone else here could help me with that.

[@599CD]

Feel free to submit your questions at 599cd.com/TH. Members get priority.

[@adamnudelman7752]

Thank you for all the tutorials. This was exactly what I was looking for. I am running into one issue however. I have added a save button for the form and when it is pressed I am getting a warning "You are about to append 1 row(s). Once you click Yes, you can't... Are you sure you want to append the selected rows?" Once I click "Yes" the row is appended to the tracking table but is there a way to not have this warning pop up every time a change is made?

[@599CD]

Suppress Warnings: 599cd.com/SuppressWarnings

[@petervansoestdevrolijkeboe3320]

I probably must oversee a typo or something but I keep getting this message: "error 3134 syntax error in insert into statement" this is the code: Private Sub Form_BeforeUpdate(Cancel As Integer) DoCmd.RunSQL "INSERT INTO zLogKlantenT * FROM KlantenT " & _ "WHERE KlantID=" & KlantID End Sub Where is the error?

[@599CD]

You're missing the word SELECT

[@robloughrey]

If you did have a drop down on the main menu for a user name how would you add that to your SQL command?

[@599CD]

599cd.com/ask

[@omedahmed3608]

What is the reason when I do these steps, but when the change comes to the screen and asks to save, because the video does not come and works, how does the warning disappear

[@599CD]

I don't understand

[@louisianaman1981]

Whenever I do this, I get a pop-up box that asks for a parameter. If I enter the customerID is saves it correctly

[@599CD]

599cd.com/EnterParameterValue

[@chanti8611]

Hi, I have followed the instructions but I am getting syntax error while implementing. Could you please help me

[@599CD]

Without seeing what you've got, it's impossible for me to help you.

[@CwameJerchie]

Very nice but one problem I'm currently facing is I ve done exactly as you did in your video..but when I try to add a new data, a pop msg always comes " appending row 0 ..." to the table..it adds the data though but how do i prevent the pop up msg or solve that problem

[@599CD]

599cd.com/SetWarnings

[@RamachandranS20]

Great videos!

[@599CD]

Thanks

[@dougroad]

Thanks

[@599CD]

You're very welcome.

[@macsmarathi1611]

Sir, how to track Data Entry by user name? pls. help.

[@599CD]

User Level Security 1: 599cd.com/UserLevelSecurity

[@SceneoftheCrymes]

Hmm, gives me "Run-Time error '3075': Syntax error (missing operator) in query expression 'WarriorID='. My code is DoCmd.RunSQL "INSERT INTO WarriorsChangeLogT SELECT * FROM WarriorsT " & _ "WHERE WarriorID=" & WarriorID I have triple-checked everything I can think of, spelling, primary key set correctly on parent form, key turned off and Indexed set to 'No' and Data Type set to Number on ChangeLog Table, ... Anyone have an idea what I've done wrong?

[@599CD]

Nothing's jumping out of me unless you're missing a warrior ID

[@thechristianrestorationass1065]

Any ideas for how to keep only the last dozen audit records for each customer?

[@599CD]

That's a tricky one. Can't think of a non-programming way to do it. You'd have to count the records first, subtract 12, then delete that many, sorted oldest to newest. Feel free to submit this at 599cd.com/TH and I'll add it to the list.

[@danieljustino2770]

Hello is there a way to use multivalued fields with this functionality? I need the function of tracking changes but I have a Multi field that I would like to keep.

[@599CD]

Nope. Multi-valued fields are evil and should NEVER be used.

[@leb19leb]

Thank you man Just a question, so i can put that vba code on after and before update, right?

[@599CD]

Before allows you to cancel the event.

[@NancyTroutman]

How do you turn off the menu on your system where you have to select the code builder? Love your video.

[@599CD]

File > Options > Object Designers > Always Use Event Procedures I get asked this at least once a week. I cover it in my Access Developer 1 class. I need to make a QuickTip video for it. :)

[@allybianco4547]

Hi! Can you please help me with a question I have in MS Access? I’m trying transfer a simple text box within a form to a drop down list box where you can select multiple different items or type in your own item if it’s not listed. I’ve been struggling to figure this out...any insight would be greatly appreciated!

[@599CD]

That's a lot, and I'm not quite getting what you mean, but as soon as you say "select multiple different items" then you're talking about a multi-select listbox which you need VBA programming for. I cover that in my Access Developer 15 class: 599cd.com/1188 - beyond that you could use a subform, but again, there's a lot to it.

[@kevinkhoo25]

Thank you for this video ! I followed the instructions closely, but encountered an issue where the ChangeLog records only the date/time of change but not the rest of the data. Also with each change, the ChangeLog updates the date/time of the last change but doesn't add/append a new entry. Would be grateful for any suggestions, Thanks there.

[@599CD]

I'm not following. Are you saying it's changing the previous record? It should behave exactly as shown in the video.

[@hollylandrum5213]

I'm having this exact same problem with one of my tables. When changing an entry on the table, the ChangeLog table just refreshing the Now() field. Nothing else is saved to the table. And whenever I refresh the original table again, it overwrites the now() field but doesn't add a new entry with any data.

[@tfusel]

@@hollylandrum5213 Same issue I too am having.

[@briandoucette4053]

It worked but I get the append pop-up? is there way to shut off warnings in the form? thanks

[@599CD]

Yep: 599cd.com/SetWarnings

[@mikewashington4188]

Turn off warning in the options

[@creativecurbappealllc416]

Hey, I am wondering if I got my Tablet names like "Client Call" and "Client Work Order". If it the VBA Code still works or do I have to rename all my Tablets?

[@599CD]

You mean "tables" not "tablets" right? You CAN have spaces in your field names. Access will work just fine. However when you do eventually get to VBA programming, you can't just say: X = Forms!CustomerF!FirstName You have to remember to enclose everything with spaces in brackets: X = Forms![Customer Form]![First Name] It's just easier to NOT use spaces up front.

[@hollylandrum5213]

Unfortunately I'm getting a syntax error. Do you have any ideas? My code: Private Sub Form_BeforeUpdate(Cancel As Integer) DoCmd.RunSQL "INSERT INTO ChargeProductsChangeLog SELECT * FROM ChargeProducts " & _ "WHERE ProductName=" & ProductName End Sub The error: Run-time error '3075': Syntax error (missing operator) in query expression 'ProductName='.

[@hollylandrum5213]

These tables are saved in a back end database. Do I need to put additional language in the VBA code to save to the backend database tables? That's my best guess for the error

[@599CD]

Product Name needs quotes around it if it's text. See 599cd.com/Concat

[@599CD]

And this 599cd.com/DoubleDouble

[@599CD]

So you'll end up with something like: "WHERE ProductName=""" & ProductName & """"

[@599CD]

However using the product NAME as a criteria like you're doing is usually a bad idea. I'd use the ID.

[@ShahidLuqman]

Thanks a lot sir, It's a great single line code but my where condition in this format = "J-00000526-000391" which gives me error msg "Enter Parameter value". How I can solve it?

[@599CD]

599cd.com/EnterParameterValue

[@ariadnalopezcollado2718]

Hello Greetings from Spain. I have a problem, I am trying to make in a form a box in which appears the "columnhistory()" of my comments. I already have the correct format, it is as indexed, and when writing the formula it seems that everything is fine, but when executing I get the error "#Name?" There is no way to fix it and all the relationships are fine, I'm desperate..... can you help me? Thanks and congratulations for your video

[@599CD]

Sounds to me like something is spelled wrong. See 599cd.com/Trouble and 599cd.com/ErrorMessages

[@user-uc9du8ev6m]

But for me the same says Runtime error 3464. Any solution or reason ??

[@599CD]

599cd.com/Trouble

[@nadaalshaikhi8422]

thanks a lot this helped me so much with my table, however i tried the same code with another table that has 3 comboboxes and it says can't use it as it sees these comboboxes as multivalued although i didn't make them as multi, can you help with this please?

[@599CD]

Don't use multi-valued fields. They're evil.

[@nadaalshaikhi8422]

@@599CD thank you for your answer, i really appreciate it, is there any other way to show the user a list requires him to choose from ? i created a table and had the primary key set in the main table as a number, it keeps seeing it as a multivalue field,

[@MoneyMiner9]

I see you’re very active with replying to comments, thus I wanted to ask mine. I’m trying to track basically the same thing, but when changes are made to a table, not a form. The data of the table is connected to a form, but when I added the item in, it only works when form changes are made within the form, not changes made directly to the table. I ask because I have append queries and sometimes easier to change data with the table than looking through thousands of forms.

[@599CD]

Nope. You can't put events in a table. Use a form.

[@codymccurley2327]

Hi I have a simple question about using the VBA Coding. So I followed all the steps and I entered the code but made it my own using different table names and what I am trying to do is track the changes made to each of my clients info for when they change addresses and such but the code keeps throwing the code 3134 which is a missing or misspelled word. I have double checked every word to ensure accuracy and they are correct. It will change in the form but will not insert that data into the new table where I want it to go. Any suggestions? This is what I have: DoCmd.RunSQL "INSERT INTO ConsumerInfoChangeT SELECT * FROM Consumer Info" & _ "WHERE ConsumerID=" & ConsumerID Thank you, Cody McCurley

[@599CD]

You see folks... This is why I tell you never to put spaces in your field names or table names. Consumer info has a space in it and that's what's causing your problem. You need to remember to put brackets around it. Or better yet go back and get rid of that space.

[@codymccurley2327]

@@599CD Thank you and I did put it in brackets and it did work. I have used the same formula for a copy different forms for my boss. Thank you again and please continue to make videos as I will become a member. Thank you, Cody McCurley

[@gregory35665]

please make video multiple table/form audit

[@599CD]

Please clarify what you're looking for. I don't understand. Best yet, if you want me to make a video, submit it via the TechHelp page: 599cd.com/TechHelp - that's the best way to get on the list.

[@gregory35665]

@@599CDthanks. I am looking for ways to audit an Parent/child or master /Details kind of detail . master record can have one or more child records. want to aduti both master form and child form and show the audit in a single view

[@599CD]

The technique I showed will work for any table, whether it's a parent, child, subform, whatever. Doesn't matter.

[@geomughal]

Blur Video . I can not understand anything.

[@599CD]

Are you talking about the quality of the video, which seems fine to me, or the material presented? This is a more advanced topic. If you are lost, start from the basics: 599cd.com/ACB1

[@geomughal]

Thank you for reply Thankyou for given me course link