Search The Web

Today's Headlines

Friday, April 8, 2011

Microsoft Access Tips & Tricks: Cascading Comboboxes

Comboboxes and listboxes are very useful in forms because they make data entry easier. They also reduce errors in data entry because you can set up the combobox or listbox to reject items that are not already in the list. I particularly like comboboxes because you don't have to take your hands off the keyboard to make an entry in a combobox. As you type, if the appropriate entry exists, you can hit Tab to choose it and move on to the next control in the form.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), and flattening a table (using VBA).

In this post, I will talk about a method for making different listboxes or comboboxes on a form dependent on one another (usually in a sequence), making the data entry process even smoother and faster. In this scheme, we might have several comboboxes on the form that collect different information that fit in a hierarchical order. Then, as each of the comboboxes are filled in, the contents of the other comboboxes are adjusted to show only relevant information to the user of the form.

To take a concrete example, take the example of a form that collects information about the type of vehicle a person drives. You might have one combobox for the make of the vehicle, another for the model of the vehicle, another for the trim level of the vehicle, and so on. It makes sense for the person to be able to choose from all possible makers when they make a choice in the Vehicle Make combobox, but after they have done that, there is no point in showing them a huge list of models in the Vehicle Model combobox, where most of the models are not made by the vehicle maker that has already been entered. Instead, we can filter the Vehicle Model combobox to only show models that the chosen maker makes. And the same principle applies to the trim level once the make and model have been chosen.

Another such example would be the entry of addresses. You can either set it up so that once the person enters a street name, only cities with that street name are available in the city combobox, and then only countries with that city name are shown in the country combobox. Or, you can set up the form so that the respondent fills in the country first, which is used to filter the city list, and then a selection in the city list enables filtering of the street list.

Since choices in one combobox or listbox "cascade" forward to other comboboxes or listboxes on the form, this kind of setup is called cascading comboboxes or cascading listboxes. In this post, I will examine how to set this up using comboboxes, but the same thing can be done using listboxes. What matters is that the information being collected in the two (or more) controls be hierarchical in some sense so that a selection in one control limits the possible choices in the next control.

I will deal with the example of vehicle makes and models to make the example code I present below, more concrete. You can take the same principle and apply it to other situations that you encounter (such as addresses, store inventories, survey responses, or any other instance where information can be categorized hierarchically, and there is a fixed set of responses that make sense for any given level based on the entries for the higher or lower levels in the hierarchy). In general, it makes sense to move from the highest level in the hierarchy to the lower levels because the number of possible entries at the lower levels are usually exponentially higher than in the higher levels (there are probably 10 times as many vehicle models as there are makes, for example), but there is not reason why you can not start from any level in the hierarchy and move in any direction.

What makes all this possible is a table of possible choices that contains fields for each level of the hierarchy. For instance, take the example of vehicles. You might have a table as shown below:

ID Make Model Trim
1 Toyota Corolla LX
2 Toyota Celica GT
3 Honda Accord EX
4 Honda Civic LX
5 Ford Mustang GT
6 Ford Mustang V6
7 Chevrolet Aveo LS

Note that there is a field for the vehicle make, vehicle model and vehicle trim level. You can create a similar table for addresses (number, street name, city, country), store inventories (category, sub-category, make, model, item), etc. The important thing to remember is that each level in the hierarchy must have its own field in the table. This table will be the row source for all the comboboxes or listboxes that are part of that hierarchy on the form. The data that the users enter should ideally also go into separate fields in the data entry table (remember that all fields in database tables are supposed to contain atomic data), but, under some circumstances, it may make sense to manipulate the data to some extent and store the manipulated data in the data entry table.

Now, for the setup of the form. There are two ways to go about it. You can make all the comboboxes visible and enabled to begin with. Set up the tab stops so that the user of the form will go through them in the logical order you think makes the most sense. Then set the row source of the first of these comboboxes to the distinct data values in the appropriate field of the row source table. So, in the vehicle information case we have been using as an example, the row source of this the combobox would be set to:

select distinct Make from vehiclesTable order by Make 
Leave the row source of the other comboboxes empty when you design the form. You will fill them when appropriate, at runtime, using VBA. The question is when is it appropriate to fill them? It is appropriate to fill the row source for the second combobox as soon as the user makes a selection in the first combobox. It is appropriate to fill the row source for the third combobox when the user makes a selection in the second combobox, etc.

So, in the afterUpdate() event of the first combobox (which, let us assume, is named cboVehicleMake), you would have the following code:

Private Sub cboVehicleMake_AfterUpdate()

On Error Resume Next

cboVehicleModel.RowSource = "Select distinct Model " & _
"FROM vehiclesTable " & _
"WHERE Make = '" & cboVehicleMake & "' " & _
"ORDER BY Model"

End Sub
Obviously, the second combobox is named cboVehicleModel in this case. You have to change the name to whatever the combobox is called in your form. Notice the single quotes inside the double quotes in the code above. Since the vehicle make is a character string, it needs to be enclosed in single quotes in the WHERE clause of the SQL statement that is being created as a string to serve as the rowsource of the second combobox. If this was a different application, and the selection in the first combobox was a number, the single quotes would not be necessary. If it was a date, then the single quotes would be replaced by #'s. You get the idea.

And the rowsource for the third combobox would be set in the afterUpdate() event of the second combobox as shown below:

Private Sub cboVehicleModel_AfterUpdate()

On Error Resume Next

cboVehicleTrim.RowSource = "Select distinct Trim " & _
"FROM vehiclesTable " & _
"WHERE Make = '" & cboVehicleMake & "' AND " & _
"Model = '" & cboVehicleModel & "' " & _
"ORDER BY Trim"

End Sub
Notice how the rowsource of the third combobox filters the vehicles table by the information in both the first and second comboboxes. In most cases, this makes sense in a hierarchical system. If you want to filter only by the selection in the second combobox, you can change the code above as appropriate.

In the second setup, you design the form so that only the first combobox is visible (or you make all the comboboxes visible, but make only the first one enabled). For this example, we will assume that we have made cboVehicleMake visible and made the cboVehicleModel and cboVehicleTrim invisible. We will also set the rowsource of cboVehicleMake as in the first method:

select distinct Make from vehiclesTable order by Make 
We will leave the rowsources of the other two comboboxes empty. They will be set at runtime using VBA.

The afterUpdate() event of the first combobox would now look as below:

Private Sub cboVehicleMake_AfterUpdate()

On Error Resume Next

cboVehicleModel.Visible = TRUE
cboVehicleModel.RowSource = "Select distinct Model " & _
"FROM vehiclesTable " & _
"WHERE Make = '" & cboVehicleMake & "' " & _
"ORDER BY Model"
cboVehicleModel.setFocus

End Sub
Notice the two extra lines of code where we turn the visibility of the second combobox on, and then set the focus to that combobox after giving it a rowsource. It is trivial to extend this scheme to create the afterUpdate() event for the second combobox so that it makes the third combobox visible, sets its rowsource and then places focus on that combobox. If you have chosen to enable and disable comboboxes rather than play with the visibility of the controls, then you have to use "Enabled" rather than "Visible" in the appropriate lines of code.

To make sure that the second and third comboboxes become invisible (or disabled) once the user moves to a new record, you can put the following code in the form's Current() as well as OnOpen() events:

Private Sub Form_Current()

On Error Resume Next

cboVehicleMake.setFocus
cboVehicleModel.Visible = FALSE
cboVehicleTrim.Visible = FALSE

End Sub
Private Sub Form_Open(Cancel as Integer)   
On Error Resume Next

cboVehicleMake.setFocus
cboVehicleModel.Visible = FALSE
cboVehicleTrim.Visible = FALSE

End Sub
Instead of setting the focus on cboVehicleMake, you can set the focus to some other control. But it is important to make sure that the focus is set to some control other than cboVehicleModel and cboVehicleTrim because it is not possible to turn off visibility of controls which have focus. And, you can obviously use Enabled instead of Visible in the above code.

I like the second scheme slightly better than the first one because there is no chance of the user directly going to a lower level combobox without first filling in a value for the upper level combobox, using the mouse. You can add code on the gotFocus() events of the lower level comboboxes to check if the upper level comboboxes have a value, and if not, you can have the focus automatically moved to the upper level combobox if the user tries to click on the lower level combobox, but it is definitely more work.

Hope this post has been helpful in solving any problems you might have had with designing and/or using cascading comboboxes in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

25 comments:

Las Vegas Foreclosures said...

Thank you for this information it helps a lot.

Kuba Jewgieniew said...

I like the valuable information you provide in your articles.

Cape Coral Properties said...

I think this is one of the most important info for me. And i'm glad reading your article.

Las Vegas Short Sales said...

Had to sort through a lot of comments, however I absolutely enjoyed your post. It proved to be extremely useful for me and I am sure to all the commenters here!

BrooklynCoe said...

I am having a hard time getting my combo boxes to cascade. I have one table [AnimalTable] with two columns [AreaName] and [Animal ID]. In the first combo box, I would like to select the area name, by which the second combo box will limit the Animal ID options according to which area I choose. I have used the code provided above, without success. I have a query created called qryArea, which incorporates this SQL:


SELECT DISTINCT AreaName
FROM AnimalTable
ORDER BY AreaName;

My VBA code looks like this:

Private Sub cboArea_AfterUpdate()


On Error Resume Next

cboAnimalID.RowSource = "Select distinct AnimalID " & _
"FROM AnimalTable " & _
"WHERE AreaName = '" & cboArea & "' " & _
"ORDER BY AnimalID"


End Sub

What am I doing wrong?

I dont receive any error messages, but the second combo box does not limit the choices. It is still giving me the entire animalID list.

Thanks

Blogannath said...

Try changing cboArea to me.cboArea in your afterupdate() sub. But the more important thing to check is whether the afterupdate() sub is being run (you can check by using the debugger to set a stop point inside the sub to see if execution stops there), and if it is being triggered, print out the value of cboAnimalID.RowSource to see if the SQL looks correct (especially the placement of the quotes, which is what I hate about text fields in Access). If your area name selection contains a single quote in it, this can create problems with the way the code is written. So, I hope you have checked to make sure that none of the area names have single quotes in them.

funker said...

I'd like to add two more levels of functionality to cascading combo boxes, in addition to the 'cascade down' described here.

1) User selects from the lowest level in hierarchy and have the change cascade up.

2) User selects from middle level, resulting in cascade both up and down the chain.

I'm finding that adding the middle level functionality requires a mess of code. Is there an elegant way to do this?

Blogannath said...

I am not sure why adding the middle level requires a mess of code. Essentially, you are using the afterupdate() event of that middle-level combobox to set the row-source for two other comboboxes. Yes, it is more code than setting the rowsource for just one more combobox, but it shouldn't really be such a mess of new code either. If you provide the specifics of why you get a mess of code, I can take a look and see if there is any way to simplify it.

jmill said...

Hello. I am trying to create 2 cascading comboboxes. This is the afterUpdate() event of the first combobox:

Private Sub Strategy_AfterUpdate()
On Error Resume Next
Indicator.RowSource = "Select Indicators.Indicator " & _
"FROM Indicators " & _
"WHERE Indicators.Strategy = '" & Strategy.Value & "' " & _
"ORDER BY Indicators.Indicator;"
End Sub

When I go to enter data in form view, the options in the drop down list for the second combobox are invisible. The drop down list itself is visible and the correct # of options are in the list, but there is no text. I know that the options are there because I can blindly select an option and open the table and see the data. Any help is appreciated. Thanks!

Blogannath said...

This does not make much sense to me. I don't know of anything that would make the dropdown visible but the text in it to become invisible. I suspect there is some corruption in your form. Try and see if a compact and repair fixes the problem. If not, you might have to copy all the elements of this form to a new one, copy over the code, and delete the original form. If even that does not solve the problem, try opening a new empty database and importing all the objects in the old database into the new one.

jmill said...

Thank you for the quick response. I tried Compact and Repair to no avail. I am working on creating a new database and hoping that will work. Thank you!

jmill said...

fyi the issue was with the correct number of columns and column widths set up in the combo box format properties.

Blogannath said...

Thanks for sharing the problem and its resolution, jmill. I should have thought of that before I started suspecting database corruption. But I am glad you got it all sorted out. Good luck!

Steve Ashby said...

I have created the first example in Access 2007 and in theory the code works, the only problem is the text isn't displayed in each combo box in FormView. If I select the invisible values and save the form then it will all correctly display in the table.

I hope this makes sense.

PLEASE HELP!!!

Blogannath said...

Have you read the previous comments from another user that had similar issues (jmill)? There is nothing in the cascading code itself that will make the text invisible, so the issue is somewhere else.

Anonymous said...

Need more help if you can spare it.

I've been working on establishing cascading comboboxes for a LONG time. I used the first of the two methods and it sort of works for me. There are 2 problems: The first is that the choices made in combobox 1-3 replicate in all the subsequent records of my tabular form. The other issue is that the data in the comboboxes do not save to the fields they represent. Please, please help. This is a crucial cog of my database function.

Blogannath said...

Take a look at this thread in a discussion form: http://www.access-programmers.co.uk/forums/showthread.php?t=190198

Are you using bound or unbound controls on your form? If you use unbound controls, that would also explain why they are not saving to the fields they represent.

Shido151 said...

Pleae understand I'm new to this but I cannot even bring up the area to write the script in. When I click on row source it brings up a query design view with very very limited options and when I click on after update, it brings up a macro builder. I'm sure it is a simple setting but I can't seem to find out how to change things over to the view where I can enter in script.

Blogannath said...

I think you would be better off learning the basics of how to use Access and VBA from a book for beginners. Once you master the basics, you will have a much easier time figuring out more advanced topics.

The simple answer to your question, though, is to locate the events tab of the property sheet of the combo box. In the After Update event, choose Event Procedure (I don't remember the exact words, but hopefully you will be able to figure that out because the other options are not anything close), then click on the three dots to the right. This will put you in the VBA editor where you can write code.

But once again, if you are unfamiliar with Access and its interface, don't just copy and paste code from here into Access and expect it to work (correctly, or even, at all). Start from the basics and work your way up so that you will get something that you can maintain.

Jennifer R Blais said...

Hi,
I found this article was really helpful but, I need to have 4 cascading combo box's... How would I do the forth one? for say in your example,at the end of WHERE you have AND then model. Would I add at the end of model another AND then do the same thing you did with model but with trim?

Blogannath said...

There is no restriction on the number of levels you can cascade comboboxes to. Each level would have a longer and longer WHERE clause in RowSource. There may be a limit on the actual number of characters you can use in a RowSource, so you might have to move the entire WHERE clause to a separate function that returns a TRUE or FALSE if you hit that limit.

Anonymous said...

Excellent post. I used this information to configure cascading comboboxes based on hardware description, manufacturer, make, and model. All of my data for the comboboxes are in the hardware_tbl. Would you have any idea how I can update a field from another table (ticket_tbl) with the rec_id once the model is selected so I can connect the hardware to the respective ticket?

Any advice is greatly appreciated.

Blogannath said...

I will give you one way I can think of. Based on that, you can come up with other methods that may better suit your needs.

I assume you are creating a new ticket and need to associate a hardware id with the newly created ticket. I would create a form that is bound to the ticket table, and have a hardware id control on it that is hidden or not enabled. I would also have unbound controls on the form that have their rowsources set to the hardware table (these would be the cascading comboboxes). Once the user has selected the data in these comboboxes, get the hardware id corresponding to the item chosen, then save all of this to the ticket table.

If you run into problems doing it this way, let me know what the exact problem is, and I can investigate how to get around it. Hope this helps.

Irais Ontiveros said...

I've been able to do all the comboboxes I needed but now the selected item cant be saved on my table. The other controls that are primarly textbox controls are being saved but all the columns where the comboboxes are supposed to save the info are left blank. You know how I can achieve this?

Blogannath said...

Are those fields bound to the appropriate columns in the table? That is the main problem I can think of for why the contents of those fields are not being saved in the table.

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web