Timestamp
By Richard Rost
4 years ago
Tracking When Record Was Last Updated in Access
In this Microsoft Access Tutorial, I will show you how to track when a particular record was last updated using one line of VBA code.
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Store the date and time when a record is modified, Form BeforeUpdate, How do you timestamp in Access, Detect time of last change on a Microsoft Access database, Time Stamping Records in Access, microsoft access audit trail, ms access history log, ms access timestamp, access log activity, track changes in access, after update, before update, afterupdate
Subscribe to Timestamp
Get notifications when this page is updated
Intro In this video, I will show you how to add a timestamp in Microsoft Access to track when a record was last updated. We will cover where to create and place the necessary LastUpdated field, how to use a simple line of VBA code to automatically update this field whenever changes are made, and which event in the form you should use for the best results. I will also demonstrate how to make this field read-only to prevent users from editing it directly.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I am going to show you how to add a timestamp tracking when a record was last updated in Microsoft Access. This is one I get asked all the time: how do I track when a record was last updated, whether it is a customer record, a product record, an order, or when was the last time that anything in this record was updated? That could be important to know. For products, for example, we have not gotten updated pricing from our vendor on this product in two months.
So in this lesson, I am going to show you how to do it.
Before we get started, you are going to need one line of VBA code. One line, just one. I am going to show you what it is. But if you want to learn how to program in VBA, go watch this. It is a free video. It is 20 minutes long. It will teach you everything you need to know to get started programming in VBA. One line of code is easy. Knowing where to put it and what it is all about – well, that is the harder part. That is what I cover in this video. It is free. It is on my YouTube channel. It is on my website. There is a link. You will find a link you can click on down below the video.
While you are at it, go watch this video too if you have time to buy the after update event. It is about how to have some bit of code run after another field is changed. Go watch this too if you have time. If not, no big deal.
Here I am in my TechHelp free template. This is a free database. You can download a copy on my website if you want to. Again, you will find links down below. Here, I have got a customer form and I want to know when any field in here is changed. The first thing I need is a field to save that data in. We need to know when this record was last updated.
Let us go to the customer table, design view. If you do not know how to do this – customer design, all that – go watch my Access Beginner 1 class. It is free, it is 4 hours long, and it is on my website and my YouTube channel as well. You will find links to that down below too.
We need LastUpdated, and we will make that a date/time field. You can put a default value down here: =Now. That will put the current date and time in there when the record is created, and only when the record is created. That is not enough, but it will get us started.
Save that. Close it. Now if we go back in here, we have to add this to our form as well. Go to my form design tab. Go to Add Existing Fields. There is LastUpdated, now that we have added it to the table. Click and drag. We will just drop it down here on the bottom. I will make that bigger, like so, and I am going to put a little space in there. Maybe we will format paint this too. Let us go click on this guy, go to the Format Painter, paint like that.
I want to see a specific format of the date in here. I am going to go to Format and let us pick General Date. That way I get the date and time. Save it. Close it. Open it back up again.
Notice there is nothing in there because these are existing records. If I go to a blank new record, you can see there it is right there. That is the default value in a new record.
What I need, though, is any time I change any one of these fields, I want that date updated to be updated. If you watched my after update video, then you will know that you can put an after update event in pretty much any one of these fields in here.
For example, let us say FirstName. Double click on that. Go to Events. Go to After Update. This happens after the data is changed and gets committed to the table. If I hit ... that brings up my VBA editor. I come in here and say LastUpdated = Now just like that. That is all you need. LastUpdated = Now. That is in the FirstName after update event.
If I come back here, let us close this. If I come in here and change this from Richard to Rick and hit Tab, boom, look at that. That triggered the event after this field was updated.
I do not want to have to put that in every single field in here – that same event. It would be nice if I could do that on the form level, and guess what, there is. Let us go in here and let us get rid of the after update for this field. Get rid of that, delete.
Let us come back over here and let us take a look at the form properties. There is an After Update for the form right here. This happens when the entire form is updated – any field on the form gets updated. Let us try this one. Let us see if this works. ... Now we are in the form After Update. Again, let us put in here LastUpdated = Now.
Save it. I am back out here. Let us close it and reopen. I always have to close and reopen forms when I am working with code. Let us go to a different record and I will try changing James to Jim and hit Tab. Did not seem to work. That is because the after update event does not fire until the record gets saved to the table. That is how an after update event works.
Let us move to a different record. The update worked but it did not move me to another record. What is going on here? Hold on. Let us try to go to the previous record. That is not working either. What is the problem?
Here is the problem. If you put that in the after update event, Access tries to save that record to the table. But in doing so, edits the record and that causes some kind of a weird loop where it cannot save it because it is trying to update it but it cannot update it because it is trying to save it. So, after update is the wrong event to use for this. I have to escape to break out of that loop.
Let us go back into our design view. Let us go back to our code. Get rid of that.
The right event to use for this is Before Update. The before update fires when you make changes to the form, to the record, but before it is saved to the table. You can edit that field and then that whole thing gets saved at once.
So, go to ... here and this is where we can put LastUpdated = Now. Before update also has a benefit that you can cancel the event. You can check for things in here, like that they put in the right kind of data, use it valid, you can check it against other tables, and cancel the event and give them an error message. I covered that in my other lessons.
Save that. Let us come back out here.
Now, the one downside to doing this is that you will not see the data be updated here as you are typing it. So, if I go to a different record – for example, let us say Diana gets married and she is now Diana Riker – now, it does not look like it is working but watch this as I leave the record. The event actually runs, saves it, and it is stored in there. That is the one downside to this method: the person editing the record will not see that until they close the form or move to a different record and come back to it. But that is not that big of a deal. I kind of like it.
In fact, you can even hide this field if you want to and just have it like in a manager form or whatever. What I like to do, honestly, is I like to disable this field. I like to gray it out. Open this guy up because you do not want people tampering with that number. Go to Data and then turn Enabled to No or you can lock it and gray it out, whichever one you want, both do the same thing.
But now if you come in here, go to Customer Form, it is grayed out. You cannot click on it. You cannot change it. If I go to someone else like Will Riker, I put a phone number in here, as soon as I leave the record and come back to it, that data gets updated and now you know when the record was last updated.
Pretty cool, nice and simple.
If you want to learn more, you will find some links down below. Here are some other related videos. This one I just did recently. It is on graying out fields. That is that Enabled property. Very similar to what we just did.
If you want to know who made that update instead of just when it was updated, you want to know who did it, well, I have another video tracking user log on and log off. You can track in a table who is logged on and you can actually save that username in the table as well. So you could say LastUpdated and LastUpdatedBy and put the person's username in there.
Getting even more complicated, I have a track changes in data where you can create an actual log table where you can store the time and date, who did it, and what was changed. This is a little more complicated, so you could track all the changes, creating a log or an audit trail table.
And of course, if you want full security for your Access database, including user log runs, tracking that workflow, with an audit table, a system log, controlling who has access to do what in the database, I have a full security seminar where I show you how to properly lock down your Microsoft Access databases and keep them fully secure.
All right, you will find links to all this stuff down below. You have to click on More if you are watching on YouTube. You have to click on the More button. If you are watching on YouTube, they do a really good job of hiding it. It is right there. You click on that More button and then you will see all these extra links and stuff down below down here. People always ask where are the links. You have to click, you have to find that More button. YouTube, you have to make that a little more visible for people.
So there is your fast tip for today. I hope you learned something, and we will see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
All of our members will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You will also get a higher priority if you decide to submit any TechHelp questions to me, and you will get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a Sponsors page that will be shown in each video as long as you are a sponsor. You will get a shout out in the video and a link to your website or product in the text below the video and on my website.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. What is the main goal of the video tutorial?
A. To track when a record was last updated in Microsoft Access
B. To teach how to create new tables in Access
C. To show how to create user login screens
D. To build a product inventory database
Q2. Which field type is recommended for the LastUpdated column in the customer table?
A. Text
B. Currency
C. Date/Time
D. Number
Q3. What does setting the default value of the LastUpdated field to =Now accomplish?
A. It updates the field every time any change is made to the record
B. It populates the field with the current date and time only when the record is created
C. It keeps the field empty until changed manually
D. It allows users to input the update time themselves
Q4. What VBA code do you use to update the LastUpdated field when a record is modified?
A. LastUpdated = DateValue
B. LastUpdated = UpdateTime()
C. LastUpdated = Now
D. LastUpdated = CurrentDate()
Q5. Why is the Form's After Update event NOT recommended for automatically updating the LastUpdated field?
A. It updates before any record changes are made
B. It can cause an unsolvable loop that prevents saving the record
C. It does not support VBA
D. It fires every time the form is opened, regardless of changes
Q6. Which event is recommended to update the LastUpdated field just before the record is saved?
A. On Load
B. After Update
C. Before Update
D. On Open
Q7. What is a downside of using the Before Update event to update the LastUpdated field?
A. It only updates when a new record is created
B. Users will not see the field update until they move to another record or close and reopen the form
C. It causes the form to close unexpectedly
D. It erases all previous update history
Q8. What can you do to prevent users from modifying the LastUpdated field directly?
A. Hide the field from the form
B. Set the Enabled property to No or lock the field
C. Make it a text field
D. Remove it from the table
Q9. If you want to track which user made the update as well as when, what should you add?
A. Add LastUpdatedBy field to store the username
B. Require a password every time a change is made
C. Use a macro instead of VBA
D. Only update the LastUpdated field
Q10. For tracking detailed changes (who, what, and when), what advanced method was suggested?
A. Using a default value for every field
B. Creating a log or audit trail table to track all changes
C. Limiting edits to admin users only
D. Sending email notifications on every update
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-A; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers how to add a timestamp to your Microsoft Access database in order to track when a record was last updated. This is a question I receive quite frequently. People often want to know how to keep tabs on the most recent changes to a customer, product, or order record. For example, it can be essential to see if a product's information has not been updated in a couple of months so you know when to follow up for new data.
To make this work, you only need one line of VBA code. Even if you are not already familiar with VBA, this is quite straightforward, but it is important to know where to put that code. For a thorough introduction to VBA, I recommend watching my free 20-minute starter video on my website or YouTube channel, which will help anyone get up to speed with VBA basics. I would also suggest checking out my video on the after update event, which explains how to trigger code after someone changes a field's value.
In the demonstration, I use my free TechHelp template database, which is available to download from my website. Suppose you want to track the last time any field in your customer form was changed. The first thing you need is a field in your table to store this data. For this, open your customer table in design view and add a new field called LastUpdated, setting its data type to Date/Time. If you want, you can use a default value (such as =Now) so that Access records the creation time of each new record. Keep in mind, though, that this will only be set when the record is initially created.
Once you have added the LastUpdated field to your table, add it to your customer form so you can see it when editing records. Set the field's format to General Date so it displays the full date and time. At first, the field will only be populated for new records, since existing records do not have data in the new field yet.
The key requirement here is to have the LastUpdated field automatically update whenever any field on the form is changed. You might recall from my previous videos that you can use the after update event at the field level, for example on the FirstName field, to set LastUpdated to the current date and time with a single line of code. However, putting this code behind every single field is inefficient.
A better approach is to use an event at the form level. Access provides an after update event for forms, but there is a problem with using this event in this scenario. If you set LastUpdated in the form's after update event, Access creates a looping situation where it is unable to save the edit properly. This happens because setting LastUpdated is itself an edit that retriggers the event.
The correct way to handle this is by using the form's before update event. This event fires after any edits are made, but before the data is saved to the table. Place the code to set LastUpdated in this event. A side benefit of the before update event is that you can also add validation code to check data and cancel the event if there are problems.
There is a small limitation with this method, though: the LastUpdated field does not update immediately after a field is changed. Instead, it updates only after the record is saved, which usually happens when you move to a new record or close the form. So, users might not see the updated timestamp right away, but it will be stored correctly.
If you do not want users to be able to change the LastUpdated field manually, you can disable or lock the field in the form design. This way, the field will appear grayed out, and users will not be able to edit it. This is usually the best practice for audit fields.
If you are interested in related topics, you may want to check out my other videos on graying out fields or on tracking who made changes to a record. In those videos, I show how to store the username of the editor, so you know who made which update, not just when it was made. For more advanced needs, I also have tutorials on creating a full audit log that records what was changed, when, and by whom.
If your goal is to add full security to your Access database, including features like audit tables, user logins, and fine-grained control over who can access what, I also have a comprehensive security seminar available on my website.
All of the links to these resources can be found on my website. Remember, the step-by-step video tutorial for everything discussed here is available at the link below.
Live long and prosper, my friends.Topic List Adding a LastUpdated field to an Access table
Setting default value for date time fields
Adding the LastUpdated field to a form
Formatting a date time field on a form
Using VBA to update LastUpdated on field change
Implementing the After Update event for a form field
Explaining limitations of After Update at the form level
Identifying the save loop issue with After Update event
Using the Before Update event at the form level
Disabling or locking the LastUpdated field on the form
Demonstrating automatic timestamp updating when records are edited |
