On Unload
By Richard Rost
3 years ago
Using the On Unload Event in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you how to use the On Unload event to perform data validation across multiple records and provide a friendly error message when you go to close a form in Microsoft Access.
Freya from Oslo, Norway (a Platinum Member) asks: I have a form where my employees enter a number of job codes for a particular project. Each job code is on a separate record. They have to enter at least five job codes but no more than eight. Is there any way I can check that before they're allowed to close the form?
Members
There is no Extended Cut, but here's the database for the Gold members.
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 Event, Data Validation, Form Closing, Error Messages, Multiple Records, Validation Rules, Friendly Error Message, Form Controls, Records Validation, form close, on close, on unload
Subscribe to On Unload
Get notifications when this page is updated
Intro In this video, you will learn how to use the on-unload event in Microsoft Access to validate data across multiple records before closing a form. We'll walk through a practical example using DCount to check for a minimum and maximum number of related child records, display user-friendly error messages if validation fails, and discuss the differences between the on-unload and on-close events. This video is ideal if you want to ensure your users enter the correct number of records before they can exit a form in Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, I'm going to teach you how to use the on-unload event to perform data validation across multiple records and provide a friendly error message when you go to close a form in Microsoft Access.
Today's question comes from Freya in Oslo, Norway, one of my platinum members. Freya says, I have a form where my employees enter a number of job codes for a particular project. Each job code is on a separate record. They have to enter at least five job codes with no more than eight. Is there any way to check that before they're allowed to close the form?
This is one of the tough things about my job: taking what you're doing and translating it into something that everyone can relate to because you've got job codes and project IDs and I don't know what any of that means and neither do the people watching.
Let me translate that. Let's say you've got a customer and they've got orders. Same thing. You've got one table with a child table. In your case, the project is the parent and the job codes represent the sub-records or the child records. You're saying that each customer must have at least five orders but no more than eight.
We can do this with something called the on-unload event before the form closes, before whatever form you've got that they are entering in these codes. Before they're allowed to close that form, we're going to do a little validation.
Now, if it's a single field, that's easy. We could use a validation rule. And if it's a single record, we could use something like the before update event. I've got separate videos on that. I'll give you links to them in just a second.
But since this involves multiple records, we have to use something a little more creative. So I would choose to use the on-unload event for this. Give them a form where they can enter the stuff. Then before they're allowed to close that form, check it. And if not, don't let them close it. We'll give them an error message and say, he didn't say the magic word. Jurassic Park, the whole movie would have been... Something would have happened if they had just simply paid their IT guy what he was worth. Pay your computer guys, people.
Alright, here we go. Now this is going to be a developer level video, which means you need to know a little bit of VBA. Now, if you've never programmed in VBA before, don't worry. It's okay. I've got a video that'll teach you everything you need to know in about 20 minutes. Don't panic. Watch this first. And also go watch this video too.
Very similar to what we're going to be showing you today. But before update, the before update event happens after you've done editing or typing in a record and before you're allowed to leave it, before you can close that record, before you can move off of it, this event runs. So you could technically do this in this event too. On-unload gives me a chance to show you another tool in your box.
Usually I use before update to check single record stuff, like if you've got to check multiple fields in a single record.
And while you're at it, go check out DCount too. We're going to use DCount today to count the number of records in a particular table or query with criteria. These are all free. They're on my website. They're on my YouTube channel. Go watch them and come on back.
Alright. So here I am in my TechHelp free template. This is a free database you can download off my website if you want to.
Let's say we've got our customer list here and using something along Freya's lines, let's say that we don't want to allow more than five customers in our database from Florida. Before the user is allowed to close this form, we're going to check to make sure there are five or fewer Florida people in here. You don't want too many Floridians in your whatever. Too many of us cause problems.
Again, you could put this in a record's before update event, but you can also put it here in the on-unload.
Now, on-unload is unique. There's on close. Where are you? I can never find these guys because they're in a weird order. So I hit the little button here to sort them and that's a glitch there. The button goes away. Look at that.
Okay, there's on close and there's on-unload. Very similar. Let me show you both of them and you can see what the difference is. Here's on close. If I click on that, it generates the on close sub. And if I generate the on-unload, look at that one. It's slightly different. See the difference there? I'll give you a hint. That bad boy. That's not really a hint. I just give you the answer. But form close means, okay, it's shutting down. We're done. You can't do anything about it. It's going to close either way. You might want to do some other stuff in here, but on-unload happens before that and you can still cancel it.
It's kind of like before update and after update. Before update, you can check some stuff and say, well, this isn't right. You can't leave this record yet. We're not going to save it. In here, we can do stuff before the user's allowed to close the form, because you might want to let them put in whatever records they want. Then before they close the form, check it and say, oh, wait, you've got eight Floridians in there. You can only have five. You need to go and delete three of them.
Let's say you're making a baseball roster. You've got to have nine players, no more than 12 or whatever for your little league. Those kinds of things.
So, how do we do this? Well, a little bit of programming here.
So, dim x as long. We're going to count up the number of Floridians. So x equals DCount all the records from the customer table, where the state equals Florida. And remember, since Florida is a string, it's inside quotes. And those quotes are inside another string, so you have to use double double quotes. If you haven't watched my double double quote video yet, go watch that if you're confused. I'll put a link to it down below in the description.
Alright, close that up. Now if x is greater than five, then yes, in case you're curious, we could have done this without the x. I just said if DCount blah, blah, blah. I prefer throwing the number into a variable. It's just my style. I've been doing it forever. I don't usually like directly checking that as part of an if statement. I've always considered that bad code. Plus if this throws an error, then you get problems.
Alright, anyway, we've got more than five of them. What happens? There's going to be a lot of drunk people in the room. First of all, just kidding.
Alright. Message box: Sorry, for safety reasons, safety and sanitary reasons. I'm a Floridian, I can pick on other Floridians. Only max five Floridians allowed. Otherwise a Jimmy Buffett concert breaks out. Okay.
So now at this point, we've given the user a warning. You've got to delete some Floridians. We're going to say cancel equals true. I know it's an integer, but I always give it true and false. It's looking for zero and not zero.
That's it. That's all you need. Like I said, most things you want to accomplish in Access don't really require that much programming. A couple lines, three, four lines and you're good. We probably could have done this with just three.
Alright. Save it. Always throw in a little debug compile. Close it. Close it. Yep. Open it. Oh, wrong one. That brings up another point. I opened up the wrong form. If you have multiple forms where users can add records and change things, we just put it in this form here. But this form can also edit and add customers. So you have to put that code in both of these, and you don't want to have the same code in both forms. So you make it in a global module out here, and then each one of these guys just calls that one. But that's a topic for a whole different video.
So we just assume this is the only form you can add records in. Right now I've got four of them. If I close this, we're good.
If I come in again, I'm used to going to this one for some of our videos. If I come in here and add someone else. Joe Smith from Florida. We've got five now. We're still good. Let me add one more. Let's just change someone. Let's say a hero moves to Florida. Close it.
Sorry. For safety and sanitary reasons, only five Floridians allowed. Now the record's in there. And yes, I suppose if the database crashes, then that record is going to stay because this form didn't get to do its thing. So there's always issues around everything. But, that I think is a nice simple way to check and say, hey, you've got too many Floridians in here, you got to go. Sorry, Joe Smith. You've got to go. You're off the team. Now I can close it. And that's it. That's how you do it.
Are there other ways to handle this? Yeah. There's lots of ways. It's like, I always use the Lego analogy. There are a whole bunch of Legos. I'm showing you different ways to put pieces around. This is just another tool in your box, another event that you're going to learn how to use, and come up with all kinds of funny ways to do stuff.
If you like learning this stuff, like learning some VBA, I take it nice and slow from the beginning. I've got lots and lots of different lessons. If you like hanging out with me and learning, if you like my style, great, awesome. Come and join class. Lots of room. Plenty of seats. Check it out. There's a link.
That's it, folks. That's your TechHelp video for today. I'm going to get out of here. Go on. Beat it. Scram. Learn something. Live long and prosper. I'll see you next time.Quiz Q1. What is the main purpose of using the on-unload event in Microsoft Access as discussed in the video?
A. To validate all records before allowing the form to close
B. To update a single record before moving to another record
C. To automatically save all form data to the database
D. To generate detailed user reports
Q2. Why can't the standard validation rule or before update event always be used for this type of validation?
A. Because they only work for validating single fields or single records
B. Because they do not support string fields
C. Because they can only be triggered by button clicks
D. Because they automatically cancel the form close action
Q3. What function is used in the video to count the number of records matching specific criteria in a table?
A. DCount
B. DSUM
C. DLookup
D. DMax
Q4. In the example given, what is checked before allowing the form to close?
A. That there are no more than five customers from Florida
B. That all order numbers have been entered
C. That no two job codes are the same
D. That the total sales exceed a certain amount
Q5. What happens if the validation check fails when trying to close the form?
A. A message box is displayed to the user explaining the problem and the form does not close
B. All invalid records are automatically deleted
C. The form closes and logs out the user
D. The database automatically corrects the invalid records
Q6. Why does the instructor prefer to store the DCount result in a variable before checking it in an IF statement?
A. It helps reduce errors and makes the code easier to debug
B. It saves memory
C. It makes the code run faster
D. It allows the same value to be used across multiple forms automatically
Q7. What is a key difference between the on-close and on-unload events on a form?
A. On-unload allows cancelling the closure, while on-close does not
B. On-close is triggered before any user input
C. On-unload is only available in reports, not forms
D. Both events do exactly the same thing
Q8. If a database has multiple forms where records can be added or changed, what is the recommended approach for the validation code?
A. Place the validation code in a global module and call it from each form
B. Only place the code in one form and hope users do not use others
C. Duplicate the code in all relevant forms without sharing
D. Avoid validation altogether
Q9. Why is it important to include the state value (e.g., "Florida") inside double double quotes in the DCount criteria?
A. Because the criteria itself is a string within a string, requiring correct quotation
B. Because Access does not support single quotes
C. Because Florida is a reserved keyword
D. Because DCount does not work with string values otherwise
Q10. What is the main takeaway the instructor intends for viewers regarding Access events and validation?
A. Understanding and using the appropriate events and functions helps maintain business rules for data entry
B. It is best to avoid VBA if possible
C. Validation should always be handled by the database administrator, not end users
D. The only secure way to validate is through back-end SQL code
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 will show you how to use the on-unload event in Microsoft Access to validate data across multiple records and provide user-friendly error messages when someone tries to close a form.
I received a great question about this topic: if you have a form where employees enter multiple job codes for a project, and you want to make sure that at least five but no more than eight job codes are entered before allowing the user to close the form, how can you enforce that rule?
To make it easier to relate to, think of it this way: imagine a customer table with a child table for orders. In this scenario, the project is like the parent record, and the job codes are the child records. You might need to ensure that there are at least five and no more than eight child records related to the parent before the form can be closed.
While validating a single field is simple using a validation rule, and single-record checks can often be done using the before update event, enforcing rules that look at multiple records tied to the same parent requires a different approach. For this, I recommend using the on-unload event. This event fires just before the form closes, giving you a chance to check any conditions you want and cancel the close if something is wrong.
The before update event is useful for validating data within individual records just before saving them, while the on-unload event gives you a chance to examine the data across all records before the form actually closes. The distinction is important: on-unload happens before the form is finished closing, allowing you to stop the process if your validation fails, whereas something like on close occurs after, when it's too late to cancel.
When your validation involves a count of records, as in this example, the DCount function in VBA is the tool of choice. DCount lets you count how many records meet specific criteria in a table or query. If you are new to VBA, don't worry. I have a beginner video that walks through the basics, as well as tutorials on the before update event and how to use DCount. These are freely available on my website and YouTube channel.
To follow along, I use my free TechHelp template database, but the concepts apply to any Access database. As a demonstration, suppose you want to limit your database to no more than five customers from Florida. Before letting the user close the form, a check should confirm that this rule is met. If there are too many Florida customers, the form will not close and an error message will appear, instructing the user to delete the extra entries.
In the form's event properties, you can find both the on close and on-unload events. Although they're similar, the key difference is that on-unload gives you the ability to cancel the closing action, just like the before update event lets you cancel leaving a record if validation fails. On close, in contrast, marks the point when things are shutting down and it's too late to intervene.
The validation process involves counting the relevant records using DCount. For example, you count all customers in Florida and if the count exceeds your limit, display an appropriate message box to the user. After presenting the warning, you set the cancel argument to prevent closing the form.
A few lines of code are all it takes, and while you could check DCount's result directly in the if statement, I prefer assigning the count to a variable first. This makes the code easier to read and debug. It's a matter of coding style.
Bear in mind, if you have multiple forms where users can add or change records, you would ideally move the validation logic into a module for reuse, rather than duplicating code across forms. For the sake of this tutorial, I assume you are working with a single form.
It's worth noting a minor risk: if the database crashes or closes unexpectedly before the validation runs, a problematic record might remain. Every technique in Access has its trade-offs, but this approach offers a straightforward way to enforce multi-record rules at the form level.
Of course, there are alternative methods for handling such situations, but learning to use events like on-unload gives you more tools in your toolkit and helps you make your Access applications more robust and user-friendly.
If you're interested in exploring more VBA for Access at a comfortable pace, I encourage you to check out my other lessons. I strive to make learning both accessible and engaging.
For a full video tutorial with all the step-by-step instructions covered in this article, visit my website at the link below. Live long and prosper, my friends.Topic List Validating record count on Access form close
Using the Form OnUnload event for validation
Comparing OnClose and OnUnload form events
Using DCount to count records with criteria
Displaying a custom message box on validation failure
Canceling form close if validation fails
Setting record count limits for related child records
Handling string criteria in DCount function
Implementing logic with VBA variables in form events |
