Microsoft Access Combo Boxes - Value List and Search Combo Boxes - Find Records

welcome to another free tutorial brought to you by access learning zone comm I am your instructor Richard Ross and in today's lesson we're going to learn about combo boxes we're going to build a value list combo box and a search combo box what is a combo box well a combo box is a combination of a text box and a list box a text box you can type values into in a list box you can pick from a list so a combo box if you click on that button there gives you the best of both worlds you can pick from a list and that information goes into a text box so this is what we're going to build in today's lesson so before we begin if you haven't watched my contact management templates video watch that video now because that's what this lesson is based on and download the free template there's the address right there on my website or I'll put a link in the description below the video grab that database real quick because we're going to be working inside of that one you can use this in any database that you want you don't have to use mine if you don't want to so here's the contact management database it's real simple it's a customer table it's a contact table with context for each customer which that's not really relevant for this lesson all we really are going to work with today is the customer table and the customer form right here's the customer and here's some contacts for each customer down below in a subform but for this lesson all we're going to care about is the customer portion of it so what I want to do is I want to make a combo box where I can pick this person's title mister miss missus and so on alright so the first thing I have to do is put a field in my customer table to hold that data because I can't just drop a combo box on the form without it having some place to store its information remember forms are only for display purposes so we're going to design our customer table here and I'm going to scroll down to the bottom and I'm going to add in here title and that will be a short text field now I like to keep stuff in order so I'm gonna click on this guy and drag it up top click and then drag it up top here and put it right in front of first name alright because that's usually where it goes right title first name last name you can put a suffix in there too if you want to like junior senior whatever I'm a junior for example so save this now you could if you want to come in here and type values and that's a benefit of a of a valueless combo box you can just type in whatever values you want also but I'm going to close this and I'm going to go over to my customer forum and now we're going to add a combo box to this customer forum so go into design view and I'm going to put first name and last name on the same line here like like I did with city state and zip but I'm going to delete one of these labels like that delete and I'm going to take last name just will go over here for a minute I'm going to slide this down and first name doesn't have to be that big maybe about that big all right and I'm going to go first comma last just like that that'll tell the user what's going in there I'll grab that box and when we're right back there that's where the last thing goes last names can sometimes be longer so you want to do something like this she can right you can make these as big or as little as you want now it's time to throw the combo box on here so let's come up top here drop this down and find the combo box tool right there and drop it right about there okay here's the combo box wizard now there's three options in here today we're going to we're going to focus on the second two we're going to ignore this first one the first one says I want the combo box to get that values from another table or query this involves relating two tables together like for example contacts and customers I'm going to have another template another video following this one that will show you how to make a a combo box that has a list of customers in it that you can put in your contact form that's going to be a different lesson but for today we're going to focus on I will type in the values that I want which means you specify the list of options which is good for something like this like title is a limited number of options and you're unlikely to change it in the future once you get your list set right mr. miss is missed dr. whatever else okay and then a little bit later I'm going to do the find a record in my form based on a value I select but for right now let's go with I will type in the values that I want next now we want one column a very simple box and we'll start off with mr. misses and I'm just using the down arrow down arrow miss down arrow and then let's throw in like dr. whatever other ones you want in here alright next now we're going to remember this value for later use so we're going to store it in a field we're going to store this value in a field we put the field in our table already remember that field is title so I'm going to pick a value from that list and it's going to store it in the title field remember the value for later use is something we're not going to use that often until we get into programming when we start having to like pick an option and then do some stuff with it next what label would you like title and then finish and there's my combo box all right we'll do a little resizing here and that's probably about big enough I'll make it about the same size of the customer ID okay save it alright let's save it close it and open it back up again alright Richard Ross I'm going to pick mr. next James Kirk let's pick mr. also and Sue Jones let's say is a Miss now let's say Sue Jones wasn't a Miss let's say Sue Jones was the Reverend Sue Jones I can put in here Reverend like that you can put in whatever you want in that in a value list textbox if you want to add Reverend to the list of options you can click on this little button right here where it says edit list items click on that and you'll get this window up here you can add Reverend to your list you don't have to write this is just a list of stuff that shows up when you drop the box down okay and you can also set a default value which one of these is the default I'll leave it blank because you don't know head okay and now Reverend will show up even if I go to a different record see there's Reverend so you have the flexibility of typing in your values or picking them from a list now this method has its pros and cons if you're only planning on having this combo box in one spot in your database then this is fine but if you're planning on having multiple forms that use the same data the same combo box you may want to consider putting that data into a table and then making a relational combo box which will be the next lesson plus also the data that store is in the table you're going to have a lot of duplicated data in here this could be better replaced with an ID from a title table but again that's going to be in the next lesson and by the way there are properties in this combo box that you can change for example double click on the box and it'll open up the property sheet here's the row source there's your list of items what I'm looking for it right down here is limit to list it says no you can force your users to have to pick an item from that list if you don't want them typing in their own items you can change that that yes I like leaving it at no for now allow value list edits that basically says that users are allowed to add things to that master list if you don't want them adding things to the permanent list then you can change that the note you can also specify a list items edit form if you have a separate form for editing this list which we're not going to get into that right now but there's all kinds of things you can do with cavil boxes and again I cover a lot more with combo boxes in my full classes alright so let's take a look at that second kind of combo box that I mentioned the one we can use to search with all right so let's go up top here and grab a combo box and just drop it anywhere like right here is fine pick the find a record in my form based on the value I selected in my combo box all right hit next now what do you want to show up in your combo box I want to search for a particular customer based on their first and last name so I'm going to go customer ID and then click that bring that over here because customer ID is the actual value that we're searching for right the customer ID is the number that's unique to each customer all right but then I need to be able to see what I'm searching for in the box so I'm going to go last name and then first name okay next this is what's going to look like when you drop the box down I'm going to see last name and then first name in that search box all right hi Tikal it means it's just hiding that key column that ID column you don't need to see it you don't want to see it in your box all right hit next what label would you like for the combo box just leave it we're going to delete it anyways and then hit finish all right there's a little search box so delete that label that it comes in with and I'm going to put this little search box right up here next to that ID field just like that that's my search box and if you want give it a give it a different color make it look different so that you know a training issue again you can train your users and say okay all the green boxes are search boxes alright so let's see how this works I'm going to close that Save Changes yes let's reopen it okay if you want to perform a search just drop this box down and now pick sue Jones and boom it goes right to sue Jones see that drop this down go to Jim Kirk boom and it goes right to Jim Kirk see how that works that's a quick little search box see that's it that's that's that simple of course you can click here and hit control f and find and all that stuff but this is nice to have right on the form or you can just click on it and then go right to a record so that's it now we know how to make a combo box to pick a title and search for a record now and in the future class I'm going to show you how to make this based on a table in other words this list of options in here will be a list that you get from a table and the benefit of that is if you use it on multiple forms you only have to change the data once you have to change multiple combo boxes we're going to create a title table or we have the list in here and we're also going to have it so down here on our contact form instead of saying 1 this will actually show Rost which is linked it's related to the customer table this is covered in my relational combo boxes template tutorial which you can find on my website or on youtube and i'll put the link to it in the description below ok so that's all for this lesson thanks for watching I hope you learn something you can find lots more templates on my website there's the address right there I'll put a link in the description below the video if you want to download the template for this database the one we just built in class there is the address for it I'll put this on the description below 2x TCB is the link if you want to learn more microsoft access i've got a free beginner in level 1 class it's 3 hours long there's the address and again links below if you like level 1 one oh one more level 2 is just one there's a link for that make sure you give me a thumbs up drop me a comment if you want and be sure to subscribe to my channel I'm to be adding lots more of these template videos in the near future there is all my social stuff blog Facebook Twitter YouTube be sure to follow me and of course if you have any questions there's my personal email address feel free to drop me a line hope you enjoyed we'll see you next time

Channel: Computer Learning Zone

Views: 54,091

Rating: 4.9277978 out of 5

Keywords: microsoft access, ms access, combo box, combo boxes, text box, list box, list of values, value list combo box, limit to list, allow value list edits, find a record on my form, combo box wizard

Id: Myk3A90Ggzw

Length: 11min 31sec (691 seconds)

Published: Sat Aug 10 2019