Prevent Shutdown
By Richard Rost
3 years ago
Prevent Accidental Shutdown Of Your Database
In this Microsoft Access tutorial I'm going to teach you how to prevent accidental shutdown of your database when the user clicks on the close button for the entire application.
Tyrone from the United Kingdom (a Silver Member) asks: When my users go to close a report in Print Preview mode, then often click the top-most "X" to close the Microsoft Access application itself instead of closing the report. This shuts down the database. Do I need to educate them to use the correct close button, or is there a way that when they click the wrong button it can just close the report instead?
Members
Members will learn how to automatically close a report if it's open and the user tries to close the database. We will also learn how to loop through all of the reports in the database and close them if they are open. And I'll show you how to hide the print preview ribbon.
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
Links
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, On Unload, OnUnload, Form_Unload, Cancel closing a form, currentproject.allreports, loop through reports, isloaded, showtoolbar, hide print preview ribbon tab
Intro In this video, we will talk about how to prevent accidental shutdowns of your Microsoft Access database, especially when users mistakenly close the entire application instead of just a report in print preview mode. I will explain how to use a simple VBA routine with the form's unload event to prompt users with a confirmation message before allowing Access to close. We will cover how to adjust form properties to safeguard the main menu and walk through setting up a message box with Yes, No, and Cancel options to help prevent unwanted database shutdowns.Transcript Today we're going to talk about how to prevent an accidental shutdown of your Microsoft Access database. This one has actually been on my list for a while, but today in my forums on the website, Tyrone from the UK, one of my Silver members, posted this question, which is very similar.
Tyrone says when my users go to close the report in print preview mode, they click on that topmost X all the way up on the right. To close Microsoft Access itself instead of closing the report, this shuts down the database. Do I need to educate them to use the correct close button or is there a way that when they click the wrong button, they can just close the report instead?
Yes, we could definitely do that. Education is always the first step. That's always the first thing I recommend: teach your users properly. I am a big fan of John Taffer and Bar Rescue, and the number one thing is you have to train your employees. That's what I do for a living. So training is definitely the most important thing. Teach them what to do. But it's always good to have a backup in case they forget. Even I forget stuff from time to time. So I build in little backups in my database to safeguard stuff like this. Let me show you what to do.
Now, before we get into it, this is going to be a developer-level video. I love this tool. So we are going to use a little bit of VBA, not much, like three lines of code, but you have to know where to put those three lines of code. If you've never done any VBA programming before, go watch this video. It's my intro to VBA. It's about 20 minutes long. It'll teach you everything you need to know to get started.
Now, if the user accidentally clicks that wrong close button to close Access, we're going to pop up a message box to ask them, "Are you sure?" We're going to say, "This is going to close the database. Are you sure you want to do this?"
So we need to know how to use the message box. If you've never done that before, go watch this video on the message box. You'll need to know how to make the "Are you sure" box and you get the response back, how to handle that response. When we get that value from the message box, we have to parse it. We have to know what to do if they say yes, no, or cancel. So we'll need an if-then statement to be able to process that response. Finally, we have to know where to trigger that message box to pop up.
Now, we're going to use the form's on unload event. I'll explain why in a minute, but unload is very similar to something that happens in the before update event before you update a field. Go watch this video first too if you've never used the before update event. These are all free videos. They're on my website and my YouTube channel. Go watch them first and come on back.
Here I am in my TechHelp free template. This is a free database. You can download a copy from my website if you want to. Here's what's happening: Tyrone's got a report that opens up. Let's open up my invoice report here. Now he's got them open all just stuff maximized.
With a report, you get this report print preview menu. When they go to close this, instead of hitting this close print preview button, that's a training thing. Instead of hitting that, they're hitting this. This shuts the whole database down because that's where they're thinking it's got to go. If you have everything in your database maximized, like a lot of people do, it looks like this normally. People are used to the close buttons being up here. I like to give people buttons myself, like on the bottom. Put all the buttons you need down here or in a spot where they're all in the same place. But instead of clicking on that, people click on this and shut the whole database down.
So how do we prevent this? What do we do? When you have this open, I always have a main menu form open in the background. Always. You can disable the user's ability to close this menu by simply going into the properties. On format, turn off the close box or the control box and the close button. Turn those off.
Now the user cannot close the main menu. It's always open. If they shouldn't have access to design mode, you can close it by going to design view, but your users should not be able to do that. If you're using a properly secured database, go watch my simple security video for more information on that. I'll put a link down below.
Now, since the only way that your users can close this main menu is by closing the database, what we're going to do is say, "If they try to close the database accidentally, it's going to close this menu," because when the database closes, the first thing Access does is it goes and closes all of its objects. It closes all the open forms, closes all the open reports, the tables, the queries, and so on. In so doing, it fires all of the events that are associated with those objects.
So all we have to do is put an event handler on the main menu and say, "If the user attempts to close this menu, that assumes that they're trying to close the database, ask them if they're sure." At that point, when they think that they're just closing this report, they're actually closing the database. Then the form can say, "Are you sure you want to do that?" If not, forget it then. We just won't close down. Hopefully that will trigger their training and they'll be like, "Ah, yeah, I clicked the wrong thing."
So how do we do this? We're going to go into design view of the main menu, open up its property sheet. That'll bring up this guy over here. Go to events. There are two main events that fire when you close a report: load and open when you open a report, and unload and close when it closes.
The difference is, on close, if you look at it - let me click the dot, dot, dot button here - on close is just a normal form close. This happens after you've already committed to closing the form. It's gone. It's done. Goodbye. The other one is called on unload, right down here on unload. The difference between close and unload is that unload can be canceled. You can do this if you want to check to make sure certain conditions have been met before you close it.
I've got another video called "prevent close." Let's say you're putting in a loan application and you've got to make sure that the user types in, you know, their security number, or their principal. If they're missing any net data, you can prevent the user from closing the form right here with the unload event. Again, I'll put a link to that video down below as well, but you can also use form unload to cancel closing the form if by any other means, like this, they accidentally try to close the form.
Here we'll use a message box. We'll say if message box - we're going to use the message box function. It's going to pop up a box and it's going to return the button they click on. So we're going to put a prompt in here: "Warning warning danger Will Robinson. This will close the database. Are you sure? Yes or no?" Comma. The next is what do you want in the box itself? You can specify the little picture that goes in there. You can specify the buttons that you want if you've watched my other video. I'm just going to use the line continuation there. I like vbYesNoCancel because it gives them the Yes, No, and Cancel buttons. A lot of times if users aren't sure, they're not sure whether to answer yes or no, they'll just say cancel, which is just abort. So it's to prevent brain freeze. I like Yes, No, Cancel.
Then I'm going to add to that plus vbCritical, which gives you the little critical warning icon. There's information, there's exclamation point, there's critical. Then what do you want for the title? Close database and ignore the rest of that. So close that. Message box is going to return a value. So we're going to say if message box something, something, something, all right, if they don't answer yes. So if it's not equal to vbYes, then they've indicated either no or cancel at this point. We're going to cancel the event: Cancel = True and Exit Sub.
Technically, you don't need the Exit Sub there because it's going to drop out of the If...Then anyways, but I always put the Exit Sub there in case later on you decide to put more stuff down here and you don't realize it.
Let's give it a quick debug compile. Everything's good. Let's close it. Save changes? Yes. Close everything down. Restart the main menu. I got a little button up here on my toolbar that does it. Let's open up that report. Orders, open up the report. Now at this point, I forget that I have to click on this guy. So instead I click on this guy. What's happening? It's trying to close the main menu in the background. When you try to close the database, it says, "Warning: this will close the database. Are you sure?" No? It leaves it open. Now the user can hopefully remember and click the close button.
But if they do want to close the database, it says, "Warning: this will close the database. Are you sure?" They hit cancel, obviously cancels. They hit yes, it shuts the database down just like they wanted to do that. That will happen anytime, anywhere that they try to close the database to make sure "Are you sure you want to close the database?" That's it.
Now is there a way we could just shut that invoice down if it sees the report is open? Of course there is. We can loop through all of the reports in the database and check to see if any of them are open. If that's the case, just shut those down, assuming that the user meant to do that instead. I meant to do that. It's like Peewee. I'll show you some tricks for hiding that print preview ribbon if you don't like it in the first place.
We'll talk about all that in the extended cut for the members. Silver members and up get access to all of my extended cut videos. There are hundreds of them by now. Lots of stuff to learn. Gold members can download these databases and get access to the code vault.
If you want to learn more cool Microsoft Access developer programming, I've got lots of developer training available on my website. I just released Access Developer Level 43. I mean, a lot of them by now. Hundreds of hours of training and you learn in the right way. None of this jumping around between different videos. I just teach you ABCD. You follow along.
We have a good time. We learn how to program and build databases and you get a raise. Well, the raise is not a promise. Hopefully if your employer is smart and you get better at building your databases, you get a raise. Hopefully send them to me if they don't want to give you a raise. I'll take care of them. I'll tell them where to go.
That's going to be your TechHelp video for today. I hope you learned something. Come join us for the after party and the members-only extended cut.
Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main problem described in the video regarding report closing in Microsoft Access?
A. Users are unable to print reports from Access
B. Users accidentally close the entire database instead of just closing a report
C. Reports are not saving changes before closing
D. Users keep opening multiple reports by mistake
Q2. According to the video, what is always the first recommended step to prevent accidental database shutdown?
A. Add more warning messages
B. Train users on the correct procedure
C. Restrict user permissions
D. Remove the close button from all forms
Q3. Besides training, what backup method does the video suggest to prevent database shutdown?
A. Lock the database file
B. Implement an automatic backup system
C. Use VBA to confirm shutdown actions
D. Disable opening reports in print preview mode
Q4. Which Access event does the video use to intercept and prevent the accidental closing of the database?
A. OnOpen event
B. BeforeUpdate event
C. OnClose event
D. OnUnload event
Q5. Why is the OnUnload event preferred over the OnClose event for this situation?
A. OnUnload fires earlier and can be canceled
B. OnUnload is simpler to program
C. OnUnload only runs for reports
D. OnUnload always prompts the user
Q6. What VBA function does the video use to prompt the user with a confirmation dialog?
A. InputBox
B. SwapBox
C. MessageBox
D. WarningBox
Q7. What parameter combination does the video recommend using with the message box to give users the most flexibility?
A. vbOKCancel
B. vbYesNo
C. vbYesNoCancel plus vbCritical
D. vbRetryCancel
Q8. If the user clicks No or Cancel in the confirmation dialog, what should the code do?
A. Proceed with closing the database
B. Save all changes and log out the user
C. Cancel the event and keep the database open
D. Display an error message
Q9. How does ensuring the main menu form is always open help prevent accidental shutdown?
A. It logs all user actions
B. Closing the main menu triggers the database shutdown process, allowing the confirmation prompt to be shown
C. It restricts user access to reports
D. It automatically saves work at regular intervals
Q10. What additional feature (discussed for members) could further improve user experience related to accidental report closing?
A. Locking the database after every report
B. Looping through open reports and closing them instead of closing the database
C. Automatically saving reports to a PDF
D. Removing the print preview feature entirely
Answers: 1-B; 2-B; 3-C; 4-D; 5-A; 6-C; 7-C; 8-C; 9-B; 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 TechHelp tutorial from Access Learning Zone focuses on strategies to prevent your Microsoft Access database from being shut down accidentally. This topic has come up frequently, and today a question from one of my forum users highlighted it again. The issue is that users working in print preview mode on a report may hit the main Access close button in the application window instead of the appropriate close button for just the report. This action shuts down the entire database.
While training your users is always the first and best solution, it does not hurt to have built-in safeguards for those moments when someone forgets. After all, even those of us with plenty of experience forget sometimes. I always recommend including backup methods in your databases to improve usability and protect against these kinds of mishaps.
Today's lesson is geared toward developers or those comfortable making changes to the database with a little bit of VBA. We only need a tiny bit of VBA code, but knowing where and how to place that code is important. For those who are completely new to VBA, I suggest watching my introductory VBA tutorial first to get up to speed.
The plan is straightforward. If a user mistakenly tries to close Access entirely instead of a report, we want a warning message box to appear, confirming their intent. We want to ask, "This will close the database. Are you sure you want to do this?" To accomplish this, you should be familiar with how message boxes work in VBA, how to process the user's response, and how to use If...Then statements to take the right action. Additionally, it is important to understand where this code should run.
The best place to control the closing of the database is in the main menu form's OnUnload event. If you are not familiar with how form events in Access work, such as BeforeUpdate or OnUnload, I recommend reviewing those concepts. The OnUnload event is particularly useful here because it can cancel the closing process, giving us a chance to warn the user.
In my databases, I always have a main menu form open in the background. You can prevent users from closing this form by adjusting its properties—turn off the control box and close button in the form's format properties. This leaves users only one way to close the main menu: by closing the Access database itself. When Access closes, it shuts down all open objects, including forms and reports. Each closing event triggers its corresponding code, meaning we can use the main menu's OnUnload event as our safeguard.
To implement this, switch your main menu to design view, open the property sheet, and go to the events tab. The OnUnload event (not OnClose, which occurs after the form has already closed) can be used to interrupt the closing process and display a message box. When the message box appears, it can offer Yes, No, and Cancel buttons, along with a critical warning symbol, making it clear to the user that they are about to close the database. If the user selects No or Cancel, the event is canceled and the database remains open.
Let's talk through the behavior. Suppose the user tries to close Access with the main menu still open. The event handler pops up a warning. If the user answers anything other than Yes, the database stays open. If they choose Yes, the database closes as intended. This interrupt gives users a moment to reconsider—and, hopefully, to realize they may have hit the wrong button.
As for handling users who routinely close reports the wrong way, there are ways to close open reports automatically if you detect they are still open when someone tries to close Access. This can add an extra layer of friendliness to your database by anticipating and correcting common mistakes. Additionally, if you want to hide the print preview ribbon or customize the user interface further, it is absolutely possible.
All of these advanced topics will be covered in today's Extended Cut video. In the Extended Cut, I will demonstrate how to loop through the open reports, close them automatically under certain conditions, and share some tricks for customizing the print preview interface to make it less confusing for users.
If you want to continue learning about Microsoft Access programming, be sure to check out my developer-level training courses. They guide you step-by-step, in the correct order, through all of the essential skills you need to build robust and professional databases.
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 Preventing accidental shutdown of an Access database
Using the form's OnUnload event
Adding VBA code to the main menu form
Disabling the close box on the main menu
Disabling the control box on the main menu
Prompting users with a message box before closing
Handling message box responses with VBA
Canceling form closing in the OnUnload event
Explaining difference between Unload and Close events
Setting Cancel=True in the Unload event
Using vbYesNoCancel and vbCritical in MsgBox
Testing the solution in a maximized window scenario |
