Cascading combo boxes - dilemma

[David R]

I've come up against a strange dilemma in a new set of combo boxes.

The combo boxes pull from a table of classifications for resources. These are in three super-divisions (Council District, Patrol Division, and Program Focus) and then a field with the actual value (District #, Patrol division area, or program type).

The first combo is a SELECT DISTINCT query of the superdivisions, unbound. The second combo is filtered by the first and lists the appropriate options; so if you select Council District, it only lists the council districts, etc.
However this is on a continuous subform. Whenever you change the selection on the first combo, it changes the display of ALL of the second combos.

However if I bind the first combo to a lookup table of the super-divisions, I am effectively storing the same data twice since it is uniquely defined by the second combo. Granted this is a small violation but it seems unnecessary.

What am I missing?

David R

 

[David R]

More continuous subform fun. Even when I bound the 1st combo to the table, the 2nd combo box still requeries for every record shown.

This means that I can only see values for those subform records which match the active record's sorting schema.

If my current subform is set to "Council District", then the 1st subform record for "Patrol Division" blanks out (the data is intact), and vice versa. There are only these two fields in the subform, it would be very disappointing to have to go to a single form format.

David R

 

[DBL]

Hi David

This is a visual problem with using cascading combos in datasheet or continuous views. I've emailed you a sample to show you how to get round it. If you unhide the "bound" field in the second combo box you will see that the ID is always available, it's the description that disappears when you requery the combo box. To get round this I have a text box especially for the description and let the user select the ID field (with the description available so the user knows which option they want to select) and the description shows in the text field. It means an extra column but I haven't found any other way round it.

HTH

[This message has been edited by DBL (edited 04-13-2002).]

 

[David R]

Excellent, I got your sample and I'll try it out today.

Thanks for the help,
David R

 

[David R]

I looked at your solution and I don't think it will quite fit my needs, probably because my structure wasn't perfect. If you're a brute for punishment you can see the continuation of this thought process here: http://www.access-programmers.co.uk/ubb/Forum2/HTML/001554.html

David R


[This message has been edited by David R (edited 04-16-2002).]

 

[paulhenley]

Hopefully I can return the favour,

I found this site below
http://www.candace-tripp.com/_pages/HTML/default_HTML.asp

It has some good examples and one of them is cascading combos's.

Many thanks

Paul.