Cascading combo boxes on a subform

[Guest]

I have created a form with a combo box filtered by another one (based on the
MS Office article "Basing one combo box on another"). It works great. Now I
want it to be a subform but when I install it into the master form the
Requery function breaks down. In other words, when the second combo box
requeries the first combo box, I get a "Enter Parameter Value" window. This
problem does not occur when the from is a standalone. Any ideas?

 

[Rick Brandt]

I have created a form with a combo box filtered by another one (based
on the MS Office article "Basing one combo box on another"). It works
great. Now I want it to be a subform but when I install it into the
master form the Requery function breaks down. In other words, when
the second combo box requeries the first combo box, I get a "Enter
Parameter Value" window. This problem does not occur when the from is
a standalone. Any ideas?

This is because a form displayed in a subform is not technically "open" and is
not part of the forms collection. That makes a query reference like...

Forms!NameOfForm!NameOfControl

....no longer work. Instead you need a reference that finds the control on the
subform by first going through the parent form...

Forms!ParentFormName!SubFormCONTROLName.Form!NameOfControl

Note the all-caps in the above. That is to emphasize that the syntax requires
the name of the subform control found on the parent form which might not have
the same name as the form contained within it. If you built the form with the
wizard or by dragging and dropping then the name of the subform control likely
is the same as the form within, but you can't count on that.

 

[Guest]

THANKS Rick! . That totally worked!!

This is because a form displayed in a subform is not technically "open" and is
not part of the forms collection. That makes a query reference like...

Forms!NameOfForm!NameOfControl

....no longer work. Instead you need a reference that finds the control on the
subform by first going through the parent form...

Forms!ParentFormName!SubFormCONTROLName.Form!NameOfControl

Note the all-caps in the above. That is to emphasize that the syntax requires
the name of the subform control found on the parent form which might not have
the same name as the form contained within it. If you built the form with the
wizard or by dragging and dropping then the name of the subform control likely
is the same as the form within, but you can't count on that.