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

Поделиться
HTML-код
  • Опубликовано: 1 авг 2024
  • 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/security
    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/oldvalue
    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/TechHelp
    Learning Connection 50% OFF: 599cd.com/Connection
    Survey: 599cd.com/survey
    Richard's Blog: 599cd.com/blog
    Access Forum: 599cd.com/AccessForum
    Twitter: / learningaccess
    Email Me: amicron@gmail.com
    en.wikipedia.org/wiki/Microso...
    products.office.com/en-us/access
    microsoft.com/en-us/microsoft...
    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.

Комментарии • 124

  • @stephenheusz3236
    @stephenheusz3236 2 года назад

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

    • @599CD
      @599CD  2 года назад +2

      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
    @kiwi-ross 3 года назад +3

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

    • @599CD
      @599CD  3 года назад

      Glad it was helpful!

  • @Jojosmith342
    @Jojosmith342 Год назад

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

    • @599CD
      @599CD  Год назад +1

      Glad it was helpful!

  • @chadtrainer123
    @chadtrainer123 2 года назад

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

    • @599CD
      @599CD  2 года назад

      Welcome

  • @minarendra
    @minarendra 3 года назад +2

    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
      @599CD  3 года назад

      You're welcome!

  • @kamleshjain6872
    @kamleshjain6872 3 года назад

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

    • @599CD
      @599CD  3 года назад +1

      Thanks a lot 😊

    • @kamleshjain6872
      @kamleshjain6872 3 года назад

      @@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.

  • @synergybydesign6783
    @synergybydesign6783 2 года назад

    thanks for keeping it simple!

    • @599CD
      @599CD  2 года назад

      You bet!

  • @MrAimalsultani
    @MrAimalsultani 3 года назад

    Thank you! and Well-Done Dir, Sir

    • @599CD
      @599CD  3 года назад

      You're welcome

  • @RamachandranS20
    @RamachandranS20 2 года назад

    Great videos!

    • @599CD
      @599CD  2 года назад

      Thanks

  • @terri78
    @terri78 6 месяцев назад

    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
      @599CD  6 месяцев назад

      You're welcome

    • @dagsgaz
      @dagsgaz Месяц назад

      Ooooo nice,
      How did you add that one?

  • @vladimirlaurent3404
    @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
      @599CD  Год назад

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

  • @dougroad
    @dougroad 2 года назад

    Thanks

    • @599CD
      @599CD  2 года назад

      You're very welcome.

  • @reyanthonysaguid1154
    @reyanthonysaguid1154 2 года назад

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

    • @599CD
      @599CD  2 года назад

      Should work. Try it. :)

  • @patrickvancooten7186
    @patrickvancooten7186 2 года назад +1

    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
      @599CD  2 года назад

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

    • @prateeksharma5662
      @prateeksharma5662 2 года назад

      @@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
      @prateeksharma5662 2 года назад

      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

  • @jupiter2479
    @jupiter2479 5 месяцев назад

    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
      @599CD  5 месяцев назад

      Indeed

  • @zahinkohestani9926
    @zahinkohestani9926 3 года назад

    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
      @599CD  3 года назад

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

  • @NancyTroutman
    @NancyTroutman 3 года назад

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

    • @599CD
      @599CD  3 года назад +3

      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. :)

  • @adamnudelman7752
    @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
      @599CD  Год назад

      Suppress Warnings: 599cd.com/SuppressWarnings

  • @leb19leb
    @leb19leb 3 года назад

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

    • @599CD
      @599CD  3 года назад

      Before allows you to cancel the event.

  • @jamesrequilme
    @jamesrequilme 3 года назад +3

    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
      @599CD  3 года назад +3

      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.

  • @danieljustino2770
    @danieljustino2770 3 года назад

    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
      @599CD  3 года назад +1

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

  • @Ariel-xe4oi
    @Ariel-xe4oi 3 года назад +1

    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
      @599CD  3 года назад +1

      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.

  • @Jojosmith342
    @Jojosmith342 9 месяцев назад

    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
      @599CD  9 месяцев назад +1

      Sure

  • @thechristianrestorationass1065
    @thechristianrestorationass1065 3 года назад

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

    • @599CD
      @599CD  3 года назад

      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.

  • @allybianco4547
    @allybianco4547 3 года назад

    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
      @599CD  3 года назад

      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.

  • @creativecurbappealllc416
    @creativecurbappealllc416 3 года назад

    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
      @599CD  3 года назад

      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.

  • @yveschoppy2010
    @yveschoppy2010 5 месяцев назад

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

  • @kevinkhoo25
    @kevinkhoo25 2 года назад

    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
      @599CD  2 года назад

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

    • @hollylandrum5213
      @hollylandrum5213 2 года назад

      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
      @tfusel 2 года назад

      @@hollylandrum5213 Same issue I too am having.

  • @johnbuchman632
    @johnbuchman632 3 года назад +2

    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
      @599CD  3 года назад

      Let's see your full SQL statement.

    • @timothyweed1333
      @timothyweed1333 2 года назад

      @@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

  • @CwameJerchie
    @CwameJerchie 2 года назад

    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  2 года назад +1

      599cd.com/SetWarnings

  • @mikewashington4188
    @mikewashington4188 2 года назад

    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
      @599CD  2 года назад

      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
      @mikewashington4188 2 года назад

      @@599CD Great idea. Thanks

  • @nadaalshaikhi8422
    @nadaalshaikhi8422 3 года назад

    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
      @599CD  3 года назад

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

    • @nadaalshaikhi8422
      @nadaalshaikhi8422 3 года назад +1

      @@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,

  • @robloughrey
    @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  Год назад

      599cd.com/ask

  • @ShahidLuqman
    @ShahidLuqman 3 года назад

    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  3 года назад

      599cd.com/EnterParameterValue

  • @louisianaman1981
    @louisianaman1981 2 года назад

    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  2 года назад

      599cd.com/EnterParameterValue

  • @chanti8611
    @chanti8611 2 года назад

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

    • @599CD
      @599CD  2 года назад

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

  • @briandoucette4053
    @briandoucette4053 3 года назад

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

    • @599CD
      @599CD  3 года назад

      Yep: 599cd.com/SetWarnings

    • @mikewashington4188
      @mikewashington4188 2 года назад

      Turn off warning in the options

  • @MoneyMiner9
    @MoneyMiner9 2 года назад

    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
      @599CD  2 года назад

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

  • @rajshekharkategar
    @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
      @599CD  Год назад

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

  • @ariadnalopezcollado2718
    @ariadnalopezcollado2718 2 года назад

    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
      @599CD  2 года назад

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

  • @petervansoestdevrolijkeboe3320
    @petervansoestdevrolijkeboe3320 2 года назад

    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
      @599CD  2 года назад

      You're missing the word SELECT

  • @omedahmed3608
    @omedahmed3608 2 года назад

    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
      @599CD  2 года назад

      I don't understand

  • @TheBrittnyda
    @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
      @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. amicron@gmail.com

  • @LHPottery
    @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
      @599CD  Год назад

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

  • @user-uc9du8ev6m
    @user-uc9du8ev6m 2 года назад

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

    • @599CD
      @599CD  2 года назад

      599cd.com/Trouble

  • @macsmarathi1611
    @macsmarathi1611 Год назад

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

    • @599CD
      @599CD  Год назад

      User Level Security 1: 599cd.com/UserLevelSecurity

  • @apolloFear
    @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
      @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
      @599CD  Год назад +1

      Yes indeed. Better to use double-double quotes.

    • @599CD
      @599CD  Год назад +1

      Double Double Quotes: 599cd.com/DoubleDouble

  • @hollylandrum5213
    @hollylandrum5213 2 года назад

    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
      @hollylandrum5213 2 года назад

      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
      @599CD  2 года назад

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

    • @599CD
      @599CD  2 года назад

      And this 599cd.com/DoubleDouble

    • @599CD
      @599CD  2 года назад

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

    • @599CD
      @599CD  2 года назад

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

  • @gregory35665
    @gregory35665 3 года назад

    please make video multiple table/form audit

    • @599CD
      @599CD  3 года назад

      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
      @gregory35665 3 года назад

      @@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
      @599CD  3 года назад

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

  • @geomughal
    @geomughal 2 года назад

    Blur Video . I can not understand anything.

    • @599CD
      @599CD  2 года назад

      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
      @geomughal 2 года назад

      Thank you for reply
      Thankyou for given me course link

  • @SceneoftheCrymes
    @SceneoftheCrymes 7 месяцев назад

    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
      @599CD  7 месяцев назад

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

  • @codymccurley2327
    @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
      @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
      @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