Join Adam Wilbert for an in-depth discussion in this video Creating a combo box, part of Access 2013: Queries in Depth.
In the last movie, we looked at how we could take the values from a text box and feed it into the queries criteria to filter our customers to a specific state. It's a system that works out well, but can be improved by converting the text box into a drop-down menu of options called a combo box. The advantage of a combo box is that it helps the user know what values are acceptable and will eliminate any potential for misspellings. Let's go ahead and open up the form that we finished in the last movie. It's called Customer Look Up Form. Now it works by typing in the state up here, versus if I type in CO, and run the query, it will show me all the customers that live in Colorado.
If I switch back in to the form here, and type in CI for instance, and try to run the query, I don't get any results, because CI is not a valid state. I want to create a system that helps prevent my users from typing in information that won't be valid and also allows them to select from a list of valid choices. Let's go ahead and modify our form to include that. I'm going to go into my form. And I'll right click and go into Design view. Here I'm going to go ahead and select this text box area and just delete it from view. Next, we're going to go up here to the controls group here, and choose a combo box.
Let's add one of these down into our form. Now the Combo Box Wizard is going to start up, but I'm going to press the escape key and build this combo box manually. The first thing we need to do is create a listing that will appear in the combo box when you open it up. To do that, I'll go to the Data tab of the property sheet. If the property sheet isn't available, go ahead and toggle it on with this button, on the ribbon. We'll switch to the Data tab, and look for the Row Source property, here. I'll click in this field, and then click this Build button, on the far right, to start the query builder. The values that combo boxes display in a drop-down list, are generated by queries.
Here, we're building a query inside of this combo box. It's what's called an embedded query, but it functions just like regular queries. So in this case, we want the combo box values to show states. So I'm going to pull out information from our States table. I'll double click it to add it to the query back here. Let's go ahead and say, Close to close the Show Table window. Now the original way our form work was that we have to type in the two letter state abbreviation and sometimes its all little bit more clear if our users can type in the state name instead of the abbreviation. So, in this case I want to show a list of state names. I'm going to double click on state name to add that to the query below.
And if I run this query, we'll see what our combo box will look like. Let's go ahead and go back into Design View, and then we'll close this query. I'll press this Close button here. It's going to ask if I want to save this query and I'll just say Yes. Now let's go ahead and make this just a little bit wider, cause I know my state names are a little long, and let's go test it out. I'll switch into Form View, and I'll go to this combo box, and I'll see a listing of all my states. So the next thing we want to do is make sure this is still hooked up into our query. In order to do that, we need to know what the name of this combo box is.
So let's switch back our view into Design View. I'll use the drop-down portion of the view button and choose Design View. Now first I'm going to double click here where it says Combo3. And I'm just going to double click and enter in enter a state. That will change the label that appears for our end users. Then I'll select the combo box over here. And we're going to go into the other tab and find the name property. Here, I'm going to name this combo box cbostatelookup. Now I know what the name of the combo box is and I also know the name of the form here. So let's go ahead and go ahead and go back into our query and update that. Let's go into the query.
I'll right click. Go into Design View. And I'll go to the criteria here underneath state. Let's right click on that and go zoom to give us some more room to type. I'm going to change this value here that says txtstatelookup to cbostatelookup. That way it'll be connected to our new combo box instead of the old text field. I'll go ahead and say, Okay, and that will update the query. Now we can go ahead and save the query, and switch back over to the form. Let's go ahead and test it out and see if it works. I'll switch my form into Form View. I'll use my drop-down list and select a state.
And then press Run Query. And unfortunately, I don't get any results. Let's check out another state to make sure that this is correct. I'll click on Customer Lookup form again. I'll change the state to Arizona. And press Run Query. And once again, I'm still not seeing any results. The problem is, is that in our query we're pulling out the state abbreviation. If I right click and say Design View, we can see that we're pulling out the state from the customers table which is storing the abbreviation. By using the drop-down list in the form, I'm trying to match the full state name to the abbreviation.
And obviously that's not going to work out. Let's go back into our form and fix this up. I'm going to go into the form and go into Design View. I'm going to select on the combo box here, go to the Data tab, and edit the Row Source here. This is the query that's embedded into the combo box. I'll press the Build button to edit it. Now what I can do is add in the state abbreviation column from this table. I'll drag it to the beginning by selecting the top of the bar here, then clicking and dragging to the left. Now my query is pulling out two columns, the state abbreviation and the state name.
What I want my end users to see is the state name, but what I need the query to return is the state abbreviation. So how can we take care of that? If we run this query, we'll see the two columns that are being returned. Let's go back into Design View and we'll close the query and I'll save the changes. Now there's a couple of additional properties that I need to effect on this combo box in order for this to work. First, let's take a look at this bound column here. The bound column is currently set to 1. This corresponds to column number one of the query.
And now since we've updated it, that corresponds to the abbreviation. Let's go ahead and take a look at the format tab over here. Here we can see that we have a column count as also listed as one. And really, our query is returning two columns now. So, what I need to do is change this from one to two. Finally, this column with property is important. Here, we specify how wide each of the columns that are being returned by the query are. In this case, what I want to do is specify that the first column is 0 inches wide, and then I'll type in a semi colon, and the second column is 1 inch wide.
That will effectively hide the abbreviation from the drop-down list. Let's go ahead and view the results. I'll switch into Form View and check out the results. Now, the combo box looks just like it did before. However, there's a major difference here. Now, our query is returning two columns, first, the abbreviation, second is the full name. But because of the properties that we set, we're actually hiding the abbreviation column. But the abbreviation column is still the bound column, which is going to get fed back into the query. So now if you select a state, for instance Alabama, and say run the query, I get what I expect.
The state abbreviation is being pushed in to the query. Let's go in to the form and select a different state. This time I'll choose Florida. Run the query and I get the results for Florida. So that's we can use a drop-down menu or a combo box to help guide our end users to provide valid input when creating dynamic parameter queries. If you apply this technique to your own databases, just be conscious of the columns that your combo box is returning and set appropriate values in the bound column and column widths properties.
- Defining criteria
- Understanding comparison operators
- Using joins
- Creating parameter queries
- Using Expression Builder to work with functions
- Working with dates and times
- Creating conditional statements
- Finding duplicate records
- Creating backups
- Making, deleting, and appending records
- Understanding SQL basics and writing SQL queries
- Useful query tricks