Big Button Form 4
By Richard Rost
2 years ago
Dynamic Big Buttons Forms for Data Entry, Part 4 In this Microsoft Access tutorial, we will learn how to handle scenarios with more than 18 buttons in data entry forms, set up button event handlers using a constant for maximum buttons, check record counts, and utilize the tag property of controls to store IDs. This is part 4. MembersThere is no extended cut, but here is the database 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 CoursesUp Next
Keywords TechHelp Access, Dynamic Forms, Big Buttons, Data Entry, Microsoft Access, VBA code, record count, rs.moveLast, dynamic button creation, tag property, Access Developer 22, event handler functions, button clicked function, me.activecontrol.tag, managing multiple buttons
Intro In this video, I continue building the Big Button Form in Microsoft Access by setting a maximum button limit using a constant, checking the record count before looping, and handling cases where there are more records than available buttons. I show how to use the tag property to store each button's ID, create a single function to manage button click events, and assign it to all buttons for easier maintenance. You'll see how to handle form closure gracefully and make the form more reliable as you add more options. This is part 4.Transcript This is part four in my big button form series. If you haven't watched parts one through three yet, please go watch those first and then come on back.
All right, so things are working pretty well. We got our customer form. We can select the vehicle. It pops this up. We got the captions all set. The buttons look good, but now the buttons don't do anything. One thing I want to look at, though, before we actually make these buttons work, is I want to make sure we don't run into a situation where we have more than 18 options. Right now, this form can only handle 18 options. If you need 30, you have to make 30 buttons like a big form - whatever you gotta do. I'm only set up for 18, and I want to let the VBA code know this. Otherwise, if it tries to write stuff to button 14, it ain't gonna work; we're gonna have a problem.
So, let's go back into our code. And what I'm gonna do is, I'm going to say up here, I'm gonna use a constant for it because it's a value that never changes. Constant maxButtons equals 18, just like that. Now, we can handle this a couple of ways. We could say right here, you know, x is less than maxButtons. That's kind of a cheesy way to do it. Let me teach you the right way to do it. What we're going to do is we're actually going to look at the number of records in the record set before we even start the loop. And if there's too many of them in there, then we'll just tell the user to tell your developer to add more buttons, pay him more money to update your database. That's something everybody should be doing. Show me the money.
So what we're going to do is right here we're going to check the number of records. We have to first move to the last record so we can get an accurate record count. There's a property of a record set called the record count, but in order for it to be accurate you have to first go to the end and then come on back to the beginning. I know it's a pain, but that's just how it works. So move to the last record. We can do that by saying rs.moveLast. Very simple.
Now we can get the record count. We can say if rs.recordCount is greater than maxButtons, then however you want to handle it - message box, error. You have more records than buttons. Pay your developer to add more buttons to the form. And literally, with the way we're building this, that's all you have to do - just drop more buttons on the form and make sure they're named correctly. That's it. The code is versatile. It will handle as many buttons as you put in here. Oh, and of course, update your max buttons counter in there.
Okay. Now, at this point, we can say cancel equals true. I'm going to change this a little later on, but this will work for now. And then I'm going to say else, else do some stuff. Because we've already opened up the record set, we want to make sure we close it down gracefully. So I'm going to say the else is going to include all of this stuff. So let's indent this. Okay.
Else, you know, OK to loop. And here you can put, I'm going to start getting better at my code. If it's only a couple of lines, I usually don't bother with too much commenting, but too many records need more buttons. And it's just easier to see that green text in here too, isn't it?
All right? And if there are, it'll message box you, cancel is true, so it'll cancel the open event. And then RS close, it closes up that; it'll set these things here but it's not a big deal because it's gonna close anyways.
And we should be able to test it now. Debug compile. Let's try it. Let's just go in the make table and add some junk. We got 18 buttons, right? Oh, that's right, we indexed it.
All right, he's got to be unique. Bunch of unique buttons in here. Alright, we got 18. Alright. Getting there, getting there. Let's make a 19th record. Okay, oh, not see, can't even do this right. Alright, I heard you. There.
Okay, let's try it. Click the button, and erp, you got too many records. Pay your developer to add more. And then, yeah, see open form is canceled. I think this is why I changed it later. Because that's going to throw an error on this guy. So what we're going to do is instead of utilizing cancel, what I'm going to do right here is just close the form. And what I'm going to do is I'm going to start it off by saying, we'll put it right here, force exit equals false. And then instead of this cancel equals true, we're going to say force exit equals true there. And then down here at the bottom, after everything else is processed, we're going to do command close acform me.name is the name of the form and then acsave yes.
I've explained why I use acsave yes here in a lot of different videos, but basically, if you're doing design work and you run it without saving it, closing it, and opening it again, you're going to lose your design changes too. So that's really just for you.
Oh, and I also noticed one more thing I forgot just a minute ago and it's right here. We went to the last record. We're checking for too many, but if we're good we have to do something here. We have to say rs.move first. We have to go back to the beginning because think of it like a little pointer. You go to the last record, and if you say, okay, loop to the end of the record set, well you're already at the end of the record set. So you got to make sure you go rs.move first in there.
Okay, so instead of trying to cancel this event, we're just gonna close the form. Makes sense. Save it, come back out here, close it, close it. Click the button, and we're good. See, so we used a little force exit there.
If your code relies on the form opening and the cancel event is thrown, then it gives an error to the guy who called it. So it's a pain. It's easier to handle it this way. All right, so we got it so we can click this. All right, it'll open up the form. Let's get rid of all those bogus records that we just put in there. All right, get rid of this stuff.
Unless there's a car that you know of named Das. Das Audi? Yeah? No? All right, so we got this open here now we've assigned each of the buttons a caption we also need to know the value of the idea of that record right out he is... what is the point this is a you know I'm saying right before these guys has an id so we need to know what that id is where we have put it I'm a store that button somehow where can I put that? Well there's a nifty property of most controls. You go to the alt tab, come all the way to the bottom. There's a property called tag. What is tag? Whatever you want it to be. You can put whatever you want in there. That's just for you. It's to store some extra information about this particular button or text box or label or whatever. So I'm going to use that to store the ID of the button. I'm going to put it in the tag property. Then when the button is clicked on I can read that value and send it back to the calling form.
Alright, I don't have a TechHelp video about the tag property. I'm probably going to make one soon. It's on my list. I do cover it in Access Developer 22. If you want to learn more about it, I'll put a link down below. So let's modify our loop. Alright, right in here. We're setting a caption. Let's also set the ID.
Alright, we'll put that in the tag property. So we're going to come in here, we're going to say me.controls.tag equals rsid. See why I aliased those as id and button caption earlier? Doesn't matter to me what the id is. Is it a make id, model id, customer id, whatever. Doesn't matter. It's just id as far as this form is concerned.
OK. Now, let's quickly verify that that worked, okay? And we're going to do that by using a function that we'll put in the button. Now, you can use a function in any of these buttons in the events here. I call them event handler functions. I don't know what their proper name is. But basically, you can make a function and you can put it in the property for the event and you don't have to make a separate build event for each button. Right click, build event, right click, build event. You don't have to do that 18 times. You just do it once for all the buttons.
Okay? So back in my code, I'm going to come up top here. I'm going to create a function. It has to be a function. You can't use a sub private function because only these buttons are going to call it. We're going to call it button clicked. And then in here we're going to message box the tag of the active button, the active control. So it's me.activecontrol.tag. So me.activecontrol will be whatever button currently is the one in focus, the one the user clicked on, whatever its tag property is, I want you to message box that.
Okay, now we just have to set this as the on click event handler for each of those buttons. So it calls this function when the button is clicked. So come back out here. Actually, let me copy that to my clipboard first. Come over here, copy. Come over here. We're going to select all of the buttons. This is why this is less powerful - you do it all of the once, murder the on click right multiple selection of these buttons like that right here it's gonna be equals that function name equals button click so all of these buttons now will call that function when they click.
Dot it tab now save it close it select the vehicle click out e there's a number five. That's Audi's ID, because we stored it in the tag property. Chevy is two. Ford is one. See? Now we know what button was clicked on. We're just message boxing it for now, but in the next lesson, we're going to be able to figure out how to return that value to whoever called it. And we'll talk about that in tomorrow's video.
Which would be what part 5 we're up to? Yeah, so you know the drill tune in tomorrow same bat time, same bat channel. If you're a member, you can watch it right now. I'm gonna keep recording; I'm on a roll, folks. But that's gonna do it for part 4. Folks, there's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check them out at accessexperts.com.
TOPICS:
Constant declaration for button limit
Checking record count before loop
Handling too many records using rs.moveLast
Using rs.recordCount to compare with maxButtons
Displaying error message for exceeding button limit
Closing record set gracefully in case of error
Using rs.moveFirst to reset record set pointer
Setting force exit to handle form closure
Using acsave yes to save design changes
Utilizing tag property to store additional info
Setting tag property for button IDs
Creating a function to handle button click events
Assigning on click event handler for multiple buttons
Displaying button ID using message box in event handler
COMMERCIAL:
In today's video, I will guide you through Part 4 of our Big Button Form series. We'll refine our form with customer and vehicle selections—ensuring buttons display correctly but also function as intended. We'll first set a maximum button count to avoid errors, adjusting our VBA code to handle forms with numerous options. Then, we will accurately count records and navigate efficiently within the record set. I'll show you how to use the 'tag' property to store additional info for buttons and handle button clicks with a single function. 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. Why does the form need to check the number of records before proceeding?
A. To ensure the record count is accurate
B. To prevent errors if there are too many records for the buttons available
C. To improve the speed of the form
D. To ensure the data is saved correctly
Q2. What is the purpose of defining a constant like `maxButtons` in the code?
A. To enable easy changes to the maximum number of buttons
B. To improve the readability of the code
C. To store user inputs
D. To handle database connections
Q3. What is the correct way to get an accurate record count from the record set according to the video?
A. Using rs.moveFirst immediately
B. Using rs.recordCount without any other commands
C. Using rs.moveLast first, then checking rs.recordCount
D. Using rs.recordCount after closing the record set
Q4. What happens in the code if the number of records exceeds the `maxButtons`?
A. The form continues to load and truncates extra records
B. The form will display an error message and then close
C. The form automatically adds more buttons to handle extra records
D. The form deletes extra records to fit the maximum button count
Q5. Where should the records' IDs be stored for each button in the form?
A. In the button's Name property
B. In the button's Tag property
C. In the button's Caption property
D. In a hidden text box
Q6. Why does the instructor suggest using a single event handler function for multiple buttons?
A. It reduces the amount of code needed for multiple buttons
B. It increases the code's execution speed
C. It prevents errors from multiple event handlers
D. It ensures that each button performs different actions
Q7. What does the function `button clicked` do when a button is clicked?
A. It changes the color of the button
B. It saves the form
C. It displays the Tag property of the active control
D. It deletes the record associated with the button
Q8. Why is `me.activecontrol.tag` used in the `button clicked` function?
A. To get the value of the clicked button's Name property
B. To get the ID stored in the clicked button's Tag property
C. To reset the button's Tag property
D. To read input from the user
Answers: 1-B; 2-A; 3-C; 4-B; 5-B; 6-A; 7-C; 8-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 continues our journey with the Big Button Form series, focusing on part four. If you have not completed parts one through three, I recommend you review those before proceeding with this lesson.
At this stage, our customer form displays vehicle options using dynamically captioned buttons, but the buttons themselves do not perform any actions yet. Before we move on to adding that functionality, it is important to address a potential issue: the current setup only supports up to 18 button options. If your record set grows beyond 18 entries, the form will not function correctly. The solution involves clearly defining this maximum and informing both the VBA code and the users about this limit.
To ensure consistency, I define a constant in my code called maxButtons with a value of 18. This constant reflects the maximum number of buttons available on the form. Although you could simply check if the current index is under this limit within your loop, I want to show you a better approach. Instead, I count the total number of records before the loop even begins. If there are more records than available buttons, the program alerts the user. This way, users know they need to update the form and add more buttons, which involves simply putting more buttons on the form and updating the constant accordingly.
To accurately count the records within a record set, you must first move to the last record. This is necessary because the recordCount property only returns a reliable value after you have navigated to the end. Once you're at the end, you compare the record count to maxButtons. If there are too many records, the code should display an error message and close the form gracefully. It is essential to close the record set appropriately here to avoid issues with lingering open objects.
At this point, you may consider using a cancel event to stop the form from opening, but that can lead to errors elsewhere in your code if another process expects the form to open normally. Instead, I introduce a force exit variable. If the record count exceeds the maximum, set force exit to true and then close the form programmatically after all necessary checks, making sure to use acsave yes. This ensures that design changes and any edits made are preserved when the form closes, which is especially important if you are developing the form's layout.
After addressing the case where too many records exist, remember to reset the record set pointer for the main loop by moving back to the first record. If you do not, and your code tries to loop from the current position, it finds itself already at the end of the record set.
Once these structural details are in place, I turn to the next challenge: when a user clicks a button, the form needs to know which record that button represents. Each record in your data source has an associated ID, and we need to store this ID with its corresponding button. Fortunately, each control has a tag property that you can use to store any value you wish. I use this field to store the relevant record's ID for each button. When a button is clicked, its tag property reveals which record it is tied to.
To display this ID or perform actions based on it when a button is clicked, I create a function that handles the click event. Instead of generating a separate event procedure for each button, which is cumbersome and inefficient, I write a single function and assign it as the on click event handler for all the buttons. This function reads the tag property from the active control (i.e., the button that was clicked) and displays it in a message box. This approach is not only concise but also easy to manage and modify later.
Finally, I demonstrate how to assign this function as the click handler for all your command buttons in one action. This unifies the behavior of the buttons and ensures that your logic remains tidy and maintainable.
With everything in place, you're now able to limit your form to a safe number of buttons, gracefully handle data overflows, store key data in each button's tag property, and respond to button clicks with a single handler routine. In the next part of this series, we will explore how to return the value from a clicked button to the calling form.
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 Constant for maximum buttons on form
Checking record count before populating buttons
Using rs.moveLast to get accurate record count
Displaying message if records exceed button limit
Closing record set gracefully on error
Resetting record set pointer with rs.moveFirst
Implementing force exit logic to close form
Using acsave yes when closing form
Utilizing tag property to store record ID in button
Setting button ID in tag property during loop
Creating shared function for button click events
Assigning one event handler to multiple buttons
Displaying button ID from tag in message box |
