Cascading Combo problem.

[JohnB]

Hi.

Im having trouble with a cascading combo set up. It works OK but when the
requery runs after the first combo is used, any previous settings made in the
second combo disappear. Im using Access 2002 on XP.

Here is the detail. I have a main form called frmSECStudents and a subform
whose control on the main form is subSECPlacementsSubform. The subform has
two combos, cboSchoolName and cboMentor. I have arranged cboMentor so that it
shows the mentor surname, a text field beside it shows the forename. In the
row source for cboMentor I have put the following code as criteria for the
SchoolID field.

[Forms]![frmSECStudents]![subSECPlacementsSubform]![cboSchoolName]

I have added Me.cboMentor.Requery to the After Update event of cboSchoolName.

As I say, this works fine - I select a School in cboSchoolName and this
correctly filters the Mentors shown in cboMentors and when I choose a Mentor,
the forename appears in the text field and the surname appears in cboMentor.
But when I go to another subform record, and select a school in
cboSchoolName, the mentor surname shown in cboMentor in the first subforms
record disappears, although the Mentor forename still shows. If I set up a
second text field to show the Mentor surname, it correctly shows the surname.

What am I doing wrong? Should I be using combos in a different way? i.e. use
a combo to select Mentors but rely on text fields to view forename and
surname?

Thanks for any help. JohnB

 

[Marshall Barton]

Im having trouble with a cascading combo set up. It works OK but when the
requery runs after the first combo is used, any previous settings made in the
second combo disappear. Im using Access 2002 on XP.

Here is the detail. I have a main form called frmSECStudents and a subform
whose control on the main form is subSECPlacementsSubform. The subform has
two combos, cboSchoolName and cboMentor. I have arranged cboMentor so that it
shows the mentor surname, a text field beside it shows the forename. In the
row source for cboMentor I have put the following code as criteria for the
SchoolID field.

[Forms]![frmSECStudents]![subSECPlacementsSubform]![cboSchoolName]

I have added Me.cboMentor.Requery to the After Update event of cboSchoolName.

As I say, this works fine - I select a School in cboSchoolName and this
correctly filters the Mentors shown in cboMentors and when I choose a Mentor,
the forename appears in the text field and the surname appears in cboMentor.
But when I go to another subform record, and select a school in
cboSchoolName, the mentor surname shown in cboMentor in the first subforms
record disappears, although the Mentor forename still shows. If I set up a
second text field to show the Mentor surname, it correctly shows the surname.

What am I doing wrong? Should I be using combos in a different way? i.e. use
a combo to select Mentors but rely on text fields to view forename and
surname?

Man, I am starting to really hate this situation :-(
It just too difficult for me to explain what's happening and
what you have to go through to make the screen look right.
Fortunately, I think you're more than halfway there.

All my ranting aside, you are not doing anything wrong. The
problem is that a continuous form only has one set of
controls/properties (displayed multiple times). So, when
you change the criteria value and requery the second combo
box, it doesn't match the value that was in effect when you
were editing a different record.

The way to get the desired appearance is to use your text
box sized and positioned to exactly cover the text portion
of the second combo box. Lock the text box and set its
TabStop property to No. Then add a line of code to its
GotFocus event to immediately switch the focus to the combo
box. As soon as the combo box receives the focus, it will
be displayed on front of the text box and the user will be
able to proceed normally.

If that doesn't solve it for you, pleas post back with more
details about the combo box properties (row source, bound
column, column widths, etc) and the form's record source
table/query.

 

[JohnB]

Thanks for this Marsh.

I hope I havent given you an headache. Im sure what you suggest will work.
In fact just after sending my post, I reduced the size of the combo so that
it consists of only the little arrowed box and then I positioned it just to
the right of the surname text box. This combination now looks and behaves
just like a combo. Although I thought this a good idea, I felt sure that the
actual solution would be more elegant.

Ive seen quite a few posts on cascading combos and even had good replies to
one of my own but I havent seen any mention of the need to do this. Curious.

Anyway, Im very grateful for your answer.

Cheers, JohnB

Im having trouble with a cascading combo set up. It works OK but when the
requery runs after the first combo is used, any previous settings made in the
second combo disappear. Im using Access 2002 on XP.

Here is the detail. I have a main form called frmSECStudents and a subform
whose control on the main form is subSECPlacementsSubform. The subform has
two combos, cboSchoolName and cboMentor. I have arranged cboMentor so that it
shows the mentor surname, a text field beside it shows the forename. In the
row source for cboMentor I have put the following code as criteria for the
SchoolID field.

[Forms]![frmSECStudents]![subSECPlacementsSubform]![cboSchoolName]

I have added Me.cboMentor.Requery to the After Update event of cboSchoolName.

As I say, this works fine - I select a School in cboSchoolName and this
correctly filters the Mentors shown in cboMentors and when I choose a Mentor,
the forename appears in the text field and the surname appears in cboMentor.
But when I go to another subform record, and select a school in
cboSchoolName, the mentor surname shown in cboMentor in the first subforms
record disappears, although the Mentor forename still shows. If I set up a
second text field to show the Mentor surname, it correctly shows the surname.

What am I doing wrong? Should I be using combos in a different way? i.e. use
a combo to select Mentors but rely on text fields to view forename and
surname?

Man, I am starting to really hate this situation :-(
It just too difficult for me to explain what's happening and
what you have to go through to make the screen look right.
Fortunately, I think you're more than halfway there.

All my ranting aside, you are not doing anything wrong. The
problem is that a continuous form only has one set of
controls/properties (displayed multiple times). So, when
you change the criteria value and requery the second combo
box, it doesn't match the value that was in effect when you
were editing a different record.

The way to get the desired appearance is to use your text
box sized and positioned to exactly cover the text portion
of the second combo box. Lock the text box and set its
TabStop property to No. Then add a line of code to its
GotFocus event to immediately switch the focus to the combo
box. As soon as the combo box receives the focus, it will
be displayed on front of the text box and the user will be
able to proceed normally.

If that doesn't solve it for you, pleas post back with more
details about the combo box properties (row source, bound
column, column widths, etc) and the form's record source
table/query.

 

[Marshall Barton]

This issue really does give me a headache sometimes.
Depending on the settings for the combo box and the form's
record source query, it can get much more complex that what
you're doing.

Reducing the size of the combo box to just it's arrow, is
not the same as using a combo box directly. After many
times around this issue, I think you'll be better off
leaving the combo box normal size and using the set focus
approach I described earlier.
--
Marsh
MVP [MS Access]

I hope I havent given you an headache. Im sure what you suggest will work.
In fact just after sending my post, I reduced the size of the combo so that
it consists of only the little arrowed box and then I positioned it just to
the right of the surname text box. This combination now looks and behaves
just like a combo. Although I thought this a good idea, I felt sure that the
actual solution would be more elegant.

Ive seen quite a few posts on cascading combos and even had good replies to
one of my own but I havent seen any mention of the need to do this. Curious.

Im having trouble with a cascading combo set up. It works OK but when the
requery runs after the first combo is used, any previous settings made in the
second combo disappear. Im using Access 2002 on XP.

Here is the detail. I have a main form called frmSECStudents and a subform
whose control on the main form is subSECPlacementsSubform. The subform has
two combos, cboSchoolName and cboMentor. I have arranged cboMentor so that it
shows the mentor surname, a text field beside it shows the forename. In the
row source for cboMentor I have put the following code as criteria for the
SchoolID field.

[Forms]![frmSECStudents]![subSECPlacementsSubform]![cboSchoolName]

I have added Me.cboMentor.Requery to the After Update event of cboSchoolName.

As I say, this works fine - I select a School in cboSchoolName and this
correctly filters the Mentors shown in cboMentors and when I choose a Mentor,
the forename appears in the text field and the surname appears in cboMentor.
But when I go to another subform record, and select a school in
cboSchoolName, the mentor surname shown in cboMentor in the first subforms
record disappears, although the Mentor forename still shows. If I set up a
second text field to show the Mentor surname, it correctly shows the surname.

What am I doing wrong? Should I be using combos in a different way? i.e. use
a combo to select Mentors but rely on text fields to view forename and
surname?

Marshall Bartonwrote
Man, I am starting to really hate this situation :-(
It just too difficult for me to explain what's happening and
what you have to go through to make the screen look right.
Fortunately, I think you're more than halfway there.

All my ranting aside, you are not doing anything wrong. The
problem is that a continuous form only has one set of
controls/properties (displayed multiple times). So, when
you change the criteria value and requery the second combo
box, it doesn't match the value that was in effect when you
were editing a different record.

The way to get the desired appearance is to use your text
box sized and positioned to exactly cover the text portion
of the second combo box. Lock the text box and set its
TabStop property to No. Then add a line of code to its
GotFocus event to immediately switch the focus to the combo
box. As soon as the combo box receives the focus, it will
be displayed on front of the text box and the user will be
able to proceed normally.

If that doesn't solve it for you, pleas post back with more
details about the combo box properties (row source, bound
column, column widths, etc) and the form's record source
table/query.

 

[chakatheapeman]

I stumbled on to a poorly documented method for using a cascading comb
boxes in Access. If you define your row sources as Stored Procedures
the second one taking a parameter for the casqued. If you name th
first control the same as the parameter on the stored procedure it wil
automatically use the value of that control. Then the only code yo
need to make a well rounded robust set of cascading Combo Boxes is
requery on the on enter event of the second box.

This allowed me to take gads of goofy code that tried to manag
different usage scenarios. It works well. This is barley mentioned o
the help for row source on the combo box control

 

[JohnB]

Thanks for this.

I don't quite understand - what do you mean by .... define your row sources
as Stored Procedures ....? Also, is this in addition to having SQL as the
row source?

JohnB

 

[Marshall Barton]

I stumbled on to a poorly documented method for using a cascading combo
boxes in Access. If you define your row sources as Stored Procedures,
the second one taking a parameter for the casqued. If you name the
first control the same as the parameter on the stored procedure it will
automatically use the value of that control. Then the only code you
need to make a well rounded robust set of cascading Combo Boxes is a
requery on the on enter event of the second box.

This allowed me to take gads of goofy code that tried to manage
different usage scenarios. It works well. This is barley mentioned on
the help for row source on the combo box control.

Except for the terminology differences, that is exactly what
John is doing. The problem he is trying to deal with has to
do with getting all rows on a continuous form to **display**
the dependent combo box's selection on rows others than the
current record.

 

[JohnB]

Thanks for this Marsh.

By the way, you said earlier "Then add a line of code to its GotFocus event
to immediately switch the focus to the combo box."

I'm not sure
a. why this is needed and
b. I don't know what the code would look like.

At the risk of causing you to literally explode, could I ask you to expand a
little? Then I promise I'll try very hard never to ask a question about
cascading combos again.


Cheers, JohnB

 

[Marshall Barton]

LOL!

Hey, I won't explode, I just get a headache, though not much
of one with chakatheapeman's relatively simple situation.

This is needed because the requery causes the combo box to
synchronize to the first combo box's value on the current
record. The combo box on other records in the continuous
form will no longer be synchronized and therefore can not
display the proper value (because it's not in its row
source).

The reason for the text box setting the focus to the combo
box is because you really want to use the combo box to
select a value. The text box is only there to provide the
correct visual display, not for editing its value.

 

[JohnB]

Hi Marsh

What on earth does "....though not much of one with chakatheapeman's
relatively simple situation" mean?

Anyhoo - could you suggest some code to get the text box to shift focus to
the combo? Or would you like me to quietly go away now?

Also, I live in the UK - did you know that there's a place near here called
Marsh Barton? Perhaps named after one of your ancestors.

Thanks for all the help. JohnB

LOL!

Hey, I won't explode, I just get a headache, though not much
of one with chakatheapeman's relatively simple situation.

This is needed because the requery causes the combo box to
synchronize to the first combo box's value on the current
record. The combo box on other records in the continuous
form will no longer be synchronized and therefore can not
display the proper value (because it's not in its row
source).

The reason for the text box setting the focus to the combo
box is because you really want to use the combo box to
select a value. The text box is only there to provide the
correct visual display, not for editing its value.
--
Marsh
MVP [MS Access]

Thanks for this Marsh.

By the way, you said earlier "Then add a line of code to its GotFocus event
to immediately switch the focus to the combo box."

I'm not sure
a. why this is needed and
b. I don't know what the code would look like.

At the risk of causing you to literally explode, could I ask you to expand a
little? Then I promise I'll try very hard never to ask a question about
cascading combos again.

 

[Marshall Barton]

Well, I never was well versed in the grammer of my version
of the English language, much less to a user of the
language's original version Just referring to the name
used by the original poster and how his problem would only
cause a mild headache.

The text box's GotFocus event procedure would change the
focus to the combo box by using a line of code like:
Me.thecomboboxname.SetFocus

Yes, I am aware of the place named Marsh Barton. I even
took a picture of a sign as my wife and I were driving by
the area. It was way back in '91-'92 when we were living in
the beutiful little village, Hampton-in-Arden, (out towards
the Biringham airport) so my memory is a little vague, but I
got the impression that it was a commercial development
center somewhere along the road towards Cornwall. All the
relatives think it's wonderful that I am so famous that they
named a place after me ;-))
--
Marsh
MVP [MS Access]

What on earth does "....though not much of one with chakatheapeman's
relatively simple situation" mean?

Anyhoo - could you suggest some code to get the text box to shift focus to
the combo? Or would you like me to quietly go away now?

Also, I live in the UK - did you know that there's a place near here called
Marsh Barton? Perhaps named after one of your ancestors.

LOL!

Hey, I won't explode, I just get a headache, though not much
of one with chakatheapeman's relatively simple situation.

This is needed because the requery causes the combo box to
synchronize to the first combo box's value on the current
record. The combo box on other records in the continuous
form will no longer be synchronized and therefore can not
display the proper value (because it's not in its row
source).

The reason for the text box setting the focus to the combo
box is because you really want to use the combo box to
select a value. The text box is only there to provide the
correct visual display, not for editing its value.
--
Marsh
MVP [MS Access]

Thanks for this Marsh.

By the way, you said earlier "Then add a line of code to its GotFocus event
to immediately switch the focus to the combo box."

I'm not sure
a. why this is needed and
b. I don't know what the code would look like.

At the risk of causing you to literally explode, could I ask you to expand a
little? Then I promise I'll try very hard never to ask a question about
cascading combos again.


chakatheapeman wrote:
I stumbled on to a poorly documented method for using a cascading combo
boxes in Access. If you define your row sources as Stored Procedures,
the second one taking a parameter for the casqued. If you name the
first control the same as the parameter on the stored procedure it will
automatically use the value of that control. Then the only code you
need to make a well rounded robust set of cascading Combo Boxes is a
requery on the on enter event of the second box.

This allowed me to take gads of goofy code that tried to manage
different usage scenarios. It works well. This is barley mentioned on
the help for row source on the combo box control.


:
Except for the terminology differences, that is exactly what
John is doing. The problem he is trying to deal with has to
do with getting all rows on a continuous form to **display**
the dependent combo box's selection on rows others than the
current record.

 

[JohnB]

Thanks for the code Marsh and for the explanation - obviously combos leave a
long trail and certain memories.

Yes, Marsh Barton is what we call a Trading Estate, although I'm sure it
must have been named after an actual place that it sort of overtook or was
built beside - a village or just an area perhaps. It's just outside Exeter,
in East Devon.

Thanks for all the help.

Best Wishes, JohnB

Well, I never was well versed in the grammer of my version
of the English language, much less to a user of the
language's original version Just referring to the name
used by the original poster and how his problem would only
cause a mild headache.

The text box's GotFocus event procedure would change the
focus to the combo box by using a line of code like:
Me.thecomboboxname.SetFocus

Yes, I am aware of the place named Marsh Barton. I even
took a picture of a sign as my wife and I were driving by
the area. It was way back in '91-'92 when we were living in
the beutiful little village, Hampton-in-Arden, (out towards
the Biringham airport) so my memory is a little vague, but I
got the impression that it was a commercial development
center somewhere along the road towards Cornwall. All the
relatives think it's wonderful that I am so famous that they
named a place after me ;-))
--
Marsh
MVP [MS Access]

What on earth does "....though not much of one with chakatheapeman's
relatively simple situation" mean?

Anyhoo - could you suggest some code to get the text box to shift focus to
the combo? Or would you like me to quietly go away now?

Also, I live in the UK - did you know that there's a place near here called
Marsh Barton? Perhaps named after one of your ancestors.

LOL!

Hey, I won't explode, I just get a headache, though not much
of one with chakatheapeman's relatively simple situation.

This is needed because the requery causes the combo box to
synchronize to the first combo box's value on the current
record. The combo box on other records in the continuous
form will no longer be synchronized and therefore can not
display the proper value (because it's not in its row
source).

The reason for the text box setting the focus to the combo
box is because you really want to use the combo box to
select a value. The text box is only there to provide the
correct visual display, not for editing its value.
--
Marsh
MVP [MS Access]


JohnB wrote:
Thanks for this Marsh.

By the way, you said earlier "Then add a line of code to its GotFocus event
to immediately switch the focus to the combo box."

I'm not sure
a. why this is needed and
b. I don't know what the code would look like.

At the risk of causing you to literally explode, could I ask you to expand a
little? Then I promise I'll try very hard never to ask a question about
cascading combos again.


chakatheapeman wrote:
I stumbled on to a poorly documented method for using a cascading combo
boxes in Access. If you define your row sources as Stored Procedures,
the second one taking a parameter for the casqued. If you name the
first control the same as the parameter on the stored procedure it will
automatically use the value of that control. Then the only code you
need to make a well rounded robust set of cascading Combo Boxes is a
requery on the on enter event of the second box.

This allowed me to take gads of goofy code that tried to manage
different usage scenarios. It works well. This is barley mentioned on
the help for row source on the combo box control.


:
Except for the terminology differences, that is exactly what
John is doing. The problem he is trying to deal with has to
do with getting all rows on a continuous form to **display**
the dependent combo box's selection on rows others than the
current record.