Cascading combo boxes and updating second box

[Sierras]

Hello,

I'm trying to do cascading combo boxes where the second box is dependant
on the first and the 3rd box is dependant on the second. I go it working
when I set the form to a single form, but if I try to do a continuous form
or datasheet, I'm finding that the previous records display changes when I
do an After update event requery of the next box.

That is, when I do a Me!MySecondComboBox.Requery, I'm finding that all the
records above go blank for the field that I'm requerying. They don't
actually go blank in the table, just on the screen. If I close the form
and re-open it, all the data is still there. This doesn't happen in a
single form since you can't see the previous records.

Any way to make the requery only on the current record I'm doing and not
all the second combo boxes above the one I'm working on in a datasheet or
continuous form?

 

[John Vinson]

That is, when I do a Me!MySecondComboBox.Requery, I'm finding that all the
records above go blank for the field that I'm requerying. They don't
actually go blank in the table, just on the screen. If I close the form
and re-open it, all the data is still there. This doesn't happen in a
single form since you can't see the previous records.

There's really only one line of controls, displayed multiple times, on
a continuous Form. When you change one control's properties (such as
the combo's Row Source) all instances of the control display that
change.

It's a bit complex but you can get around this problem. Put a Textbox
on the form, carefully superimposed on the text area of the combo box.
Set its Control Source to the value you want displayed, either by
joining the "lookup" table to the form's Query, or setting its Control
Source to =DLookUp("[textfieldname]", "[lookuptablename]",
"[IDfieldname] = " & cboMyCombo).

Make sure that the textbox's properties are:

Enabled: No
Locked: Yes
Tab Stop: No

so the user can't set focus to it or change it in any way.

The textbox will display the "looked up" value, but will disappear
behind the dropdown when the user opens the combo.

John W. Vinson[MVP]

 

[Sierras]

Very interesting idea - neve occured to me. Thanks.

But I'm wondering if it might be easier to do this with a sub form instead
of cascading combos. Maybe I could link the subform in such a way as to
not allow the values to disappear. Do you know if that would be possible
or do you think it would end up the same way as with the combos..

That is, when I do a Me!MySecondComboBox.Requery, I'm finding that all
the
records above go blank for the field that I'm requerying. They don't
actually go blank in the table, just on the screen. If I close the form
and re-open it, all the data is still there. This doesn't happen in a
single form since you can't see the previous records.

There's really only one line of controls, displayed multiple times, on
a continuous Form. When you change one control's properties (such as
the combo's Row Source) all instances of the control display that
change.

It's a bit complex but you can get around this problem. Put a Textbox
on the form, carefully superimposed on the text area of the combo box.
Set its Control Source to the value you want displayed, either by
joining the "lookup" table to the form's Query, or setting its Control
Source to =DLookUp("[textfieldname]", "[lookuptablename]",
"[IDfieldname] = " & cboMyCombo).

Make sure that the textbox's properties are:

Enabled: No
Locked: Yes
Tab Stop: No

so the user can't set focus to it or change it in any way.

The textbox will display the "looked up" value, but will disappear
behind the dropdown when the user opens the combo.

John W. Vinson[MVP]

 

[AccessVandal via AccessMonster.com]

Sierras,

I propose a Pat Hartman’s cascading combo boxes on a continuous form.
(if I can remember)

On the current event of the sub form,

Me.MyFirstCombo.Requery
Me.MySecondCombo.Requery
Me.MyThirdCombo.Requery

This will requery the combos when you are at the current record of your
subform.

Next, create or unbound your 3 existing combo boxes with the existing query.
You can hide the unbound combos, so that you’ll only see the down arrow
button.

Next, create 3 new Textboxes that will be bound by Query/Table of your
subform. Align the Textboxes to the unbound combos, so that they look like
the original Combo Boxes. Note – You can bound the “MyFirstCombo” and use
only to 2 TextBoxes if you want to.

Use your combo box query or VB to autofill the textboxes.
Or
Use your AfterUpdate event of the your combo box to requery the bound
TextBoxes.

Use the afterupdate event in the combos,

Private Sub MyFirstCombo_AfterUpdate()
Me.MySecondComb.Requery
Me.MyTextBox1.Requery ‘if using Textbox
End Sub

Private Sub MySecondCombo_AfterUpdate()
Me.MyThirdCombo.Requery
Me.MyTextBox2.Requery
End Sub

Private Sub MyThirdCombo_AfterUpdate()
Me.MyTextBox3.Requery
End Sub

If you use the query in the second combo, you need to refer to a control like
“Forms!SubFormName!MyFirstCombo” in your second combo box in the Query Grid
Criteria and same for the Third Combo.

 

[Sierras]

Well I tried to make a few combination sub forms to try and get the
links right so that I wouldn't need to use the text box trick. But I
couldn't figure out how to do it, so I finally decided to go ahead and
try your first suggestion. I must admit that I was not very convinced
in trying this cause I thought it was a little Mickey Mousey. But I
was surprised how well it actually works. Except for a little flicker
of the boxes when the user updates the combos, this is a really nice
solution. Thanks for your help.

 

[Sierras]

Hello again

The suggestion you made below worked very well when the form was based
on a table. Thank you.
However, I needed to invoke a query on the form so that a field in the
continuous form is sorted. When I did that, it seems that the new
record in the list shows ?error. This is probably normal since the
lookup is not initiated yet.

Any idea how to fix this so that the new record doesn't have an error.
Thanks

That is, when I do a Me!MySecondComboBox.Requery, I'm finding that all the
records above go blank for the field that I'm requerying. They don't
actually go blank in the table, just on the screen. If I close the form
and re-open it, all the data is still there. This doesn't happen in a
single form since you can't see the previous records.

There's really only one line of controls, displayed multiple times, on
a continuous Form. When you change one control's properties (such as
the combo's Row Source) all instances of the control display that
change.

It's a bit complex but you can get around this problem. Put a Textbox
on the form, carefully superimposed on the text area of the combo box.
Set its Control Source to the value you want displayed, either by
joining the "lookup" table to the form's Query, or setting its Control
Source to =DLookUp("[textfieldname]", "[lookuptablename]",
"[IDfieldname] = " & cboMyCombo).

Make sure that the textbox's properties are:

Enabled: No
Locked: Yes
Tab Stop: No

so the user can't set focus to it or change it in any way.

The textbox will display the "looked up" value, but will disappear
behind the dropdown when the user opens the combo.

John W. Vinson[MVP]

 

[John Vinson]

Hello again

The suggestion you made below worked very well when the form was based
on a table. Thank you.
However, I needed to invoke a query on the form so that a field in the
continuous form is sorted. When I did that, it seems that the new
record in the list shows ?error. This is probably normal since the
lookup is not initiated yet.

Any idea how to fix this so that the new record doesn't have an error.
Thanks

Please post the SQL of the query.

John W. Vinson[MVP]

 

[John Vinson]

Hello again

The suggestion you made below worked very well when the form was based
on a table. Thank you.
However, I needed to invoke a query on the form so that a field in the
continuous form is sorted. When I did that, it seems that the new
record in the list shows ?error. This is probably normal since the
lookup is not initiated yet.

Any idea how to fix this so that the new record doesn't have an error.

Ah... on second thought:

=DLookUp("[textfieldname]", "[lookuptablename]",
"[IDfieldname] = " & NZ(cboMyCombo, -1)).

Use some value that will never be an ID in place of -1.

John W. Vinson[MVP]

 

[Sierras]

Thanks John that did it.

I used

=DLookUp("[textfieldname]", "[lookuptablename]", "[IDfieldname] = " &
NZ(cboMyCombo, 0))


and I'm not getting the error anymore.

Hello again

The suggestion you made below worked very well when the form was based
on a table. Thank you.
However, I needed to invoke a query on the form so that a field in the
continuous form is sorted. When I did that, it seems that the new
record in the list shows ?error. This is probably normal since the
lookup is not initiated yet.

Any idea how to fix this so that the new record doesn't have an error.

Ah... on second thought:

=DLookUp("[textfieldname]", "[lookuptablename]",
"[IDfieldname] = " & NZ(cboMyCombo, -1)).

Use some value that will never be an ID in place of -1.

John W. Vinson[MVP]