Combo boxes on continuous sub-forms, with different value lists

[David Walker]

Sorry about the complicated subject for the post...

I'll try to explain my problem with a simplified example.
Imagine I have the following fields (see below). I have these in a subform
(they are linked to a main form through UID), and I have a combo box which
allows me to choose a 'category' (ie fruit or drink). When i change this
value, I want a second combo box to give me the choices for items within
that category - ie if I chose fruit, the second box would give me the choice
of apple or banana.

UID CATEGORY ITEM
abcde fruit apple
abfed fruit banana
abdef drink water
acfea drink coffee

This should be really simple i'm sure, but I just can't make it work. I've
tried simple queries for the combo boxes, i know about requery and stuff,
have even tried looping through each record in the subform in turn to
refresh its combo box values individually. Any of the methods i've tried
might have been a little bit wrong, I haven't got much experience with
Access specifically, but the main thing that went wrong was to do with how
the second box updated. Basically I can make it update no problem, but I
can't get the combo box for each record to update independently. ie if the
first record is set as fruit, and the second as drink, both combo boxes will
either display apple and banana, or both will display water and coffee - I
can't get them to update relative to their own category settings.

Any help greatly appreciated!

David

 

[Sandra Daigle]

Hi David,

This is common problem which is caused by the fact that on a continuous
form, it appears as though there are multiple sets of controls when in fact,
there is only one set of controls just shown multiple times. Whatever you do
to the current row, happens to all rows.

To get the effect you want (synchronized combos) you can create another
textbox control, include the display column of the combo in the Recordsource
query of the form (join the foreign table and drag in the column). Position
and size the new textbox so that you can place it directly over the combo
box allowing only the down-arrow portion of the combo to show.

Then also create a GotFocus event for the textbox that only does a SetFocus
to the combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm

 

[David Walker]

Hi
Sorry should have mentioned before, will this work for Access 97 too?
Was hoping for a bit more of an elegant solution, but i'll have a play with
that and see what happens...
Cheers

David

Hi David,

This is common problem which is caused by the fact that on a continuous
form, it appears as though there are multiple sets of controls when in fact,
there is only one set of controls just shown multiple times. Whatever you do
to the current row, happens to all rows.

To get the effect you want (synchronized combos) you can create another
textbox control, include the display column of the combo in the Recordsource
query of the form (join the foreign table and drag in the column). Position
and size the new textbox so that you can place it directly over the combo
box allowing only the down-arrow portion of the combo to show.

Then also create a GotFocus event for the textbox that only does a SetFocus
to the combo.

This will create the effect that you are wanting - the correct display value
will always show even though the value itself might not be fit the criteria
that is in effect based on the current record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sorry about the complicated subject for the post...

I'll try to explain my problem with a simplified example.
Imagine I have the following fields (see below). I have these in a
subform (they are linked to a main form through UID), and I have a
combo box which allows me to choose a 'category' (ie fruit or drink).
When i change this value, I want a second combo box to give me the
choices for items within that category - ie if I chose fruit, the
second box would give me the choice of apple or banana.

UID CATEGORY ITEM
abcde fruit
apple abfed fruit
banana abdef drink
water acfea drink
coffee

This should be really simple i'm sure, but I just can't make it work.
I've tried simple queries for the combo boxes, i know about requery
and stuff, have even tried looping through each record in the subform
in turn to refresh its combo box values individually. Any of the
methods i've tried might have been a little bit wrong, I haven't got
much experience with Access specifically, but the main thing that
went wrong was to do with how the second box updated. Basically I
can make it update no problem, but I can't get the combo box for each
record to update independently. ie if the first record is set as
fruit, and the second as drink, both combo boxes will either display
apple and banana, or both will display water and coffee - I can't get
them to update relative to their own category settings.

Any help greatly appreciated!

David

 

[Sandra Daigle]

Yes it will - unfortunately this problem has been around for a long time.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi
Sorry should have mentioned before, will this work for Access 97 too?
Was hoping for a bit more of an elegant solution, but i'll have a
play with that and see what happens...
Cheers

David

Hi David,

This is common problem which is caused by the fact that on a
continuous form, it appears as though there are multiple sets of
controls when in fact, there is only one set of controls just shown
multiple times. Whatever you do to the current row, happens to all
rows.

To get the effect you want (synchronized combos) you can create
another textbox control, include the display column of the combo in
the Recordsource query of the form (join the foreign table and drag
in the column). Position and size the new textbox so that you can
place it directly over the combo box allowing only the down-arrow
portion of the combo to show.

Then also create a GotFocus event for the textbox that only does a
SetFocus to the combo.

This will create the effect that you are wanting - the correct
display value will always show even though the value itself might
not be fit the criteria that is in effect based on the current
record.

Here's a KB Article that explains the problem
ACC2000: Combo Box in Continuous Form Shows Incorrect Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;208866

Here's a link to my sample database that offers several different
alternatives for displaying this type of data including the technique
described above:

http://www.daiglenet.com/msaccess.htm


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sorry about the complicated subject for the post...

I'll try to explain my problem with a simplified example.
Imagine I have the following fields (see below). I have these in a
subform (they are linked to a main form through UID), and I have a
combo box which allows me to choose a 'category' (ie fruit or
drink). When i change this value, I want a second combo box to give
me the choices for items within that category - ie if I chose
fruit, the second box would give me the choice of apple or banana.

UID CATEGORY ITEM
abcde fruit
apple abfed fruit
banana abdef drink
water acfea drink
coffee

This should be really simple i'm sure, but I just can't make it
work. I've tried simple queries for the combo boxes, i know about
requery and stuff, have even tried looping through each record in
the subform in turn to refresh its combo box values individually.
Any of the methods i've tried might have been a little bit wrong, I
haven't got much experience with Access specifically, but the main
thing that went wrong was to do with how the second box updated.
Basically I can make it update no problem, but I can't get the
combo box for each record to update independently. ie if the first
record is set as fruit, and the second as drink, both combo boxes
will either display apple and banana, or both will display water
and coffee - I can't get them to update relative to their own
category settings.

Any help greatly appreciated!

David

 

[David Walker]

Sorry should have mentioned before, will this work for Access 97 too?

Yes it will - unfortunately this problem has been around for a long time.

I'd have thought something like that would come up so often that they'd have
put something in Access to cope with it... oh well!

I have it a go today, ended up not needing the text box laid over the combo
box. I just set the second combo box based on the field it was intended to
be based on, and had a simple bit of VB to requery the row source for that
combo box each time it was clicked on. Works fine if I don't have the combo
boxes set to 'limit to list' - which I didn't want anyway.
Not sure if its just different because its Access 97, or whether theres
something subtly different i'm doing, but works for me without the textbox
on top.

David