|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Event Handler By Richard Rost 
4 years ago
Single Event Handler Function for Multiple Controls In this video, I'll show you how to quickly assign a single event handler function to multiple controls in your Microsoft Access forms. Original Video
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, event handler function, event procedure, function
IntroIn this video, you'll learn how to use a single event handler function for multiple controls in a Microsoft Access form. I'll show you how to simplify your form code by assigning one function to several checkboxes instead of creating individual event procedures for each one. This method makes maintaining and updating your Access forms much easier and can save you a lot of time when working with similar control events.TranscriptWelcome to another Fast Tips video brought to you by accesslearningzone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to use a single event handler function for multiple controls in a Microsoft Access form.In my other TechHelp video, Custom Listbox Columns, I show you how to create a list box that will display different columns based on which checkboxes you select. Here's the database. I've got first name and email selected. If I add last name, it pops up over here, phone number, it pops up over here. It displays each one of these. I'll put a link to this video down below in the links section in the description below the video. Go watch that. In that video, I say you have to put an event in each one of these boxes. So when you check on that box, the after update event fires, or you can use the on-click event, either one. What that after update event does is it runs this guy called Requery List. It's the same thing, but you've got to put it in each of those boxes. Requery List then comes up here and runs this guy. I absolutely love it when one of my students calls me out on something. Long-time member Akiim brought it to my attention that you can use a single function and assign it to all those check boxes at the same time without having to add them individually. And of course, yes, he's absolutely correct. I'll show you how to do it in just a second. Number one, I do try to not add too much new stuff to each new video. I don't want to blow people's brains out. But number two, I haven't used this technique myself in years. So it completely skipped my mind as I was preparing this video. As soon as he mentioned it to me, I was like, duh. Yeah, of course you can do this. Let me show you what I'm talking about. First things first, let's come in here and remove all these after update events that we don't need. So show customer, show email, first, active, last name. Get rid of all those. Show phone, show state, get rid of all those. Now all those check boxes are not bound to any event. We still need the one in the form load though. Now come back out here and you'll see the events are all gone from over here. After that, the events are all gone. Now in your code, this guy is Private Sub RequeryList. A sub just does something, a function returns a value. If you want this to work, we have to turn the RequeryList into a function. Just change that word to function. It doesn't have to actually return a value, but it's got to be a function. Now come back out here to the database. We're going to select all of those check boxes. In either the after update or the on-click event, it doesn't matter, we're going to put =RequeryList(). That's the name of my function. That's one quick way to assign that to all of these. You can't put event handlers in here like you can't use the event procedure because that has to be unique VB code for each one. But if you can put it in a single function and all of those check boxes just run that function, then it'll work. This is one of those tips that I haven't used in years myself. Let's save this, close it, open it back up again. Now you'll see as I click, that function still runs because each one of those boxes is basically calling that function. As you'll see, that's much quicker than going through and assigning an after update event to each check box. Akiim, once again, thank you very much for bringing this to my attention. You're one of those members that hasn't signed up on my website. Go to my website and sign up your account because I tried to look you up. I see you in my YouTube member list, but you're not on my website yet. There's a page right there. It's my YouTube Find User page. Just come over here. There's a little form to fill out. It will sync up your YouTube account with an account on my website. There's your fast tip for today. I hope you guys learned something. I know it brought back something to my brain that I forgot a while ago. A lot of people say, I know you've forgotten more than I've ever learned. I hope you learned something and we'll see you next time. Thanks for watching. How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for access too. I also teach Word, Excel, Visual Basic, and lots more. You can now become a Diamond sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you are a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website. But don't worry, these free TechHelp videos are going to keep coming as long as you keep watching them. I'll keep making more and they'll always be free. QuizQ1. What was the main concept demonstrated in this video regarding Microsoft Access forms?A. How to use a single event handler function for multiple controls B. How to create a list box with custom formatting C. How to secure an Access database D. How to link Access to Excel Q2. In the original method presented in the Custom Listbox Columns video, what was required for each checkbox? A. A unique color B. An event handler for each individual checkbox C. A separate database table D. A macro for the form Q3. What is the main benefit of using a single function for multiple checkboxes in Access, as shown in this video? A. It reduces the need to create a separate procedure for each checkbox B. It makes the form more visually appealing C. It automatically sorts the data D. It encrypts the data Q4. Which event can you use to call the function from each checkbox? A. Only the On-Load event B. Only the On-Click event C. Either After Update or On-Click event D. Only the Change event Q5. Why did Richard convert the subroutine to a function in the code? A. Because functions are faster than subroutines B. Because only functions can be called directly from control events in the property sheet C. Because subroutines can't access form controls D. Because subroutines can only be used with labels Q6. What do all the checkboxes do after being assigned =RequeryList() in the event property? A. Each runs a separate event procedure B. Each calls the same function, updating the listbox C. Each clears the form D. Each disables the listbox Q7. What was the issue with assigning individual event procedures to each checkbox? A. It increased the efficiency of the database B. It required a lot of repetitive code C. It secured the database D. It made the checkboxes read-only Q8. How do you become a member to access additional perks on accesslearningzone.com? A. By subscribing to the newsletter B. By clicking the Join button below the video and selecting a membership level C. By sending an email to support D. By downloading a form from the website Q9. What benefit do Gold members receive? A. Access only to beginner classes B. Access to download all sample databases and code vault C. Access to only YouTube videos D. Lower priority for TechHelp questions Q10. If you want to access all full beginner courses for every subject, which membership level do you need? A. Silver B. Gold C. Platinum D. Diamond Answers: 1-A; 2-B; 3-A; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-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. SummaryToday's video from Access Learning Zone focuses on how to handle multiple event triggers with a single function in a Microsoft Access form. I'm Richard Rost, and today I'll be explaining a more efficient way to manage event handlers for multiple controls, specifically checkboxes, in your Access forms.Previously, in my TechHelp video about Custom Listbox Columns, I showed how to build a list box where different columns appear depending on which checkboxes are selected. For example, choosing "first name" and "email" displays those columns. Adding "last name" or "phone number" dynamically updates the list box to include those, too. If you haven't seen that lesson yet, I recommend watching it for a full understanding of this setup. In that earlier tutorial, I mentioned that you needed to add the same event handler code to the After Update event (or the On Click event) of each checkbox. This event would trigger a routine called RequeryList, which updates the list box. You'd need to repeat this process for each checkbox, so that whenever one is checked or unchecked, the form updates as expected. One of my long-time members, Akiim, reminded me of a more streamlined approach. Rather than handling each checkbox separately, you can use one function and connect it to all of the checkboxes at the same time, removing the need for repetitive event code. I appreciate that feedback, and he's absolutely right. Let me walk you through how to implement this. First, clear out the After Update events from all your checkboxes, such as show customer, show email, first, active, last name, show phone, and state. Leave any form-level events you still need, such as Form Load. Once these events are cleared, your checkboxes will be event-free and ready for the next step. Now, in your VBA code, the RequeryList routine is a Sub, which means it performs an action but doesn't return a value. To use this for all your controls, change it to a Function by adjusting the declaration appropriately. A function can be called directly from the property sheet for your controls, even if it doesn't return anything. Once that's set up, go back to your form and select all the relevant checkboxes. For each one, set the After Update (or On Click) event to call your new function, for example by entering =RequeryList(). This approach centralizes your update logic, so every checkbox triggers the same function, rather than requiring individual event procedures for each one. This tip is one I had not used for quite some time myself, and I appreciated the reminder. It really does make managing forms with multiple controls much easier and cleaner. Everything works just as before, but setup is faster since you aren't repeating the same steps over and over. Thanks again to Akiim for the helpful suggestion. If you're following my videos, be sure to sign up for an account on my website to get the most out of your membership and resources. For those interested in joining as a member, simply use the join button on the video page. Memberships come in several levels, each offering different perks. Silver members and above receive extended cut TechHelp videos, a free beginner class every month, and more. Gold members can download all the sample databases from TechHelp tutorials, access my code vault of functions, and receive increased priority when submitting questions, along with a free expert class after finishing the beginner lessons. Platinum members get even higher priority for questions, access to all beginner-level courses for each subject I cover, and a free developer class every month after completing expert classes. If you'd like special recognition, you can become a Diamond sponsor and have your name or company listed as a sponsor, getting a mention in each video and a link on my website. Regardless of your membership level, these TechHelp videos will remain free to everyone. 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 ListUsing a single event handler function for multiple controls in Access formsRemoving individual After Update events from multiple checkboxes Converting a VBA Sub to a Function for use as an event handler Assigning the same function to multiple checkbox events at once Difference between event procedures and function-based event handlers in Access Demonstration of updating listbox columns by requerying via function Efficiency benefits of centralized event handling for form controls |
||||
|
| |||
| Keywords: FastTips Access event handler function, event procedure, function, event property function, event function, function property, property function PermaLink Event Handler Function in Microsoft Access |
