Form Opening Events
By Richard Rost
8 months ago
On Open, Load, and Current Events in Microsoft Access In this Microsoft Access tutorial, you will learn about the pivotal form opening events: On Open, On Load, and On Current. We'll examine how to effectively use each event, their distinct purposes, and the timing of their execution. From canceling a form's opening with the On Open event to initializing data using the On Load event, and updating the UI dynamically with the On Current event, you'll grasp the nuances of handling these events. This tutorial is essential for developers wanting to fully understand form behaviors and interactions within Microsoft Access. Colin from London, UK (a Platinum Member) asks: I searched on YouTube for a video explaining the difference between On Open and On Load in Access, but one of yours didn't show up. You mentioned before that we should let you know if that happens, so I wanted to give you a heads-up. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesLinksRecommended CoursesNotesOn Open, On Load, On Current, Form Load, Form Open, Form Current
Keywords TechHelp Access, form opening events, on open vs on load, cancel form open, form load sequencing, data validation on open, active x controls load, record count on load, form UI updates, RGB color coding, sequence of events in forms, on current
Transcript
Today we're going to talk about the form opening events, the three most important events that run when you open a form. There's an open, unload, and on current. We're going to talk about what each of those do and when you want to use each of them and what the differences are. Today's question comes from Colin in London, UK, one of my platinum members. Colin says, I searched on YouTube for a video explaining the difference between on open and unload and access, but one of yours didn't show up. You mentioned before that we should let you know if that happens. So I wanted to give you a heads up. Yes, I appreciate that very much. Thank you, Colin. One of my goals is to make sure that if you search for anything access related, one of my videos shows up and if it doesn't let me know and I'll make one. So I'm making one. The funny thing is I have actually covered these different events in different videos of mine, but they might not have, on open isn't the theme of the video. On load isn't the theme, but I cover it. So if you've been following my channel for any period of time, you've probably seen these before, but maybe you didn't understand the intricacies between these two different events, these three different events actually. Let's talk about them.
All right, first up, this is a developer level video. What does that mean? Well, that means if you've never done any VBA programming before in access and you want to learn, go watch this video first. It'll teach you everything you need to know to get started in about 20 minutes. All right, so when a form opens in access, several events fire. There's lots of them. There's a bunch more. There's an activate and a whole lot of them. We're going to cover the three most important ones. There's an open, unload and on current. On open fires first, before the form is fully loaded, and this event can be canceled. All right, it's the only one of these you can actually cancel the event and say, no, I don't really want to do that. Exit out. Okay, and the form won't open. After that, unload events, fires after on open when the form is fully loaded and the data is ready to be displayed. Okay, and I mean all of the data, not just the first record. We'll talk about that in more detail in a second. And then on current fires last, and that's when the record is loaded and it actually fires every time you move to a new record, not just when the form loads. Okay, and I've got whole separate videos on the on current event.
Now, the on open event is best for checking things before the form fully loads. You can use it to check user permissions. If you've got user level passwords and stuff set up, you can check their permissions. Hey, does this person have access to use this form? If not, you can cancel it. Restricting access to a form based on business hours or other conditions. We'll see how this works in just a second. Validating prerequisites before the form is displayed. Hey, is this customer marked as an actual customer? Are they still in like the pre-customer phase, or they're not an actual purchasing customer yet? Okay. And you can cancel the form's opening by setting cancel equals true. Let's see an example. Let's do the one with the business hours. It's the simplest one I can think of.
All right, here I am in the TechHelp free template. This is a free database you can grab off my website if you want to. And in here, I got a customer form and I got an order form. Let's say for whatever silly reason your business has a rule that you can't open the order form outside of business hours, between nine to five. So how could we prevent that? Well, you could put the code in the button, but let's pretend for the sake of class that you can get to this form from many other places. So you don't want to have to code it in all those different buttons. We can put it in the forms on open events. We're going to design view, bring up the forms properties. Here's on load, but we want on open. Open goes first... that'll bring up a code builder. There you are. Resize this for you here. Okay, over there. I'm in the form open event. Notice form open has a parameter here called cancel. You'll see in a minute load does not have that. And here we can just check to see what the current hour is. So we'll say if hour of right now is less than nine or the hour of right now is greater than five. Then we give a message box. Come back during business hours. And this is the important part. Cancel equals true. Yes, I know it's technically an integer and true is a Boolean value, but it's just what everybody does. Cancel equals true. Okay, and then end if. Save it. Always throw in a debug compile once in a while. I'm going to close this. I'm going to close this and I'm going to reopen the order form and come back during business hours. Now it's currently three fifty nine PM. So I shouldn't have seen that and I'm going to leave this mistake in the video because I just made this mistake. You're going to make it too one of these days. And here's another thing that comes up too. But hit okay, I get an open form action was canceled. Why is that? Let's take a look at the debug first. If you have an open form command in a button and that command gets canceled if that on open event cancels the form opening, this line will generate an error. So if you know that's going to happen, make sure you handle it with an on error resume. Next, that means if this guy throws an error, which it will if that form open is canceled, it'll just ignore it and move on. Okay, but let's take a look at that other problem. What happened that's it's four o'clock now. So this shouldn't be coming up. Let's take a look at the code again. Let's go into my order form design view. Let's go back into the code and see if you could figure out what's going on here. Hour has to be less than nine, but greater than five. Anybody pause the video if you have to think about it. Well, the hour is a number from one to 24. So you got to use 24 hour time. So five o'clock is actually 17, 100 hours. Okay, I do this a lot and I whoops, someone's beaming into that was the temporal police from the 26th century. I just get yelled at for having a time mistake in one of my videos. All right, won't happen again. No, but seriously, I do this a lot. I sometimes don't think and I put in here, you know, just I'm thinking six, six PM. Okay, but now save it and let's try it again. It worked. It opened up. Okay, but again, if I want to fake that, I can come in here and now I know it's four o'clock. So if I put in here 14, let's say it's nine to two PM, and then I try to run it. And it says come back during business hours. Okay, so that's one thing. That's the benefit of form open. That's pretty much the only time I ever use the on open event is if I'm checking to make sure that the user can get into the form. Everything else I usually put in on load event, but as you're going to see as we go through here, most things work pretty much the same between on opening on load. All right, the only major difference is that the open event can be canceled. The load event cannot.
So let's continue on. All right, on open can be canceled. That's the big one. We're going to bold that. Right. Yes, this is PowerPoint. Okay, and I'm off my slide here. Let me fix that. There we go. All right, what's next on load event is next. Now the on load event is best for performing initialization that depends on the forms data and not just the first record, all of them. Okay, running code that modifies the form after all the records are loaded or ensuring the form is fully ready before interacting with active x controls. Now I personally almost never use active x controls. These are things like the web browser control or the progress part. Once in a while, I'll use them and I've got some other videos where I do use them, but I use them extremely sparingly. All right, but the on load event happens after on open. On open basically says, all right, you're good to go. You can continue loading the form now. Now we're onto the on load event. In fact, let's do one thing. I want to show you the timing sequence here. Here's how you can see how these run. Let's go into the customer form here. I'm going to go to the forms properties again. Let's go to the on load event and I'm just going to put in here message box load so we can see how these open. Okay, now you want to go to the on open event. You don't got to go back here. Just come up to this box right there. See how this is on form. Come over here and drop this down and pick on open. Where are you? Open right there. Now we're in the open event. So message box open. Open. Open. There we go. And one more, let's do the current event. That's the last one we're going to talk about current. And here we are message box current. All right, save that. Let's close it and now we'll see the order that they open it. Ready? Click. All right, the open event. Notice the form isn't displayed yet. Loader event is next. And then current event goes. See? And then as I move from record to record, the current event will fire again. See? Okay? All right. So that's the timing of those three things. Let's get rid of that. Do it. Do it. And do it. All right. I just want to show you the timing. Now you may see some other videos out there. I know I did when I was doing research for this that says that you can't access any of the forms data in the on open event. You have to get the forms data in the unload of it. That's just not true. There are a lot of wrong sources out there. Trust me, I spent about an hour researching this earlier. You can access some of the forms data in the on open event like the current record. The first record, the properties are available, things like the properties, like the caption and stuff. You can get those, the forms width. You can get that in both of these events. But what you can't get is the entire set of data. For example, back in our customer form here, well, let's go and do this. Let's one more time. This is a load event. I'm going to say message box load. This time I'm going to say and first name. Show me the first name record. I'll do the same thing in the on open event. I said first name record, first name field, message box open, first name. Let's close and reopen the form. See, open, it has a Richard. That's the name of the first person in the records. Richard, both of those have access to it. Both events, the open and load event, they have access to some of the data, specifically the first record. Load happens after all of the records have loaded into the form. If you've got a form with multiple records in it, and you can do things like get a record count. Let me show you that one. If I say show me me.recordsetclone.recordcount, and yes, that beep happens. I don't know why. I'm going to talk to the access guys. Sammy, put that on the list. Every time you do that and you hit the dot there, that comes up. This is how you get the count of how many records are in the record set clone. In other words, how many records are loaded into this form, basically. We'll do the same thing down here in the open event. Remember, open goes first before all the records are loaded. Ready? Close it. Open thinks there's one record. Load sees all 33 of them. That is, of course, the correct answer. The open event gets some of the information. The load event gets all of it. All of the records are available. It knows how many records have loaded and all of that stuff. If you have stuff that you want to do that is dependent upon knowing how many records are in the underlying data set, then you want to use the load event. That's really the only thing that I could think of that requires the load event. Most things you want to do, you can do in both open and load. You want to hide fields. You can do it in both. You want to set the caption, set properties. You can do it in both. Really the load event is only necessary when you have to make sure that the entire set of data has loaded in the form. I pretty much use both interchangeably all the time, unless again, that's a specific instance. I use opens specifically to cancel stuff. That's mostly for doing validations and things.
Finally, we got the on current event. Like I said, I got a whole separate video on the on current event. I'll put a link to it down below in the links section. You can go watch that one as you want. This is for updating the form. Each time a new record is selected, dynamically changing control properties based on the current records data, highlighting in active records or performing per-record validation. For example, I'll just give you a quick one since I've got a whole separate video on this. Let's say it is active here. I got some customers that are active and some that are not. Let's say if they're not active, I'll make the background gray. You can look at each record and see what you want to do. Let's go into the events we got in there first. Let's go to the on current. Here's the on current event. We can get rid of load and open. Whoops, not too much. Get rid of that stuff there. Okay. And in the form current event, let's say if is active, then in other words, if is active equals true, if that box is checked, me.detail.backcolor. Because the form itself doesn't have a background color, but all of the sections do. You can refer to it by me.detail or me.section0. There's a whole bunch of different ways you can refer to that. Again, I got different videos on this stuff. We'll say equals RGB. That's red, green and blue. Let's go gray. So this will be like a light gray, like 100, 100, 100. All right. Otherwise, me.detail.backcolor equals. Let's go with the light blue RGB. Let's go 100, 100, 255. And whenever I do colors like this, I like to put next to it. What that means? That's a gray. And that's a blue. And I always get these color codes wrong. So let's see what we got. Ready? Let's close it. Open it. Okay. That's an active one. So that's gray. I think I got them backwards. Yeah, I got them backwards. I got them backwards. But you see how it's working. As you go from record to record. All right. Let's fix those colors. All right.If it is active, we're just going to flip these. We'll do this. 255 and 100. And that's blue. And that's gray. And if you want it lighter, just increase these numbers. They go from one to 255. So we can go, let's go 200, 200, 200. And we'll make this not so dark of a blue. There we go. This should look better. Ready? Oh, that's nice. That's a nice light blue. And a nice light gray. Let's see. Based on the active.
So that's the on current event. I use on current constantly. I'm always using the on current event. But you don't want to put stuff in the on current event that can be handled in the on open or on load events. Because then you're just running code that you don't need to run. If it's something that only has to run once, put it in the on open or on load events. If you need to check something from record to record, that's when it goes in the on current event.
Quick summary: on open is before the data is fully loaded, you get some data, but not all of it. Checking conditions before opening or canceling an open based on whatever reason. On load event is after the form's data is fully loaded. That's how I like to think of it. All right, you're fully loaded. And I don't mean you had too much to drink. And you can run code that depends on the fully loaded data.
On current, every time the record changes, updating the UI based on record data, changing the color like we just did. That kind of stuff. If you want to learn more about the sequence of events and all these different events and when they run and when they fire on what they do, I cover it in my Access Advanced Level One class. Why advanced? Well, I've got these six classes called the advanced classes. They're sandwiched between the expert lessons and the developer lessons. The advanced lessons cover mostly macros. But the macros run in the same events that the actual VBA events run in. So I teach the sequence of events and what fires when in the advanced lessons.
That's why when developers say, hey, can I skip the macro classes? I usually say no. Because there's some good stuff in these lessons. There are only six of them. And sometimes macros have their place. There are a few things you can only do with macros. And I talk about those in the advanced classes. So if you want to learn more, check it out.
So there you go. There's the form opening events. And thank you again to Colin for letting me know that there's a gap in knowledge when it comes to my videos out there. If you want to see more videos like this one, post a comment down below. Guess what? There are other events like on unload and on close when you close forms. Those have their own special things you can do. Like you can validate to make sure the user typed in the right data before they're allowed to close a form. So if you want to see a video on that stuff, post a note down below. I'll think about it. Maybe I'll make it. I'm just kidding. I'll make one for you if I get enough people to post down below.
That's going to do it, folks. That is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Form opening events overview
On Open event description
On Open event usage
Canceling form opening with On Open
Restricting access with On Open
Validate prerequisites with On Open
Form On Load event description
On Load usage for initializing form data
Difference between On Open and On Load
On Load event code example
On Current event description
Dynamic UI changes with On Current
Example of On Current usage
Accessing form data in On Open
Accessing form data in On Load
Event firing sequence demonstration
COMMERCIAL:
In today's video, we're discussing form opening events in Access - specifically the three key events: open, unload, and on current. You'll learn how each event operates, when to use them, and what makes them different. We'll explore how the on open event can be used to check user permissions, while the on load event is ideal for tasks requiring fully loaded data. We'll also cover how the on current event can dynamically update your form as you navigate records. If you're an Access developer, this video is packed with insights for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. Which form opening event is the only one that can be canceled in Microsoft Access?
A. On Open
B. On Load
C. On Current
D. On Activate
Q2. What is the primary use of the On Open event?
A. Modifying the form after all records are loaded
B. Initializing ActiveX controls
C. Checking conditions before opening or canceling the form open
D. Dynamically changing control properties based on record data
Q3. Which event is best for performing initialization operations that depend on all the form's data, not just the first record?
A. On Open
B. On Load
C. On Current
D. On Unload
Q4. During which event can the number of records in the recordset be accurately determined?
A. On Open
B. On Activate
C. On Current
D. On Load
Q5. What is a typical use case for the On Current event?
A. Performing initial checks before form opens
B. Running code that modifies the form after all records are loaded
C. Updating the user interface based on the current record data
D. Initializing ActiveX controls
Q6. What type of condition can cancel the opening of a form in the On Open event example discussed in the video?
A. Incorrect data entered
B. User not having the appropriate permissions
C. Form being opened outside of business hours
D. All of the above
Q7. For what reason is the On Current event used more frequently compared to On Open and On Load?
A. To cancel the opening of the form
B. To update the form whenever a new record is selected
C. To initialize controls once the form is fully loaded
D. To open the form only for select users
Q8. Which event should you use if you want to write code that runs each time the form moves to a new record?
A. On Close
B. On Open
C. On Current
D. On Load
Answers: 1-A; 2-C; 3-B; 4-D; 5-C; 6-D; 7-B; 8-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 focuses on the important form opening events in Microsoft Access: On Open, On Load, and On Current. These events are key in understanding how forms operate from the moment they begin to load until the user interacts with them. We'll explore the purpose of each event, when to use them, and the distinctions between them.
Today's topic is prompted by a question from one of my platinum members, Colin from London. He was unable to find a video on the differences between On Open and Unload events in Access on YouTube and reached out for assistance. I am creating this tutorial in response, as it's crucial that my content appears when you search for Access-related inquiries. If you ever notice a gap in coverage, please inform me, and I'll be sure to address it.
This session is intended for those at the developer level, so if you're new to VBA programming in Access, I suggest watching the introductory video available on my site. Now, let's dive into the core events associated with opening a form: On Open, On Load, and On Current.
When you open a form in Access, multiple events trigger in a sequence. While there are many, including Activate, we'll concentrate on the three primary ones. First, the On Open event activates before the form is fully loaded and can be canceled, making it unique in this regard. If certain conditions aren't met, you can use this event to prevent the form from opening.
Next, the On Load event fires after all the data is prepared, meaning the entire dataset, not just the first record. The On Current event occurs every time you navigate to a new record, not only when the form initially loads. For more detailed information on the On Current event, I have separate video content on that.
The On Open event is ideal for pre-loading checks, such as verifying user permissions or validating prerequisites. For instance, you can restrict form access to specified business hours or based on user status. I've provided an example where the order form cannot be opened outside of business hours, demonstrating how code can be utilized in the On Open event to check the current time and cancel the form if necessary conditions aren't met.
Now, moving on to the On Load event, this is where you execute tasks that require the form's complete data. It is beneficial for making changes that rely on all records being loaded, such as modifying display elements or working with ActiveX controls. Although I seldom use ActiveX controls in Access, this event remains critical for tasks that demand fully loaded data.
Lastly, the On Current event updates the form whenever a new record is selected. This is useful for dynamically modifying properties or performing record-specific validations. I've shown a quick example where record colors are updated based on their active status. This showcases the utility of the On Current event in customizing record appearance.
In summary, use the On Open event for initial validations and cancellation, the On Load event for operations needing full data, and the On Current event for updates per record change. If you're interested in learning more about the sequence of these events and their execution, my Access Advanced Level One class delves into this topic comprehensively.
To wrap up, I appreciate Colin highlighting the need for this content. If you have requests or notice topics not covered, let me know. I'm considering creating further videos on events like On Unload and On Close, which also have unique applications. Your feedback is welcome, and I'll consider new content based on interest shown.
For a complete video tutorial with step-by-step instructions on the topics discussed, visit my website at the link below. Live long and prosper, my friends.
Topic List
Form opening events overview
On Open event description
On Open event usage
Canceling form opening with On Open
Restricting access with On Open
Validate prerequisites with On Open
Form On Load event description
On Load usage for initializing form data
Difference between On Open and On Load
On Load event code example
On Current event description
Dynamic UI changes with On Current
Example of On Current usage
Accessing form data in On Open
Accessing form data in On Load
Event firing sequence demonstration
|
