Big Button Form 3
By Richard Rost
2 years ago
Dynamic Big Buttons Forms for Data Entry, Part 3
In this Microsoft Access tutorial, I will show you how to use TempVars to create dynamic forms with big buttons for efficient data entry. We'll cover passing SQL statements to forms, handling recordsets, and setting button properties dynamically. This is part 3.
Members
There 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, dynamic forms, big button forms, data entry, Microsoft Access, replace combo boxes, replace list boxes, TempVars, global variables, custom message box, SQL statement, form open args, global function VBA, record sets, TempVars SQL, button record source, dynamic button captions, set button visibility, access tutorial series
Intro In this video, we'll continue building the dynamic Big Button Form in Microsoft Access by linking it to your data using SQL statements and TempVars. We'll talk about different ways to pass information between forms, work through the process of setting up SQL in TempVars, and use record sets in VBA to dynamically populate button captions and control their visibility. Finally, we'll set the form's title and caption with TempVars for a cleaner interface. This is part 3.Transcript This is part 3 of the Big Bottom Borrows. I mean the Big Button Form Series, where we're making a dynamic form with a bunch of buttons on it that can replace combo boxes, list boxes, and all that good stuff. If you haven't watched parts 1 & 2, go watch those so you know what I'm talking about. Alright, here we go.
Alright, so we got our tables built with all the data in and we got a spot to store that information in the customer table. We've got our form built. Now we just got to send information to this form to tell it what we want this to look like.
Now there's a lot of different ways you can pass information between forms. You can have this guy get its value from here. Right, we get storm in hidden form fields. That's not very flexible though because then you can only use it with this form. So that's, I don't like that one. You can use global variables if you want to store them in global variables in VBA, but then if you do run into any errors or something, those get lost and that's not a great thing.
You can use open args. I've covered that in a couple of different videos. I think I covered that in the custom message box video, which I told you to watch before this one. Open args are okay. You can send arguments into the form and then you can do stuff that way. But since this is Adam's video, I'm going to use Adam's favorite, TempVars. And I honestly think for this particular case, TempVars work best.
One of the things I'm going to do later on is we're going to make a global function that calls this form and we can store all the information in TempVars and get the information back in TempVars as well. So TempVars is the best solution for this particular case.
Alright, so the first thing we're going to do is we're going to tell this form where it's getting its data from. We're going to make an SQL statement where we're going to say we need basically, what do we need? We need an ID and a caption for the button, right, for all the buttons. So it's ID1 is Ford, ID2 is GMC, ID3 is Jeep, and so on. So that's all we really have to send to us. We're going to send to it an SQL statement with an ID and a description, what table, and then a sort order.
So it's basically a simple SQL statement. We're going to put that SQL statement in a temp bar so the form then knows, hey, I'm going to go look at this temp bar to get my SQL statement so I know what data I'm displaying. It'll make more sense once you can see it in action. Now, if you're a little weak with your SQL, that's OK. Go watch this video if you need a brush up.
Alright, I teach how to make a basic select statement from where order by. I'll be honest, I was a programmer long before I started working with Access and so I learned VBA and all that before I learned SQL. I'd probably been working with Access for five, six, seven years before I started really understanding SQL. So I was weak with the two when I first started. So go watch this if you need a little refresher.
Alright, so close this. Let's go back to our customer form design view. Let's go into the code behind this button. All right, so before we open this guy, because one of the benefits of using this AC dialog is as soon as it gets this line, as soon as this line is finished executing, it stops. That's the benefit of using a dialog form this way. We covered that in the message box video. And you can have stuff after this, but it won't run until after the form is closed.
So before the form here, we're going to put the SQL statement we need in a temp vars. And I'll move this over this way so we can see this a little bit better. Oh, someone's beaming in. All right, so we're going to say temp vars. What are we going to call it? Let's call it button record source. It could be a table query, but ideally we're going to make this an SQL statement, equals, select. Now, what are we doing first? We're doing the vehicle make ID. We're picking make first. So v make ID.
Now, I don't want to have to deal with different ID names and different field names and description names and all that once I get into the other form. I want to make it generic. So I'm going to alias this. I'm going to call this as ID. So all that button has to worry about is reading a value called ID. Next thing is the name. So V make name as button caption. So all the form has to worry about is reading a field out of the record set called button caption. It doesn't need to know what the specific names from this table are. The SQL statement handles that.
Want to learn more about aliasing? Here you go. Alright, so we got our select, we got our fields. We don't need a comma there because we're just doing two fields. And we'll continue the line. From what table? V make T. Order by the V make name. So at least they're in alphabetical order. If you want to change it, change it. That's fine.
All right. So now, in a temp bar, we have the SQL statement that the form is going to use to display its data. All right. Save that. We're good here. Now, we have to go into the form, the big button form, and make it read that. All right. Big button F, design view. All right. Now, we're going to find the forms open event. Go to the form properties, events, and find on open right down here. Now we're going to open a record set to loop through the records in whatever the SQL statement is that we put in that temp var.
If you need a refresher on record sets, here's a video for you. I believe these are all prerequisites in the other video that I had you watch, the custom message box one. If not, I apologize. But here you go. All right. So we're going to dim RS as a record set. We're gonna say set RS equals current DB dot open record set. What is the table query or SQL statement that we want to open as our record set? Well, we just put it in a temp bars. So temp bars button record source and there you go. That'll open up that record set.
Okay now we're gonna loop through the record so while not RS EOF end of file, right do some stuff RS move next. I always put this in there and then the WEND like that. I always put the incrementer right move next whatever you got to do X equals X plus 1 whatever is going in there and then end your loop and then we're gonna RS close and set RS equals to nothing. I always do my cleanup before I start worrying about what's inside the loop because you always forget this and then you end up with endless loops and you get a million of the first record. All right save that.
Now if you want to just test that it's working at this point let's message box and then we're just going to message box RS and then the field we need is button caption, right? Button caption. That's it. It doesn't matter what table we're pulling off of, it's always going to be called button caption. So at this point, I click on the button, it should open up that form and then just message box all the button captions, basically all the makes.
Alright, debug, compile, make sure that's good. Come back over here, save changes, go to customer form, click the button. All right, there we go. We got Audi, Chevy, Ford, GMC, Jeep. All right, so we know the record set loop is working. But I want to assign those names to these buttons. And I don't want to see them, so we're going to have to change the visible properties too.
So how do I know which button I need to work with? I got to go button one, then button two, then button three. So that's why I had you name them this way, because we can use a counter and just loop through them. So back to our code. We'll need another variable, x as long. And we're going to start it off as x equals 1. So now we're on button 1. And then we're going to say right here, x equals x plus 1. Now, I don't want to message box that.
What do I want to do? I want to set button x's caption to rsButtonCaption. How do I reference a control with a variable name? Well, it looks like this. This might be new to a lot of you. It's going to be me.controls and then in here you could put like first name or something like that. But what we have is we have button and X. See that? So it's going to be me.controls, button1, button2, button3 as it's looping. See how this works? Okay. .caption, we still have to use its caption property, equals the caption from the record set, RS, button, caption.
Okay. Save it. Debug compile. I've thrown a lot of debug compiles, I know. I don't like it when I hit a syntax error and it's something stupid. All right, ready? Let's close, what do we got open back here? Let's close you. Come here, come here. Close, okay, ready? And click. Look at that. See? One, two, three, four, five, and then we ran out of data, so it ended.
Okay? Alright, so let's start by setting in, let me close this. I don't like to work on, I don't like to switch from those here, watch. I don't like to switch from these right to design view because sometimes it messes things up on you really weirdly. But let's do this. Let's go design view here. Let's set all of these buttons to not visible by default. So select them all, go to format, set visible to no, save it, okay, and now in our loop, in our code, when we have set a button, then we will also set it visible. So select all of that, come over here, and say dot visible equals true.
Every time the form opens, the buttons will all be invisible. We'll make the ones visible that we need to see. Save it, close it, click the button. We're getting there. We're getting there. It's starting to look good. Alright. One more thing for today, and then we're going to call it for part three. Let's set this title and we'll put the same thing in the title and in the forms caption and again we'll do that with tempvars. Design view, come back in here, right-click, build event.
So what do you want to call these? So, let's say tempVars, let's say button form caption equals, and this one's going to be make. And I'm going to start a group now. So select make. Alright, because we're going to do make and then do the same kind of thing for model and so on. Okay, and now we have to make it so our form can read it. This is one of those instances where I do turn the project explorer on view and then project explorer because I'm flipping between code in two different spots. I wish I could open two separate windows of this, but it's just too weird in here.
So now we're going to go back to the big button form. And then you could put this anywhere. I put it at the bottom. Other settings. I should be commenting more, but we'll put some comments in later. Alright, so me.caption equals tempvars button form caption and then also the title label dot caption equals you can just say me that caption it's easier to type the same thing.
Okay save it. Yes debug compile one more time come back out yeah let's close this close it close it click it open it and there we go we got our make all set we got the right buttons in here they don't still don't do anything we still got to plumb these buttons and plumb the buttons and then yeah we'll move on to the next one lots more to come folks and we will pick this up on Monday this is a Friday class it's today's date is the date that this is going public is Friday August 2nd 2024 so we will pick this up on Monday unless you're a member if you're a member you can watch it right now because that's one of the benefits of being a member is you don't have to wait for my videos to go public so tune in Monday yeah Monday I should change this to Monday the same bat time same bat channel and we'll pick it up with part 4 but that is gonna be your TechHelp video for today I hope you learned something live long and prosper my friends I'll see you Monday for part four.
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 him out at accessexperts.com.
TOPICS:
Creating dynamic forms with VBA
Passing information between forms
Pros and cons of storing values in hidden form fields
Using global variables in VBA
Sending arguments using OpenArgs
Introduction to TempVars in VBA
Creating global functions with TempVars
Constructing SQL statements for forms
Storing SQL statements in TempVars
Setting up SQL SELECT statements
Alias usage in SQL
Assigning TempVar to SQL statement
Creating and managing VBA record sets
Looping through records in a record set
Dynamically setting button captions
Making buttons visible based on data
Setting form titles with TempVars
Using counters to reference controls
Setting control properties dynamically
Ensuring visibility of dynamically populated controls
Debugging VBA code with compile checks
COMMERCIAL:
In today's video, we'll continue with part 3 of the Big Button Form Series, where we transform forms by dynamically replacing combo boxes and list boxes with a series of buttons. First, we'll set up our tables and customer form. Then, we'll link data to the form using SQL statements stored in TempVars. Next, I'll guide you through refining the form to display button captions dynamically and making them appear as needed. We'll also set the form's title and caption using TempVars for a seamless user experience. 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 method did Adam decide to use for passing information between forms in this tutorial?
A. Global variables
B. Hidden form fields
C. Open args
D. TempVars
Q2. What is the primary advantage of using AC dialog forms as mentioned in the tutorial?
A. Ability to use more complex SQL queries
B. It stops executing code after the form is opened and resumes only after the form is closed
C. Allows for greater flexibility in designing the user interface
D. Enables the use of custom message prompts
Q3. What SQL alias does Adam use for the ID field in the SQL statement?
A. vehicle_id
B. button_id
C. V_make_ID
D. ID
Q4. What field name is used in the alias for the button caption in the SQL statement?
A. button_label
B. B_caption
C. button_caption
D. button_name
Q5. Why does Adam use the alias for ID and button caption in the SQL statement?
A. To use predefined system fields
B. To avoid different field names and make it generic for the form
C. To ensure compatibility with older versions of SQL
D. To match the field names in the customer table
Q6. In the loop for reading records from the record set, what command moves to the next record?
A. RS.next
B. RS.move()
C. RS.moveNext
D. RS.increment
Q7. What does Adam do to ensure that the buttons on the form are initially not visible?
A. Removes them from the form
B. Sets their 'Visible' property to No in the form's design view
C. Hides them using VBA code at runtime
D. Makes them invisible through a macro
Q8. Which TempVar is used to set the form's title and caption for the button form in this tutorial?
A. form_title
B. button_form_title
C. form_caption
D. button_form_caption
Q9. How does Adam verify that the record set loop is working correctly before assigning button captions?
A. By printing the record set to a debug window
B. By displaying all button captions in a message box
C. By logging the results to a text file
D. By checking the values directly in the database
Q10. What key concept is illustrated by using the statement 'me.controls["button" & x].caption = RS("button_caption")'?
A. Error handling in VBA forms
B. Looping through controls dynamically
C. Using global variables in forms
D. Configuring form events
Answers: 1-D; 2-B; 3-D; 4-C; 5-B; 6-C; 7-B; 8-D; 9-B; 10-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 3 of the Big Button Form Series. In this session, I am focusing on the process of building a dynamic Microsoft Access form that features a row of buttons, effectively replacing the need for combo and list boxes. If you have not yet reviewed parts 1 and 2, I recommend starting there so all the foundational concepts make sense.
By this stage, we have constructed the relevant tables, populated them with the necessary data, and set up our customer table to store selections. We also have our basic form ready. The next step is to communicate to this form exactly what it should display and how.
There are several options for passing information between forms in Access. You might consider retrieving values from hidden form fields, but this approach is restrictive since it only works with that specific form. Global variables are another possibility, though they can be lost if errors arise and therefore are not especially reliable. You could also use the OpenArgs property, which allows you to send arguments to a form upon opening; this was covered previously, especially in my custom message box tutorial. However, for this project, I have chosen TempVars, which I consider the most suitable method given what we are building. TempVars are robust, retain their values during the session, and can be read from anywhere in your VBA code.
Looking ahead, I plan to introduce a global function that can call this dynamic form, passing information in and out through TempVars. This approach gives us maximum flexibility and control.
For the immediate task, the first thing we need to do is indicate to our form what data source it should use. To achieve this, we'll build an SQL statement selecting the data we want for our buttons – specifically an ID for each button, along with a caption. For instance, this could be an ID for Ford, GMC, Jeep, and so on. The SQL statement will select two fields – the ID and a description (aliased, so the form code only needs to look for consistent field names). This makes it straightforward to change the underlying data or table without rewriting the code in the form itself.
If your SQL skills are a bit rusty, I suggest brushing up on writing SELECT statements as needed. In my early days with Access, I focused heavily on VBA before learning SQL, so I know the transition can be challenging. Getting comfortable with basic SELECT statements, WHERE clauses, and ORDER BY can make the process smoother.
Within the customer form's code, right before opening the button form as a dialog, we assign our SQL statement to a TempVar, for example, "ButtonRecordSource". This string tells the dynamic button form which data to display. When the dynamic form opens, it will reference this TempVar to set up its record set.
In the Big Button form, we handle this in the form's On Open event. The code will read the SQL string from the TempVar, open a corresponding record set, and loop through its records. If you need a refresher on working with DAO record sets, now is a great time to revisit that as well.
During the loop, we want each button to display the correct caption. To manage this, the design technique is to name your buttons with a numeric suffix (for example, Button1, Button2, etc.). This way, you can use a counter in your loop to easily reference and set the caption for each button dynamically without hardcoding every button's name. Access makes it possible to reference controls by building their names on the fly using the Controls collection.
Initially, all buttons on the form are set to invisible. As the record set loop progresses, each button that receives a caption is also made visible. This ensures only the required number of buttons show up based on your underlying data. Remember to always handle proper clean-up of your record sets to avoid performance or logic issues.
The next detail is setting the form's title and caption using TempVars as well. This lets you reuse the form for multiple types of selections (such as makes, models, etc.) simply by changing the value in the TempVar before opening the form. The method is the same: set the desired text in a TempVar, then assign that value to the form's Caption property and any label you are using as a title when the form opens.
All the steps above make the Button Form reusable and dynamic, with the form's buttons and title adapting to whatever data you choose to pass.
That is everything for part three of the Big Button Form Series. We have now established a form that can display a variable number of buttons, set their captions and display state based on database data, and update its title dynamically. In the next session, I will show you how to further wire up (or "plumb") the buttons to complete the workflow.
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 Passing data to forms using TempVars
Building SQL SELECT statements for button data
Aliasing fields in SQL to generic names
Storing SQL statements in TempVars
Assigning TempVar SQL to fetch form data
Opening recordsets using SQL from TempVars
Looping through a recordset to populate controls
Dynamically assigning button captions from data
Referencing controls via variable names in VBA
Setting button visibility based on data count
Resetting all buttons to invisible by default
Using a counter variable to reference controls
Setting form and label captions from TempVars |
