AutoSave
By Richard Rost
3 years ago
AutoSave Form Data at Timed Intervals in Access
Word and Excel have an AutoSave. Why not Access? In this Microsoft Access tutorial, I'm going to teach you how to have your form automatically save data at a timed interval. So, if someone walks away from the computer in the middle of editing a record, it will automatically save after, say, 3 minutes.
Declan from San Francisco, California (a Platinum Member) asks: I run a call center and have a problem where my employees will get up and walk away from the computer in the middle of entering a record without saving it. They'll go on a smoke break, or even go out to lunch, or sometimes even go home before saving their work. Of course, if that caller calls back and someone else goes to open the record, they don't see the most up-to-date changes. Is there any way to have the database automatically save changes every minute or two in case this happens?
Members
Members will learn how to stop the Autosave from kicking in while the user is actively editing the record. This way he won't be interrupted while typing and have the form save and reset.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
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, Autosave records in access, refresh interval, auto save, automatically save, keypress, key press, on mouse over, button press events, Key preview, keypreview, how to automatically save records after a timed interval in microsoft access
Subscribe to AutoSave
Get notifications when this page is updated
Intro In this video, I will show you how to add an auto save feature to your forms in Microsoft Access using a timer event and VBA. You will learn how to automatically save records at set time intervals to help prevent data loss if users walk away without saving their work. I will walk through how to detect when a record is being edited and how to trigger an automatic save only when needed, as well as provide a visual indication to users when the auto save occurs.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to teach you how to set up an auto save in your forms so that your data saves at timed intervals in Microsoft Access. Word and Excel, they all have auto save features, right? If you are working on a Word document or an Excel spreadsheet, it saves. Why does Access not have that? That is what we are going to build in today's video.
Today's question comes from Declan in San Francisco, California, one of my Platinum members. Declan says, I run a call center and have a problem where my employees will get up and walk away from the computer in the middle of entering a record without saving it. They'll go on a smoke break or even out to lunch or sometimes even go home before saving their work. And of course, if that caller calls back and someone else goes to open the record, they do not see the most up-to-date changes. Is there any way to have the database automatically save changes every minute or two in case this happens?
Yes, of course, Declan, we can do this using something called a timer event. I also cover timer events in this video on how to make a reminder pop-up so that every few minutes or so, your form will check to see if a certain event happens and it will pop up a message. You can use the same thing to save the record.
You do not always want to save the record. You do not always want this timer event running. You want to check to see if the user is in the middle of editing it first.
So how do we do that? Well, to do that, we are going to check to see if the record is dirty first. Dirty means that the user is in the middle of editing it. You get that little pencil here. So, in the timer event, we are going to check to see if the record is dirty. If so, we are going to save it. I will show you how in just a second.
If you have not watched either of these videos though, the Dirty video or the Timer Event video, go watch those first and then come on back.
This is a developer video, which means we are going to be using a little bit of VBA. If you have not yet watched my Intro to VBA class, go watch this. It is about 20 minutes long and teaches you everything you need to know to get started.
Here I am in my TechHelp free template. This is a free database. You can get a copy off my website if you want to. In here, I have got a customer form. Let's pretend this is the form that our call center people are using on a regular basis. They come in here and they make some changes, and they make another change down here. They type in some notes, whatever. And they leave for the day.
Here the database is sitting. It is dirty. You have got that pencil there. That means that this record, it is here on the form, but the data has not yet been committed to the table. If someone else goes to look up this customer, they will see the old data because this data has not been saved yet.
So what you want is an event that will run every few minutes, not too often. You do not want to do this too often because you might annoy the user. If this runs every second or every five or ten seconds, then they might be in the middle of typing and the record saves. That could be annoying. I would set it to something maybe like five minutes or whatever you feel is appropriate for your business and your use. But for the purposes of class, just for this video, I am going to set it to five seconds so we can see that it is working.
So how do we do that? First, let's go into Design View, and let's open up the form's properties, and find Events. We are going to scroll down here until we find the Timer Interval. This is the number of milliseconds, not seconds, but milliseconds before the event fires off. I am going to put 5000 in here. That is five seconds. If you want two minutes, it would be 60 seconds times two times a thousand or 120,000. You could have a pretty big number in here. But for a real case scenario, I would make this at least a couple of minutes. Every time the event runs, it might steal focus, too.
So what are we going to put in the On Timer event? Well, let's go in here. Dot, dot, dot. That will bring up our Code Builder.
Here I am in the form's Timer event. The first thing I want to do is check to see if the user is actively editing this record, because if they are not editing it, we do not have to do anything. I am going to say if Me.Dirty, then we are going to do some stuff. If it is not dirty, nothing will happen. We do not want to run this event if the record is not dirty, if the record is not being edited.
Now, all you really have to do is set the Dirty property to False and that will save the record. It is not going to refresh things, it will not do any recalculations on the form. You could use a Me.Refresh here if you wanted to. That will refresh any calculations and stuff like that. But I do not want to do that. I am going to do a Me.Dirty = False. All that literally will do is save the record. The pencil will go away and you will see that it turns into a little arrow. I will put a Beep in here too, just so we know that it is working. You do not want the beep in your final version. You could leave it if you wanted to.
So I am going to save it. I am going to close this form and then open it back up again. Now, the record is not dirty. I am going to sit here and wait. Two, three, four, five. Nothing's happening. I am not hearing any beeps. That is good. Let me edit the record. I will just come in here and put in some characters. It is now a pencil. I will wait. And there it goes. See? It saved it. I heard my beep and my little pencil went away.
Now, I want to give the user a visual indication of what is happening. Just set that caption to the form property up there.
Be careful. This happens a lot. I get this email all the time. I just saw this in the form the other day. If this form is open over here, see how I just switched over to my VBA editor? This event is still running. If I come in here and start typing, see what just happened? I did not do anything. The event ran and it refreshed this and now it is saying, Sub or Function not defined, because it is trying to run this. So, keep in mind, I had to stop that. When you stop this, it kills that timer event.
If you get the VBA editor open and a form is running, people do not understand why the text goes red or you get error messages, because this thing is still running in the background. You have to close it.
You can come over here and make changes. We are not worrying about that. That is a common question. I get that all the time.
Back to that caption. Put it before the Me.Dirty, though, because for some reason, setting the caption property also makes the record dirty. So it will look like nothing happened.
So, Me.Caption = "Auto saved " and now, we will put the current date and time in there, so the user knows what is happening. Save it. Come back over here. Close that. Open it up again. I will come back in here and fix this. I am going to walk away. Oh, look at that. It saved my work for me automatically. The record got committed. The user got notified. And everybody is happy.
Now, there is only one thing you really have to watch out for. That is if you have a business where your people are typing in novels into long text fields, if they are typing lots and lots and lots of stuff in. Because if I am in here typing away, typing away, type, oh, see what happened? That event is going to run. They might be in the middle of typing. They are not going to lose anything. The data will be saved, but it is going to be annoying. It is going to be irritating because it is going to save it and might move their cursor back up to the top.
So how do you fix that? Well, that is what we are going to cover in the extended cut for the members. I am going to show you how to disable that auto save if the user is still actively editing the record. They are still typing and they are still clicking on stuff. I will show you how to temporarily disable that auto save. But if they do walk away, then it kicks back in. It involves a little bit more coding.
Extended cuts are for my Silver members and up. You get access to all of my extended cut videos, not just this one - all of them. So it is well worth your membership.
If you like learning with me, if you like all this developer, VBA programming stuff, I have got tons of developer classes on my website. Come check those out too. I will put links down below.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I will see you next time.Quiz Q1. What is the main problem Declan faces in his call center regarding data entry in Access forms?
A. Employees forget to create new records
B. Employees leave records unsaved when they walk away from the computer
C. Employees accidentally delete important records
D. Employees use the wrong form to enter data
Q2. What feature found in Word and Excel is missing in Microsoft Access, which this tutorial helps to replicate?
A. AutoFill
B. AutoFormat
C. AutoSave
D. AutoCorrect
Q3. What mechanism does the tutorial suggest using to periodically save data in an Access form?
A. Data Macro
B. Query Automation
C. Timer Event
D. Form Property Sheet
Q4. Why is it important to check if the record is "dirty" before saving it using the timer event?
A. To ensure the record is locked
B. To only save records that have been modified
C. To prevent data from being deleted
D. To refresh all calculations on the form
Q5. In Access, what does it mean if a record is "dirty"?
A. The record is outdated
B. The record has been deleted
C. The user is in the middle of editing the record
D. The form is closed
Q6. What is the primary line of VBA code used to save a dirty record in the timer event?
A. Me.Refresh
B. Me.Save
C. Me.Dirty = False
D. Me.Caption = "Auto saved"
Q7. Why should you avoid running the timer event too frequently (such as every five or ten seconds)?
A. It could overload the database server
B. It could slow down the computer hardware
C. It might annoy the user and interrupt their typing
D. It will not impact anything negatively
Q8. What property must you set on the form to determine how often the timer event fires?
A. OnLoad
B. Timer Interval
C. Form Width
D. Default View
Q9. In the context of Access forms, what unit of time does the Timer Interval property use?
A. Seconds
B. Minutes
C. Milliseconds
D. Hours
Q10. What is a potential problem if the timer event saves while a user is typing in a long text field?
A. Some data might get deleted
B. The form might close unexpectedly
C. The save could interrupt the user and move their cursor
D. The database could crash
Q11. What visual feedback does the tutorial suggest providing to the user after an auto save occurs?
A. Change the background color of the form
B. Update the form caption to indicate auto save and display the current date and time
C. Show a message box
D. Hide the form
Q12. What should you be careful about when editing VBA code while a form with a timer event is open?
A. The timer event can continue to run and trigger errors in the background
B. The form data will be deleted
C. The database will automatically close
D. New records will be created automatically
Q13. According to the video, how can you improve the auto save feature for users who type long entries?
A. Increase the Timer Interval to 30 seconds
B. Allow the form to save regardless of what the user is doing
C. Temporarily disable auto save if the user is still actively typing or clicking
D. Only save when the user closes the form
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-C; 7-C; 8-B; 9-C; 10-C; 11-B; 12-A; 13-C
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 TechHelp tutorial from Access Learning Zone will show you how to set up an auto save function for your forms in Microsoft Access, allowing your data to be saved automatically at set intervals. While applications like Word and Excel automatically save your documents as you work, Microsoft Access does not offer this feature out of the box. I will walk you through how to build an auto save system for your Access forms.
Recently, I received a question from a Platinum member who manages a call center. He described a common issue: employees sometimes start editing a record, then walk away from their computers without saving. If another employee tries to access that record, they will only see outdated information. The question was whether it is possible to have Access save changes automatically every couple of minutes, so that valuable data is not lost if an employee forgets to save before leaving.
The answer is yes. We can accomplish this using a timer event. Timer events let you run particular actions on your form at repeating intervals, such as displaying a reminder message or, for our needs, saving the current record. However, we need to be careful with how often this runs to avoid disrupting users while they are working.
One important thing to consider is that we do not want to force Access to save a record unless a user is actually editing it. In Access, a form is considered "dirty" when a user has made changes to the data but not yet saved them. You can usually tell the form is dirty if you see a little pencil icon, which means the record exists on the form but the changes have not been saved to the table. To automatically save, our timer event will first check if the record is dirty. If so, we initiate a save operation.
If you are not familiar with how timer events or the "Dirty" property work in Access, I recommend watching my earlier tutorials on those topics before you continue. Also, since we will be using a bit of VBA code in this solution, it is helpful to start with my Intro to VBA lesson if you are new to programming in Access.
Let me describe how this works using the TechHelp free template database. Imagine your call center workers are using a customer form to take notes or update information. Suppose someone steps away from their desk in the middle of editing. The form still displays the changes, but because the record is dirty and not yet saved, the latest edits will not be visible to others working with the same customer data.
To handle this, you want your form to run a timer event every few minutes to check for unsaved changes. You should set this interval to a value that makes sense for your environment. Running the check every few seconds might be too frequent and could annoy users if it interrupts their typing. For demonstration, I set the interval to five seconds, but in practice, I suggest at least a couple of minutes.
To set this up, go into Design View on your form, access the form's property sheet, and locate the Timer Interval property. This property is measured in milliseconds, not seconds, so for two minutes you would set it to 120,000. Set the On Timer event to run some VBA code whenever the timer fires. In this code, you first check if the form is dirty. If it is, you use the Dirty property to trigger a save. Setting the Dirty property to False will commit changes to the table. You could also use the Refresh method to update calculations, but for this purpose, saving the record is all you need.
As a visual confirmation that the auto save is working, I mention adding a beep or updating the form's caption to show when the record was last saved. For the final version of your project, you can remove the beep if you prefer not to disturb users. As an additional tip, remember that changing the form's caption will itself make the record dirty, so you want to adjust the caption before clearing the Dirty property so you do not trigger unnecessary saves.
Be cautious if you have the VBA editor open while your form is running, because the timer event continues in the background and can cause errors when you are working with your code. Always close the form before editing its code to prevent problems.
One possible annoyance with auto saving while a user is typing in a long text field is that the timer might force a save while the record is actively being edited, which can disrupt their workflow or reposition the cursor. Users will not lose data, but it can be irritating if the save interrupts their work by refreshing the form.
To handle situations like this, in the Extended Cut for members, I will show you how to temporarily disable the auto save if a user is still actively typing or clicking within the form. This allows the timer event to pause while the record is being edited, but resume auto saving once the user steps away. This requires additional code and some logic to detect user activity.
Extended Cut videos are available to my Silver members and above, giving you access to all past and future advanced tutorials. If you are interested in learning more about Access development and VBA programming, I have many comprehensive courses on my website.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Setting up automatic saving in Access forms
Using the form Timer event in Access
Configuring the Timer Interval property
Checking the Dirty property to detect unsaved edits
Saving the current record with Me.Dirty = False
Adding a visual auto save notification using the Caption property
Demonstrating timed auto save with VBA code
Explaining user experience considerations of auto save |
