Sunday, March 2, 2008

How to Synchronize Two Combo Boxes on a Form in Access

"I have two combo boxes (or list boxes) on my form. When the user picks an items in one list I want the choices in the second list to be narrowed down".

This remains one of the most frequent questions Access database users run up against early in their work with Access forms. It is sometimes referred to as cascading combo boxes, or dependant combo boxes, or synchronizing combo boxes. There are many good posts on the subject on the Internet. I posted on this back in November, giving a couple of good links then, including a visual how-to from Microsoft and Candace Tripp's frequently cited examples.

This morning I ran across this post in the Microsoft Knowledge Base. It has a very straightforward solution which I use in most of my forms. It's the same approach that's at the heart of Candace's examples, but the Microsoft post is very concise in describing the technique.

The Microsoft post also includes a downloadable database of example forms that I found useful when I was learning to develop forms. Of interest is that the combo box example if the database uses a different technique to "synchronize" the combo boxes. It uses the "almost no code" solution where the query that feeds the second combo box is built to use the first combo box as a filter. This is probably the easiest approach for someone who's not a VBA programmer.

7 comments:

Anonymous said...

I have used the microsoft codes and when I select a Categorie I get a blanc Product list.
What's wrong?

Stephen said...

diee- start your debugging by looking at the SQL you're generating for the second combo box- add a Debug.Print to see this- the problem usually becomes obvious when you look at the query you've created on the fly

Vicky Potin said...

I'm having a problem with exact issue. I can't seem to get my fields to work. Can we communicate outside of this blog so I can get help? I've been trying for hours and hours and reading a bunch of posts... I don't know what to do anymore!

Stephen said...

Vicky- post at www.UtterAccess.com - that's the best way to get this kind of help. The blog comments is not a good place to do troubleshooting.

Vicky Potin said...

I did! I posted at a bunch of places. I can't figure it out!

Stephen said...

Can you post the URL to your UtterAccess question and I'll look at it there?

Vicky Potin said...

I managed to fix it! I downloaded the Sample database from Microsoft and copied word for word the properties of the combo boxes and the VBA code. I had to start from scratch!

Thanks!