Microsoft Access 2016 Forms: Combo Box

a combobox looks just like a text box except it has a little arrow in it that when you click on it it drops it down and if i did a combo box for the part number it would list all the part numbers but the prom i'd run into is if i have a client that calls up and wants to purchase the book title how to mow your lawn unless i have the part numbers memorized with their associated book titles on toast so what I can do is go ahead and edit the combo box so that when I click on it it displays not only the part number but it's corresponding book title now before I go ahead and replace this part number text box with the combo box there's a couple of things I need to be aware of first of all what is this form based upon is it a query or a table because if it's based upon a query when it comes to creating a combo box I may get duplicates and I don't want to base my combo box upon a query that might give me duplicates so to find out let's go ahead and pull up the properties sheet for the entire form and to do that we need to right click in a blank area go to the design view and double click off in the gray area here to bring up the forms property sheet and then on the alt tab up at the top the record source is pointing the records from the cue select query cell profits which is over here double click on that and in there we got the part number field does it have duplicates well let's take a gander there's 10 4 5 - h2 and there it is again 10 4 5 - H - I don't want duplicates in my part number field from these different orders so let's go ahead and right-click and see what's going on in the design view and close out of the property sheet there's the part number field down below and it's coming from the book sales table and you can see it's right there and you can see it's got to join a relationship to the primary key now remember a foreign key can have duplicates and I hope for my book sales for the books that part number I have a gazillion duplicates because we're selling yay but I don't want to see that in my form for each order I only want to see the part number listed once and so I want to base the combo box upon the primary key field here in the book project table and I can do that as long as the table is part of the query I'm not going to be basing upon what's in the query here because that primary key field isn't down below you can go ahead and add it but actually link it the combobox to that primary key field which is well outside of this in the book project table and as long as the table is part of the query and it is and is related and it is I should be fine that way I can avoid base in the combo box upon the part number field here where it's going to have duplicates and so again I need to see what tables has query is based upon so let's go ahead and close out of here come back here to the design view and let's click and drag a line through that and delete that part number field and then to add a combo box come up here click on the design tab go to the controls group click on the more button and it's right there go ahead and click on it and bring it down below you get a plus sign that means when you click you're going to be adding the combo box I'm gonna click about yeah right there because when I click is going to add a label over to the left hand side and then the well I was gonna say text box but the combo box over to the right and it's got a wizard and it's gonna ask us a bunch of questions based upon our answers we should get what we want if we answer it correctly here and it says wizard creates a combo box which displays a list of values you can choose from how do you want your combo box to get its values I want the combo box to get the values from another table or query as opposed to typing in the values that you want or find a record on my form based on the value I select in my combo box keep it simple the first selection and based upon another table click Next and then you got a list of your tables your queries or if you want to see both of them I just want it from a table and it's going to be the book project table go ahead and select it click Next or you can double click on it and then it automatically advances to the next step and then what fields do we want well we want the primary key field part number and the book title so we got the part number and we can look that up so we can be able to look it up by book title and get its corresponding part number click next and then you want to sort your records well I do let's sort it by book title so when I click on the drop down arrow I can quickly have it ascending a disease and go OK here h4 how to mow your lawn and not have it garbled I mean you can do it descending by clicking the button but I want to ascending and then click next and how would you like your columns in your combo box well there's the book title it says to hide the key column recommends it all not for me brother let's go ahead and uncheck that because I want to have the part number there and then look over here the book title and you can see it's sorting ascending numerically first and then alphabetically A's down to the Z's and so I want to do a best fit for these columns by hovering over the right-hand side of the column header part number two I can see arrows pointing in opposite directions double click really fast does the best fit let's do it to the right of the book title double click there we go nice let's click Next it says when you select a row in the combo box you can store a value from that row in your database or you can use the value later to perform an action choose a field that uniquely identifies the row well that's easy it's the part number it's a primary key and you can continue reading which column in your combo box contains the value you want to store or use in your database well this is the primary key field that's unique and also the value that I want to store so we'll leave it as is and click Next and it says Microsoft Access can store the selected value from your combo box in your database or remember the values so you can use it later perform a task when you select a value in your combo box in this case a part number what do you want access to do well I want to store that part number value in the primary key field the part number here remember I got them labeled differently the part with the pound symbol is the primary key and the part with the word number is the foreign key Sony select it in the primary key field I'm gonna store it over in the foreign key which allows duplicates let's go ahead and click Next and then what label do you want to give for your combo box let's just do CMB for combo and then part number hit enter on the keyboard and we're finished you want to go ahead and take a look right click on the tab go to the form view and okay well CMB part number I can change the label for that but in any case here's the part number for that order let's go next there's the part number for that order and so if I get a new order click on new record click on the drop down arrow okay well this is a little bit difficult it's kind of squishy in here the part numbers and then the titles but in any case you won't get duplicate here part numbers 10 143 and then 10 143 with it based upon the primary key field well we just get one book title here with this corresponding part number and whatever I select there and then I go ahead and enter in the customer ID the date of sale and so on let me go ahead and hit the Escape key and let's go back to the first record click on the drop down arrow and let's clean this up because it's squishy for me so let's go ahead and right-click on the tab and go to the design view but before we do that and start clean get up let me go ahead and show you what else you can do once it's here let me click and drag this over just a bit and then click on the little gray box for the label and drag that over and in fact double-click in there and just call it the part number hit enter clean that up and then hover over the right little resizing handle so I can see arrows pointing in opposite directions double click to do a best fit and then click and well drag that back in okay now you can have it as a combo box but you can also right-click on it and change it to back to a text box the way we had it or a list box how about if we do a list box what does that look like let's go ahead and right click and go to the form view okay that's squishy you got the part number you have to scroll over to see the book title let's right click and go back to the design view and hover over the bottom right hand corner resizing handle click and drag that out and right click and go back to the form view and there you go so you can have it as a list view and what it has highlighted is the selection for that record and so you can see that little triangle there if I go ahead and select something else it says up you're changing it you're writing it to be something else you want that hit the Escape key and it goes back to what it was before so you can do that and we can toggle through the other records let's right click go back to the design view or you can right-click on that and change it to a text box and then right click and go back to the form view and we're back to where we started now once you start making changes let's right click and go back to the design view you can't really change it back at least I haven't had success to a combo box and let's right click and go to the form view and click on the drop to see so once you start messing with it and you want to go back to the combo box we got to go back and start from scratch hit the Delete key and let me go through this really quickly click on the drop down arrow combo box click and next we want book project we want part number you and let's go ahead and right-click go to our form view click on the drop-down arrow and let's fix this up okay let's do that now let's right-click and go to the design view because this - squishy in there for me for this text box field when you select it and you got your property sheet open or if you don't and then just go ahead and double click on the border of it brings up the property sheet and let's go to the alt tab and down to the column widths so the first column with in front of that delimiter the semicolon is the left hand side and that's for our part numbers and so that's a devil number 0.666 let's get rid of that let's do point seven is that gonna be enough I don't know and let's go ahead and stretch this out make it a little bit bigger let's try three hit the tab key and then right click go to the form view click on the drop down arrow okay we get a little bit more space but we got scroll II here now let's go ahead and hit the Escape key right click go back to the design view and I can try to make that second column width really large but I think the problem that we're running into is the width of the list so how about if we just did something like 4 hit the tab key and right-click go to the form view and click on the drop down arrow oh there we go okay so the part number is still a little squishy I can make that calm a little bit bigger I don't get the scroll bar down below oh that's fancy I can go ahead and scroll through and it's sort of alphabetically so I can quickly find it by book title or I could have sorted it by part number but it's easier for me to go ahead and look it up by book title here in any case let's hit the Escape key and leave it there and I'm satisfied thanks for watching hey as a quick reminder if you liked my video please give it a thumbs up you can also click on me and subscribe to my channel to get notified for the latest videos and four great specials on my products please see the description below this video

Channel: Kirt Kershaw

Views: 28,224

Rating: 4.7966104 out of 5

Keywords: microsoft Access 2016, Access 2016, Access, 2016, microsoft, combo box, combo, box, drop, down, list, data, field, create, insert, form

Id: rtBkJ7XotUo

Length: 12min 8sec (728 seconds)

Published: Mon Dec 31 2018