Big Button Form 5
By Richard Rost
16 months ago
Dynamic Big Buttons Forms for Data Entry, Part 5 In this Microsoft Access tutorial, I will show you how to create dynamic forms with big buttons for data entry, utilizing temp vars for returning item IDs, cascading combo boxes for selecting makes and models, and handling potential issues like cancellations and missing records. This is part 5. 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, Dynamic Forms, Big Buttons, Data Entry, Microsoft Access Forms, Temp Vars, Access VBA Tutorial, Button Form Series, Return Value Handling, Dynamic Button Captions, Opening Forms Dynamically, SQL Statement Modification, Record Source Adjustment, Debugging Access Forms, Cascading Combo Boxes, Vehicle Data Entry Access, Coding in VBA
Transcript
This is part 5 of my Big Button Form Series where we're making dynamic buttons on forms to do all kinds of cool stuff. If you haven't watched parts 1 through 4 yet, go watch those first, then come on back.
Okay folks, so we're to the point now where we can open this up, and we've got the buttons all set with the captions. We can click on a button and we know what button was clicked on now; what the ID of that item is. Now we just have to return that ID to whoever called it, and we'll do that with, guess, Adam, what are we going to use? Another temp var. We'll set the return value in a temp var. So let's go back to our code window. We're going to go to the code for the big button borrow form, and instead of message boxing this, we're gonna set it in a temp var. So temp vars let's call it button return ID equals now it's an ID, so I want a long integer. Technically, a tag is a string value; shouldn't matter too much, but I'm gonna typecast that into a long CLNG, so now it's returning an actual long integer.
And once that's done, now we can close the form. We're just picking one thing. Yeah, I'm thinking maybe as a possible future series, or an add-on, or maybe an extended cut or something, we might do it where you could pick multiple options, but that's way down the line, folks. We still got a lot of work to do. All right, so do command dot close, same thing we did before, ac form, me dot name, ac save yes. So it's gonna set a temp var, and I'm just gonna copy this into my clipboard because you know we're gonna use it in a minute, right? Save it.
Now we're gonna go back to the customer form where we're calling this thing from and right down here now I'm gonna message box that temp var. So we're doing the same thing we just did a minute ago, but the key difference here is it's not the form displaying this number; it's the calling form. The customer form now knows what the ID is. That's the big difference here. All right, so debug, compile, come back out here, close it, save it, close it, open it, click it, click Chevy, and now Chevy, that number two got sent back to the customer form in the temp var, and now we know what the ID is. Now the customer form knows what the user picked for the make. We know it's ID 2, Chevy, which let's double check it is correct. All right, see, we're getting there, we're getting there.
All right, let's close this. All right, since the value came back here, let's go into this form's code again. Since the value came back here, now we can actually store this. All right, so let's make some variables in here to store this information. So we want the VMakeID as a long. We want the VModelID as a long. And we want the VYearID as a long. And that VYearID is what we're actually going to be storing in the customer table, but we got to get the model first before we can get the year. Okay. So I'm going to say VMakeID equals that. And now the make section is completed.
Now we can open the form again, same form, doing most of the same stuff, but now we just got to tell it, hey, I want to see all the models for that make. So we're going to modify the SQL statement just a little bit. All right. But it's really just a big copy and paste. So copy all that, paste it down here. We're going to select the model this time. So the caption will be model. We don't want it looking like that. We're looking for... All right, so in the record source, it's going to be the V model ID, the model T. Now the where condition has to go before the order by. Okay, so we're going to put a new line here, and then we'll put our where condition here and then the order by there.
All right, so what's going to be, we got to put quotes on that, and this will be order by model name. Okay, all right, so what's our where condition going to be? It's going to be where the vMakeId, because remember we're picking a model, so we want only the models from the make that was picked earlier. Where the vMakeId equals and vMakeId and we need a space before the order. I make this mistake all the time. I forget to put the extra space in there, and that gives you an incorrect SQL statement. So we're looking for the model now. Show me the model ID, the model name from the model T where the make ID equals what the user picked earlier. That goes there. So it's going to be make ID equals 3 or make ID equals that. And let's just message box it again. Message box the model ID to make sure that worked.
Okay, you ready? All right, save it. Did a bunch of coding, so let's go debug compile. All right, everything's good. Ready, click. All right, now I should see just the Audi models. Go. Oh, look at that! Isn't that cool? Now I'll click a Q5 and that's number 30. Let's double check. Yep, number 30, Q5. Let's do another one. Click Ford Mustang and that's number 2. And I'm pretty sure that's number 2. Yep. All right, now we'll do the same thing one more time for the year.
All right, get rid of this. And this again is just a big copy and paste. You see you can do as many levels deep as you need this to be, you just have to do one of these for each of them. All right, so select the year. All right, this will be year. Select the year. vyear, if I'm going to copy the word year to my clipboard, vyearid, and then it's just vyear, not vyearname, as the caption, from vyeart, sort by vyear. Now this is going to be where the model is the model we picked up here, where vmodelid equals model ID. And then this is going to be the year ID. So copy that, paste it there. Now we should have the year.
Now, once we get the year, we don't have the message box. You know it's just going to work. We just did it. Once we get it now, now we can save that in this combo box. That's the whole point of getting it, is to put it in that field. Okay, so we're going to say vehicle combo. Oh, do we name the combo box? See, that's one of the things I hate about the wizard is, it doesn't always let you name the combo box. I was going to check this, this guy. Yep, combo 30. I hate that. Sammy, that's already on the list, I know. So we have a list going for things we want to see them change in Microsoft Access. So we're going to call this guy the Vehicle Combo. Some of the controls ask you, like the button control, what do you want to name this button? The combo box wizard should say, what do you want to name this combo box? Right? It doesn't. Pet peeve of mine.
All right. So, Vehicle Combo equals V Year ID, which is what we're saving. And I'm going to throw in a meet out refresh so it immediately saves the record. Okay save it debug compile come back out meow click open it ready here we go so there's a Chevy Silverado in there right now let's change it Ford Mustang 2023 boom look at that you like you like come on you gotta like. All right, Audi Q5, 20, I think mine's a 2021 or two, I can't remember. I think it's 2021. Okay, see? So now we've gotten to the point where the user can pick a make, pick a model, and each one of these just fills the data it needs to into the next iteration of the form. That's essentially what a cascading combo box does. Right, you pick the make, it filters the models, it filters the years. All right, still got more to go folks, still more to go.
What happens if the user just gets to this point and then decides to cancel? All right, they cancel it and then it doesn't give you the right data. I can pick Audi and then I just cancel this. Well, it's still got the values from the previous runs in there too. So we're going to add our own cancel button. Okay, then we're going to deal with what happens if you add a new make and don't add models. You'll run into a problem like this. Let's see make, let's say we add BMW in here. Right, and now the user goes like this and picks BMW. Meh, no current record because there's nothing for it to look for in that one. So we'll deal with that problem, we got a couple more problems we're gonna deal with and then we should be all set with this and we will pick it all up tomorrow in part six you know the drills tune in tomorrow same bad time same bad channel all that members you can watch it right now because I'm gonna record it right now so yeah but that is it for part five.
That's gonna do it for your TechHelp video for today I hope you learned something good stuff today live long and prosper my friends see you tomorrow for part six 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 thirteen-time Microsoft Access MVP check him out at accessexperts.com
TOPICS:
Dynamic buttons on Access forms
Returning button ID using temp var
Setting return value in a temp var
Closing form with DoCmd.Close
Using temp var in calling form
Storing returned values in variables
Modifying SQL statement for model selection
Testing model selection with message box
Copying and pasting code for year selection
Storing selected year in combo box
Testing year selection functionality
Naming and using combo box correctly
Cascading combo boxes logic
Handling user cancellation
Issues with adding new makes without models
Adding a custom cancel button
COMMERCIAL:
In today's video, I'll guide you through part 5 of my Big Button Form Series. Together, we'll make dynamic buttons on forms and use temp vars to efficiently return selected item IDs. Then, we'll integrate these IDs into the customer form, refining our SQL statements to display models corresponding to the chosen make. You'll see how to handle multiple selections and even prepare for potential user cancellations. Get hands-on with practical coding and debugging tips that will streamline your database forms. 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. In the tutorial, what is used to store the return value when a button is clicked?
A. A global variable
B. A message box
C. A temp var
D. A form property
Q2. How is the return value typecast when storing it in the temp var?
A. As a string (CStr)
B. As a double (CDbl)
C. As a long (CLng)
D. As a Boolean (CBool)
Q3. What is the purpose of setting the temp var in the code?
A. To display the clicked button's caption
B. To store the ID value for use by the calling form
C. To save the user's preference
D. To debug the button click event
Q4. After storing the return value, what action is taken next in the code?
A. The form is minimized
B. The form is closed
C. A message box is displayed
D. The form is refreshed
Q5. What is the eventual goal mentioned in the tutorial regarding selecting multiple options?
A. To allow batch processing
B. To create dynamic reports
C. To potentially handle multiple selections in a future series
D. To improve the user interface design
Q6. How does the customer form use the temp var?
A. It updates the database directly
B. It displays the returned ID in a text box
C. It shows the returned ID in a message box
D. It logs the ID to a file
Q7. Which variables are declared to store the information needed from the button clicks?
A. VMakeID, VModelID, VYearID
B. VarMake, VarModel, VarYear
C. VMakeCode, VModelCode, VYearCode
D. TempMake, TempModel, TempYear
Q8. What SQL modification is necessary to display models based on the selected make?
A. Adding an ORDER BY clause
B. Creating a new table
C. Adding a WHERE condition for vMakeID
D. Converting model IDs to strings
Q9. What common mistake is highlighted when adjusting the SQL statement?
A. Forgetting to alias the table names
B. Forgetting to add spaces around keywords
C. Incorrectly using JOIN clauses
D. Using reserved SQL keywords as column names
Q10. What is the ultimate purpose of the cascading combo boxes?
A. To dynamically filter and display options based on prior selections
B. To provide predefined static lists for user selections
C. To update database records in real-time
D. To simplify the user interface by reducing the number of forms
Q11. What is the pet peeve of the instructor regarding the wizard in Microsoft Access?
A. The wizard does not automatically save forms
B. The wizard adds unnecessary fields to forms
C. The wizard does not ask for control names
D. The wizard is not user-friendly
Q12. What additional feature is planned to address when a user cancels mid-selection?
A. Implementing an undo function
B. Adding a cancel button to reset values
C. Adding an auto-save functionality
D. Redirecting to a different form
Q13. What issue arises when a new make is added but no models are available?
A. The form crashes
B. An incorrect ID is stored
C. A 'no current record' error is displayed
D. The combo box does not update
Q14. What is the final outcome demonstrated in this part of the tutorial?
A. The form displays error messages successfully
B. The user can pick make, model, and year in a cascading manner
C. The user can add new records directly
D. The user can select multiple options at once
Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-C; 7-A; 8-C; 9-B; 10-A; 11-C; 12-B; 13-C; 14-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 with part 5 of my Big Button Form Series, where I focus on building dynamic buttons inside Microsoft Access forms to give your users an intuitive and fluid experience.
If you have not already gone through parts 1 to 4, I strongly suggest reviewing those first to ensure you are up to speed with everything we have done so far.
At this point in our project, we have successfully created a system where forms display dynamically labeled buttons. When a user clicks a button, we capture which button was selected and, more importantly, the ID associated with that item. The next step is to return that ID back to whatever part of the program called up the form originally, so the rest of our database knows what was picked.
To accomplish this, I use a temp var to store the selected button's ID. Even though the Tag property holds a string by default, I prefer using type casting to make sure we store this value as a Long integer. This ensures that, from here forward, we are working with the correct data type. Once the temp var is set, we can close the form, signaling that we are done with it and that the selection process is complete.
Now, all of this has been set up so far for choosing a single item. In future expansions, or in a possible extended cut or side tutorial, we might build out the logic to let users pick multiple items at once, but we are keeping it simple for now.
Switching over to the form that called this selection form, now it can read the temp var and know which ID the user picked. This is a crucial progression because now the calling form, such as the Customer Form, is aware of which make was chosen by the user. I like to use a message box at this stage to confirm that everything is functioning as expected; this instantly verifies that the correct ID is coming through.
With the return value properly handled, it is time to store these selections in variables for further processing. You will want to establish variables like VMakeID, VModelID, and VYearID to hold these values. Ultimately, VYearID will be the one saved back to the customer's record, but the others are just as important for controlling the logic flow as the user steps through the choices.
Next, we move on to filtering the next layer of data. After capturing the Make, we open the selection form again but tweak the SQL statement so it only shows models for the previously picked make. This requires a small adjustment in the SQL: we specify a WHERE clause based on the chosen Make ID, ensuring that the user sees only the relevant models for that make. Be attentive to the placement of the WHERE and ORDER BY clauses - even the smallest syntax errors, like missing spaces, can throw off your query.
Once the Model is selected in the same way, you repeat the process for the Year. Again, it is mostly copy and paste from the steps before, but adapted for the new table and fields. Each choice drives the options available in the following steps, which is the key aspect of cascading combo boxes.
Once the user finally picks a year, that value is assigned to a control in the form - typically a combo box for storing the selected vehicle. At this point, I remind everyone to name their controls clearly. Access does not always prompt you to name new controls, which can leave you with default names like Combo30. Renaming your combo box now helps prevent confusion as your forms get more complex. With a correctly named control and a quick call to refresh the record, the selection is stored right away.
Now, switching back to the form interface, you can see this system in action. Selecting a Make filters the available Models, then selecting a Model filters the Years, and after making all selections, the information is stored smoothly.
Still, there are important edge cases to consider. For example, what if the user begins making selections then decides not to finish and cancels out? In that case, you need to ensure no unwanted values are left lingering. Creating your own Cancel button allows you to handle these situations properly and reset the values when appropriate.
Another thing to be aware of is how the system behaves if a new Make is added but no Models exist for it yet. This will result in problems because the next form cannot find matching records. You must include handling for these instances to keep the user experience smooth.
By the end of this lesson, you have built a form system that lets users pick a vehicle's make, model, and year, each one filtered by the prior choice. This creates an interface similar to cascading combo boxes, a very popular technique for data entry in Access.
There are a few more enhancements and error handling scenarios left to cover, and I will address those in upcoming lessons. Members can proceed right away to the next part, but for everyone else, I will see you in part 6.
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
Dynamic buttons on Access forms
Returning button ID using temp var
Setting return value in a temp var
Closing form with DoCmd.Close
Using temp var in calling form
Storing returned values in variables
Modifying SQL statement for model selection
Testing model selection with message box
Copying and pasting code for year selection
Storing selected year in combo box
Testing year selection functionality
Naming and using combo box correctly
Cascading combo boxes logic
Handling user cancellation
Issues with adding new makes without models
Adding a custom cancel button
|
