Dynamic Forms with Big Buttons for Data Entry in Microsoft Access, Part 2
Today is part two of my big button form series where we're going to make a dynamic form that you can click to pick options just like these. If you haven't watched part one yet, what are you doing here? Go watch part one and then come on back. All right, so yesterday we got our tables all set. We got make, model, and year tables all set up.
Now once the user picks their selection, right, they pick a make, model, and a year, we've got to save that information somewhere. Now ideally, you would want to have maybe a subform over here or another form that pops up like the contacts so that they can have multiple vehicles. Ideally, but we're not going to waste our time setting up relationships and stuff for that. So for the purposes of class, we're just going to put one field in the customer table where we put the customer's vehicle. One vehicle, that's all they're allowed to have. I don't know, maybe they're registering for a demolition derby and they can only register one vehicle at a time. I don't care. But just to keep things simple for class, we're going to make one field in the customer table.
Now what should we store in the table to know they're unique, make, model, and year? Do we have to store all three of these fields in there? No, absolutely not. All we need is really the last one, the year table. Because if we know the year, we can very easily look up the model, and if we know the model, we can look up the make. So if you've saved your data this way, you really only need to store the vehicle year ID if that's your last field. And of course, if you have other options like color and stuff that are separate that aren't related, make those extra fields too. But again, for the purposes of class, all I have to do is go into the customer table and add is V year ID and that's it.
Now I can tell based on that what their make and model are. All right, so go ahead and save that. Now what we're going to do is on the customer form I'd like to put a combo box down here so we can see all three of those things together. Just like if you watched all my other videos, just like on the order form we put together last and first name into this LFQ. That just takes first name and last name and puts them together so you can see both of those fields in a combo box like that. Because normally you only see one field at a time.
So let's make a query that puts these things together. So I'm going to bring in the tables make, model, and year. You shouldn't have to worry about joins here. You should use inner joins because every record in each of these tables should have a matching record in the other table. You would never have a make and a model without any years. Otherwise, you can't. No, it's not going to work. So just bring in the stuff that we need for this combo box. I need the make name, don't need the ID. I need the model name, don't need the ID. I need the year to display it and the value that we're going to be storing in the customer table is going to be the year ID. That's the unique value that will give us all of this information. And if I open this up now, you'll see this is what it looks like.
Now I just got to put all three of these together with a little concatenation so I can see them all from my combo box. We'll go over here. We'll call this display name. Here, I'll zoom in so you can see it, shift F2. Display name and that will be colon. It will be the make name and a space and the model name and a space and the year or however you want to display it. And if you don't know about string concatenation, you're in the wrong class. This is a developer class. I cover string concatenation in my beginner classes. But just in case you missed it, yeah, of course, I got a video on it. Go watch this. You would not believe the number of times that someone emails me and says, hey, I don't need these beginner classes. I want to skip them. I'm going to go right to the VBA stuff. That's what I want to learn. And then I'm like, OK, I don't recommend it, but it's your money, do what you want. And then, you know, two days later, they're posting in the forums, what's this thing about concatenation? I'm like, well, I covered that in the classes that you skipped. So there's a reason why my course covers things in a particular order.
So anyways, a little concatenation, we get this, this looks great, that's what's going to be in our combo box, rearrange the order if you want to, I don't care. I know Adam does it, you're a make model. And let's save this. I'm going to save this one as Vehicle Q, Vehicle Q. Yeah, I'm spelling it out this time because VQ is just dumb. So Vehicle Q is fine. Now we can use this to make the combo box on our customer form to store their vehicle choice. Is it vehicle or vehicle? How do you guys pronounce it? Depends, if you're from the south, it's definitely a vehicle. Drop that combo box right there. Look up the values from a table or query. Go to queries, pick the Vehicle Q. Next, what do you want? Well, we need the year ID first. And then we need the display name. We don't need all this other stuff. All right, next, we're going to sort it by the display name. Next, we're going to arrange the columns like that. We're going to resize that one.
Normally, this is the key column. This guy, come here. I can't get my mouse on it. This is the key column, but since this combo box is made from a query, you don't get that little checkbox here that says hide the key column. Access will only give you that if it's a table. So we can manually make that zero and that will hide it. Make this as wide as you think it needs to be. You can take a look at the data that's in here. Some of those are pretty wide. Yeah, like the GMC Sierra. Let's bring it out to here. All right, next. What field is the bound field? In other words, what's the data we care about? The year ID, obviously. We're going to store that value in the year ID field that we just added to the customer table. All right, we're picking the value and we're storing it in the customer table. Next, what label do you want? Vehicle. And we're done. There it is right there. We'll slide it up into place like so. Make this a bit wider. That width that we set earlier is for how wide the box is when you open it when you drop it down. Little format painter and picks that. All right, save it, close it, open it, and now we can pick a vehicle. All right, there's our list. And this list is what we're going to make our button and the big form to avoid because if you got greasy technicians with, you know, two thumbs, it's very difficult to click on that little guy, open this up and scroll down to pick all these things. Even if you make these nice and big, it's still easier to do what I'm going to show you how to do with these big button forms.
All right. So now we've got a way to view the data in here. Now we can start actually building the form, the single dynamic form that we're going to use to select all of our options. Now, I'm going to make this as a really basic form. It's not going to have any records in it. It's going to be an unbound form just like our main menu. It's not going to have any record selectors or navigation buttons. So I'm going to just copy the main menu and use that as the template for the big button form. Let's find the main menu, copy, paste, control C, control V. We'll call this big button F or whatever you want to call it I don't care but if you want your code to match mine name your stuff the same you can rename it later if you want to right-click design view I'm going to delete everything in here except for one button and that label caption I want to keep these things while we're in here let's change the caption because the caption right now says tech help free template we'll just call this the Big Bottom. Big Bottom? Big Bottom Barrow. I'm thinking elementary school math. They teach a big bottom barrow. Big Button Form. We're going to replace that in code. So when it opens up, it's going to say select the make or whatever. All right, this guy, let's change the name of it to the title label. And again, we're going to replace this in code or the caption in code.
So I'll just put title in here. That's going to be changed. Remember, change captions of labels here, not over here so it doesn't resize this guy. Now for the button, let's change the name to button1. And you can also make that the caption. And again, the caption is going to be changed with code. Now here's the thing with the buttons. We're going to have to copy and paste and make as many buttons as we think we're going to need in the worst case scenario. So I'm going to do it with I think I'm going to fit 18 buttons on the screen. If you need more than that, you'll have to make more. The thing is that Access cannot create or destroy objects on a form like buttons or labels or text boxes. It can't create them or destroy them unless you switch to design view, which you can do if you want to do stuff for yourself. And I built some databases like that just for me, where I say make me 10 buttons and the VBA code creates them. But your end users aren't going to be able to do that. In fact, your end users shouldn't have design access to the forms at all because you should be giving them ACCDE files, in which case they can't get into design view. So you have to basically create as many buttons as you're going to think you're going to need up front. So if you got 30 items, you're going to need 30 buttons. And then what we can do is we can show and hide them by making the visible property true or false in our codes. If there's six options, we'll only see six buttons. And yes, it is possible to resize the buttons, to resize the form, to do all that stuff. We're not going to cover that right now. If enough of you want to see it, I'll show you how to do it in a future lesson. Post a comment down below if you do. So I'm gonna resize this guy so I can get roughly three of them across. All right, so I'm gonna go copy.
Actually, what I'm gonna do first is I'm gonna change the caption to just button because it's easier now if I copy all these just come on here and go two, three, four, five, and so on. So copy, paste, paste. I'm gonna line these up like this. So they're how I want to see them. That looks about right, close enough, maybe a little bit to the left like that. Okay looks good. Then I'm going to copy this and go paste, paste, paste, paste, paste. I got 18 of them, right, 3 by 6. Now all you got to do is come over here and change this to button 1. All right, now I'm going to copy button to my clipboard and I'm going to click on this guy and this is not going to be button 2 and 2. See, right here, button 3, 3. Next one, you can click over here on the name and it will select that for you. Button 4, 4. And yeah, this is one of those things that's a little tedious to do the first time but you only have to do it once or if you got to add more buttons later, you got to go, okay, come in here and add three more buttons. No big deal. We're going to have code in our VBA to check. We're going to set the maximum number. So if you do have 20 options, it's going to yell at you and say you don't have enough buttons. We'll get to that. I rehearsed this earlier when I was building the prototype database. It took me under two minutes to do all 18 buttons. So I'm going to pause this now. I'm going to do one more for you. Click on this, click here, paste, button 5, 5. See, it's not, it's not hard. I can do it in under two minutes. I'm not going to make you watch me have to do it. So I'm going to pause now and finish them all myself. Go ahead and do it yourself too. Go on, get going, do it right now. All right, I'm on 11 and I want to say, do you have to name them like this? Yes, name them exactly like this, button 11, button 12, exactly like I'm doing it because we're going to make a loop in VBA and we can programmatically access the stuff in each of these buttons, but it's got to be exactly that, button and then the number. And there we go. I got all 18 of them done, all set. And yeah, that took me under two minutes to do, so you can do it too. All right. I'm going to leave a little space down here because later on we're going to put a cancel button down here in case the user wants to cancel.
Okay. But this is all set. We're going to save this, close it, open her up, take a peek. All right, looks pretty good. Not too bad. One more thing I am going to do is I'm going to come in here and turn auto center on. Find the auto center property and set that to yes because we're going to be opening up this as a dialog form so that it pauses execution and when that happens, it treats it like a pop-up form. So you want to put auto center on so it opens up in the middle of your access application. Alright it actually opens up in the center of this here. Alright so that's what I want. Otherwise if you got a multi monitor setup like I do it could open up anywhere on any monitor and that's annoying. So make sure you got auto center on.
Alright so now we can put a button on here that our big fat fingered technicians can click on to open up that form. Okay so design view and I'm just going to grab one of these buttons over here, copy and paste, slide it down here, make a big fat fingered button that says, whatever, what do you want? Select vehicle. I will right click properties and name this, I shall henceforth name this button, what am I calling it? Select vehicle button BTN. Save it. And we'll put some code in it to open up that form. Right click, Build Event. Yeah, I know it was off the screen. You guys don't need to see all that. My code window's always giant because when I work on stuff myself, I expand it a lot. And then when I have to teach a video, I got to close it down. All right, do command, open form. What are we opening up? Big button F. I almost typed in big buffoon. Big button F. And then we could go comma, comma, comma, comma, comma all the way to window mode and pick AC dialog like that. Or if you don't want all the commas there, you just go like this. You say window mode. It's got to be the exact name of the parameter, colon equals AC dialog. And then it gets rid of all those extra commas. I just learned that one myself not too long ago, a couple of years ago.
Debug compile. Oh, what do we got? Variable not defined status box. That's right, I deleted the status box. Let's get rid of all the code because we've got in our big button F. We've got all this code that we no longer need and there's no reason to have it. This is all the stuff that was in the main menu and we can just get rid of all that. We're going to put a bunch of stuff in here in a bit. All right, save that and that's why we do a debug compile once in a while. Debug compile, it cleans up all that garbage.And also make sure you got Option Explicit in all of your modules: your form modules, your report modules, and your module modules. All right, we should be good to go now. Once again: main menu, customer form, select vehicle, and there it is.
And we'll be able to click buttons and do stuff. Can we click buttons and do stuff now? No, we haven't programmed this yet. All we can do now is close this guy. Well, we're going to start programming that guy in part 3 tomorrow.
So you know the drill: tune in tomorrow, same bat time, same bat channel. And if you're a member, you can watch it right now because I'm going to continue recording right now. So it should be posted soon.
That's one of the benefits of being a member. You don't have to wait for this stuff to get published. But that is going to be your TechHelp video for today. I hope you learned something in part 2.
We're getting to the good stuff, folks. We had a lot of setup to do before we get to the meat and potatoes. Right? We got some condiments we had to set up first. I don't know.
Anyways, live long and prosper, my friends. I'll see you tomorrow for part 3.
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/BigButtonForm2
No comments:
Post a Comment