Dynamic Forms with Big Buttons for Data Entry in Microsoft Access, Part 3

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.

For a complete video tutorial on this topic, please visit https://599cd.com/BigButtonForm3