Disable AutoSave
By Richard Rost
3 years ago
Disable Automatic Record Saving in Microsoft Access
In this Microsoft Access video tutorial I will teach you how to prevent Access from automatically saving records when you make edits unless you specifically allow it. When the user tries to leave the record, we will allow three different options. They can either accept the changes and save the record, not save the changes, or cancel leaving the record and return to editing. Once again, we'll take a look at how ChatGPT tackles this problem.
Pre-Requisites
Links
Recommended Courses
Subforms?
- Tons of people have asked me if it's possible to do this in a subform with continuous forms and multiple child records. The answer is yes. I cover that here: Disable AutoSave Subform
Source Code
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response
Response = MsgBox("Save changes. Are you sure? (Yes=Save Changes, " & _
"No=Abort Changes, Cancel=Continue Editing)", vbYesNoCancel + vbQuestion, _
"Save Changes?")
If Response = vbYes Then
Cancel = False
ElseIf Response = vbNo Then
Cancel = False
Me.Undo
Else
Cancel = True
End If
End Sub
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, disable blind saves, automatic saving of edited records, disable automatic saving, How do I turn off AutoSave in Access
Intro In this video, I will show you how to temporarily disable automatic record saving in Microsoft Access by adding a confirmation prompt when users try to leave a record or close a form. We will use the Before Update event and some basic VBA programming to ask users if they want to save changes, abort changes, or continue editing. I will walk you through applying this technique to your forms, demonstrate the message box options, and explain how this solution works to help prevent accidental data changes in your Microsoft Access databases.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 disable automatic record save in Microsoft Access. This is something I get asked once in a while. People say they open up a record and make some changes to it - either intentionally, accidentally, whatever - and then if they close the form or leave the record, those changes get saved. That is the way that Access is designed to behave. Any changes that you make will be saved to the table as soon as you either close the form or move to a different record.
Some people, however, do not like that option. They want it to act more like Microsoft Word, where if you make a change to a document and then close the document, it prompts you and says, "Do you want to save this record?"
Access does not have this functionality built in, but with a little bit of programming, we can add it. We will make a little pop-up that says "Save changes? Are you sure?" and give the user three options. They can save the changes and continue (either move to a different record or close the form). "No" will abort the changes, meaning do not save what was just typed in. Or "Cancel" means they did not mean to leave the record or close the form - just cancel it and put it right back into editing. We will do all three of those, but we'll start off simple.
This is a developer level video, which means we are going to need a little bit of VBA programming. Do not be scared though. If you have never done any VBA programming before, go watch this video. It is about 20 minutes long and teaches you everything you need to know. Then go watch this video. If/Then statements - we are going to use one of these. You will need to know how the message box works.
Normally, you can just use message box to prompt a user, giving a message and saying okay, but you can actually use message box to get a response back: yes or no. So go watch this. And finally, go watch my video on Before Update. This is an event that runs before changes are saved to the table. There is a Before Update event for both a control, like a text box, and for the form itself. That way, before all of the changes entered are committed to the table, you can do something. And that something will be to prompt the user, "Are you sure?"
These are all free videos. They are on my website and my YouTube channel. Go watch them if you do not know any of this stuff. Then come back and we will go through it.
We have to put this code in whatever form you want this behavior in. So let's go to the customer form. We are going to right-click, go to Design View, bring up the form's properties by double clicking right there on that little box, and then go to the Events tab. Find the Before Update event. This event will run before the form is updated. In other words, you can type in whatever changes you want, in whatever fields you want, on this record, and before you either move to a different record or close the form, it is going to prompt you, "Are you sure?" That is what we are going to do.
Click on the dot dot dot right there. That will open up your VB editor. We are sitting right here in the Private Sub Form_BeforeUpdate. Notice the "Cancel As Integer." One of the major benefits of Before Update is that it can be canceled before the changes are committed, as opposed to After Update - After Update takes place after the record has been saved to the table.
Let's put some blank space in here. I am going to dim a variable called response. I am just going to leave it like that. It is going to be declared as a variant. Technically, MessageBox returns an integer, but that does not matter - you do not have to give it a type; that is fine.
We are going to say: response = MessageBox("Save changes? Are you sure?", vbYesNoCancel, "Save Changes"). You could do vbYesNo, but I like to include Cancel in there because sometimes users are not sure, and they see a pop-up and their first instinct is to hit Cancel. If they hit Cancel, it just puts them right back where they were.
That is unless they are not thinking. The title, if you want, "Save Changes" is fine.
MessageBox is going to pop up, say "Save changes? Are you sure?" The user is going to see three buttons: vbYes, vbNo, and vbCancel. Yes, No, and Cancel.
Let's do the simple one first. We are going to say: If response is anything other than vbYes, then Cancel = True, and then it will exit out because there is nothing else after that. You could put an "Exit Sub" in there if you want to; in fact, at this point, you could just do that.
They are going to be asked if they want to cancel. If they say anything other than "Yes" (or "Save changes?"), if they say anything other than Yes, it will cancel and exit out and put them right back where they were.
Save that. Back over here. Let's close it down. Open it up. Now, if I do not make any changes to anything, it behaves as normal. But if I come in here and try to change this back to Richard, and I can make other changes too.
Now, if I try to leave the record or close the form, it says "Save changes? Are you sure?" If I am not sure and I hit No or Cancel, it just leaves me right there. Try to leave, hit Cancel or No. If I try to leave and I say Yes, it does move to the next record and it commits those changes. See that?
Now, let's add a third level in here. I might want to save changes (Yes) and also move to the next record. I might want to say No, which will mean do not save those changes but continue on to the next record - kind of like an abort. The third option, Cancel, will do what we just saw where it will cancel those changes and leave me there sitting on my still-being-edited record.
What I am going to do is put right here in the prompt what is going to happen so the user knows: Yes = Save changes, No = Abort changes, and (I'll continue this on to the next line) Cancel = Continue editing.
We have to take all three of those options into consideration. So I am going to say:
If response = vbYes Then
Cancel = False
ElseIf response = vbNo Then
Cancel = False
Me.Undo
Else
Cancel = True
End If
If they say Yes, Cancel is False, so go ahead and save your changes and move on. If I say No, Cancel is also False, but undo the changes and move on. If I hit Cancel, then Cancel is True, meaning I want to stay here without saving anything.
Save it. Give it a quick Debug > Compile. Come back out, close it, open it up. All right, it puts me back to where I was. Move to the next record: "Do you want to save changes?" Yes, save it.
Go back. Let me make a change here. I will become Richard Rosti. Move to another record. This time, I am going to abort the changes. I am going to say No. It did not cancel. But if you go back, it undid the change. See that?
And finally, the third option: Cancel. It leaves you right where you were. You can hit Escape if you want to.
Now, one thing to point out: If they do cancel the changes when they try to close the form, if I say Cancel, you are going to get another error message saying "You cannot save the record at this time" because the Close event is also running. The user just says "Do you want to still close the database object, anyway?" Say No and you will stay right where you were.
There are ways around that error message, too. If you want to see that, post a comment down below in the comment section and I will cover that in a future video.
Most of this is just user perception. A lot of users are used to having to save the record, like if they are working with Excel or Word documents. They are used to making some changes and having to manually save it with a Save button or do something. Access is kind of unique in the way that it behaves - when you make a change, it automatically gets saved for you.
I do have another video that I recorded a little while ago where we can actually put a Save button on the form for those users who are like, "How do I save this?" They do not realize that they just have to close the form or leave the record. It is a training issue, but a lot of people do not get proper training. That is what I am here for.
But if you want to put a Save button, go watch this video. I will put a link down below.
As I have been doing lately with a lot of my videos, after I put the video together or at least run through it myself, I like to see what ChatGPT has to say about the topic. I fed my question into ChatGPT and let's see what it gave me.
I said, "How do I disable automatic record save in Microsoft Access?" It said to go to File - Options - Client Settings - Saving Databases - and then Automatically Save Object Design Changes. It is thinking that I mean automatically saving changes to the forms or reports themselves.
I also decided to throw it a curveball. I said, "Well, that option does not exist in Access 2021. What do I do now?" It gave me some more, and always apologizes: "I apologize for the confusion." It is okay. I said, "In Access 2021, the option to disable automatic record save is no longer available." That is true. I think it used to be back in 2003. You could do that under File - Options.
Now it is telling me to go into Options - Client Settings - Advanced - and then Confirm Record Changes. That is in there, but that has to do with Action Queries. If you run an Update Query, for example, Access says, "You are about to modify 15 records. Are you sure you want to do this?" That is what that setting is for.
Then I said, "Well, Confirm Record Changes is for Action Queries looking up the query. I want the user to confirm any manual changes to the record." Again, it apologized. Then, guess what? It came up with my solution. Very similar to it: Before Update event, MessageBox, "Are you sure you want to save changes?" If it is No, Cancel is True.
ChatGPT got to what I wanted after I had to corral it a little bit, but it is getting there. It is really starting to get smart. If you have got a problem or question and want to see how to do something like this, fire it into ChatGPT. It is scary because with just three questions, it got some code that took me years to learn.
That is your Fast Tip for today.
Oh, I forgot to mention a class. I covered this in a lot more detail in Access Developer Level 24. We spent a lot of time with Before Update. This is a great lesson, too, because it has one of my favorite things that people always ask me: copying an order with details. You want to copy a record and all of the related child records. So you have an invoice. The invoice has an order information and order record and then multiple detail records. You want to copy all of that, maybe take an estimate and make it an invoice, but you want to leave the original estimate in the system and make changes to the invoice. This is a very good lesson if you want to learn about that.
Now we are done. That is your Fast Tip for today. I hope you learned something and had some fun. ChatGPT is not as fun as me yet. Live long and prosper, my friends. I will see you next time.Quiz Q1. Why does Microsoft Access automatically save changes to records?
A. Because Access is designed to commit changes as soon as the form is closed or a different record is selected
B. Because Access requires a manual save after every change
C. Because the default setting disables record changes
D. Because Access does not allow users to modify data
Q2. What feature do some users expect from Access that is more similar to Word or Excel?
A. A prompt asking whether to save changes when leaving a record
B. Automatic undo of changes after a fixed time
C. Requiring a password for every field change
D. Real-time collaborative editing
Q3. Which VBA event is used in Access to intercept and potentially stop record changes before they are saved?
A. BeforeUpdate
B. AfterUpdate
C. OnClick
D. OnOpen
Q4. What is the main benefit of using the Before Update event on a form in Access?
A. It allows you to cancel changes before they are saved to the table
B. It allows Access to auto-correct user input
C. It enables automatic printing of records
D. It creates a backup of the table
Q5. What does the MessageBox function in VBA do in the context of this tutorial?
A. Prompts the user to save changes with Yes, No, and Cancel options
B. Automatically saves the record without user input
C. Deletes the current record after confirmation
D. Logs the user out of the database
Q6. What is the purpose of the Cancel parameter in the Before Update event?
A. To stop changes from being saved if set to True
B. To force the database to refresh the record
C. To bypass all error checks on the form
D. To finalize the record changes immediately
Q7. If the user selects No in the custom save prompt, what should happen according to the tutorial?
A. The changes should be undone and the user should proceed to the next record
B. The form should close and discard all records
C. The record should be deleted from the table
D. The changes should be saved anyway
Q8. Which of the following is NOT a correct response for the MessageBox prompt described in the video?
A. Yes - save changes
B. No - undo changes and move on
C. Cancel - stay in edit mode
D. Ignore - apply changes in background
Q9. If the user selects Cancel in the message box, what occurs?
A. The user remains on the current record and continues editing without saving changes
B. The form closes immediately
C. The changes are automatically saved
D. All input fields are cleared
Q10. Why does Access sometimes display an error message if Cancel is selected when the user tries to close the form?
A. Because the Close event still runs and Access expects the record to be saved or discarded
B. Because the user lacks permission to close the form
C. Because there are too many records in the table
D. Because Access automatically undoes all changes without notice
Q11. What misconception does ChatGPT initially have about disabling automatic record save in Access?
A. It thinks the setting is for saving design changes, not data changes
B. It suggests updating Windows registry settings
C. It recommends uninstalling and reinstalling Access
D. It claims this feature never existed in any Access version
Q12. What does the "Confirm Record Changes" option in Access Client Settings actually relate to?
A. Confirming changes in action queries like update or delete, not manual record editing in forms
B. Asking for confirmation before editing any record manually
C. Disabling all automatic saves in the database
D. Preventing any changes from being made to forms
Q13. How does the tutorial's approach ultimately help users who want explicit control over data saving in Access forms?
A. By prompting them before every record save and allowing them to confirm, undo, or continue editing
B. By hiding all save-related prompts from the user
C. By deleting records after every form submission
D. By creating a backup copy of each form
Q14. What is one alternative mentioned for users who prefer manual saving?
A. Adding a Save button to the form
B. Disabling all buttons in the form
C. Allowing only administrators to edit records
D. Restricting edits to datasheet view only
Q15. What is a common user training issue with Access as discussed in the video?
A. Users do not realize that Access saves records automatically when leaving a record or closing a form
B. Users frequently attempt to use Excel formulas in Access
C. Users always forget their database passwords
D. Users do not use Access reports
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-D; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 covers how to disable automatic record saves in Microsoft Access. This is a topic that many Access users ask about, especially those who come from programs like Microsoft Word where you're prompted to save changes before closing a document.
By default, Access automatically saves any changes you make to a record as soon as you either leave that record or close the form. This can cause problems if someone makes an accidental change and does not realize it has already been committed to the database. Many people would prefer if Access acted more like Word, prompting, "Do you want to save this record?" before any changes are finalized.
Although Access does not offer this feature natively, you can add it with some VBA programming. In this lesson, I will explain how to set up a pop-up prompt that asks the user if they want to save changes, giving three options: Yes to save the changes and continue, No to discard the changes and move on, and Cancel to keep editing the record without saving or leaving.
To achieve this, we will need to use some basic VBA code in the form's Before Update event. If you have never worked with VBA before, I recommend watching my introductory videos on VBA basics, If Then statements, the MessageBox function, and specifically, the Before Update event. All of these are available for free on my website and YouTube channel.
The Before Update event is key here because it runs every time changes are about to be saved to a record. Unlike the After Update event, which triggers after the changes are already written to the table, Before Update allows us to intervene and even cancel the operation before anything is permanently saved.
To implement this feature, you first open up the form for which you want to control the save behavior. In Design View, bring up the form's properties and navigate to the Events tab to find the Before Update event. In the VBA editor, you'll be working inside the Private Sub Form_BeforeUpdate procedure.
You will create a variable to hold the user's response to the MessageBox prompt. The MessageBox will display a custom question like "Save changes? Are you sure?" and offer three buttons: Yes, No, and Cancel. I suggest using all three options because sometimes users want a way to pause or cancel the operation without making a decision right away.
In the code handling this, if the user selects Yes, you allow the operation to proceed and Access saves the changes. If the user chooses No, the changes that were just made are undone but you still move to the next record or close the form. If Cancel is picked, everything stops and the user stays on the current record, still able to edit.
After saving and compiling your code, test it out. Without making changes, everything works as normal. If you edit a record and try to leave, the pop-up prompt will ask what to do. Choosing Yes saves, No discards the changes and moves on, while Cancel keeps you in edit mode. If you try to close the form and opt for Cancel, you might see an additional error message because of how Access handles unsaved changes in form close events, but there are ways to manage that as well, which I can cover in a future tutorial if you are interested.
Users often assume Access should behave like other Office products, but it is unique in its approach to data storage. For users who prefer an explicit Save button, I have a separate video that explains how to add one to your forms. This is also a matter of training, as many users do not realize how Access handles saving by default.
I also did a quick experiment with ChatGPT to see how it answered this question. Initially, it misunderstood, confusing saving form design with saving record data. After a few clarifications, it reached a similar solution using the Before Update event and a user prompt. This highlights how learning the fundamentals gives you more control and understanding than relying too heavily on AI suggestions, though tools like ChatGPT are improving every day.
If you are interested in a more in-depth study of the Before Update event and related development topics like copying records alongside their child detail records, I cover these topics fully in Access Developer Level 24, available 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 Disabling automatic record save in Microsoft Access forms
Adding a save confirmation prompt using VBA
Using the Before Update event of a form
Writing VBA code to prompt users before saving
Configuring the MessageBox function for Yes No Cancel
Handling user responses to the save prompt
Undoing changes with Me.Undo on user selection
Explaining the difference between Before Update and After Update events
Demonstrating form behavior with the save prompt in action
Preventing unintended record saves with custom logic |
