combo box based on a second combo box not working

[magicdds-]

I have 2 combo boxes on a continuous form.
After selecting one of 4 choices in combobox1 (from a value list), the
afterupdate property is activated to assign the rowsource property of
combobox2.
After selecting a choice from combobox2, both comboboxes display the choices
in record 1. (Both comboboxes are bound)

When you go to record 2, if the choice made in combobox1 is the same as for
record 1, the data displayed in record 1 remains unchanged. If in record 2, a
different choice is made in combobox1, the display in combobox2 for record 1
disappears (the reason is because the rowsource property of combobox2 has
changed). When you put the focus back on record 1, the oncurrent property
causes combobox2 of record 1 to show the correct data, but now the display on
combobox2 record 2 has now disappeared (again, because the rowsource property
of combobox2 was changed to the table for record 1.)

Is there a way to change the rowsource property of combobox2 in upcoming
records and still display the selected choices for the previous records?

Thanks for any help.
Mark

 

[John Spencer]

I know of no way to easily do this in a continuous form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ken Sheridan]

Mark:

It sounds like the bound column of combobox 2 is being hidden, e.g. it’s a
'surrogate' autonumber key and the visible column is a text column. The
reason the visible data disappears in such cases is that the value of the
combo box is the hidden column, but with the changed RowSource property no
corresponding value can be found in the visible column. If a 'natural' key
is used so that no column is hidden and the value of the control is the
visible column, then you won't have this problem because the control can
still show its value even if not present in the rows delivered by the
RowSource query.

The ability to use a natural key depends on the column having unique values
of course, e.g. for US states a natural key is possible, but for cities its
not as city names can be duplicated. In the latter situation you can get
round the problem by using a hybrid control made up of a text box overlaying
a combo box so that to the user it looks and behaves like a single control.
I've posted a demo of this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo has three correlated combo boxes and uses the local administrative
areas of County, District and Parish in my part of the world. If you do
download it you'll see that I'm not very enthusiastic about using hybrid
controls in a continuous form for performance reasons, preferring a
multi-column bound combo box and unbound text boxes which reference it (the
demo includes both) With only two controls the performance deficit would be
less, however.

Ken Sheridan
Stafford, England