Cascading Combo Boxes on Subform within Tab Control - Help!

[Mike Jakes]

I hope that someone can offer a little advice on this one - I've
searched the group but can't find an answer. I think that I'm doing
something really stupid or missing something trivial, but see what you
can make of this...

I have a main form "Events" that contains a tab control. The tab
control has 7 pages. The 7th page (named "Boats") contains a subform
called "BoatEventssubf orm". On this sub-form are two combo boxes,
named "SupplierCo mbo" (unbound) and "BoatNameCo mbo" (bound to
"BoatID", an Autonumber Primary key).

The purpose of this whole form is to set up different aspects of
organising sailing events for a sailing club. Specifically this part
of the form is associate boats to an event by selecting a supplier
(yacht charter company) from the "SupplierCo mbo" list and filtering
the list of craft in the "BoatNameCo mbo" list to show only the yachts
in that supplier's fleet. Both combo boxes display some additional
information in the pick list e.g. location of supplier, location of
yacht but only the supplier name and boat name when the entries are
selected.

SQL for the two combo boxes is as follows:
SupplierCombo:
SELECT Suppliers.Suppl ierID, Suppliers.Suppl ierName,
Suppliers.Colle ctionPostalCity
FROM Suppliers;

BoatNameCombo:
SELECT Boat.BoatID, Boat.BoatName, BoatType.BoatBr and,
BoatType.BoatMo del, Boat.Location, BoatType.Berths ,
BoatType.Layout Type
FROM BoatType INNER JOIN Boat ON BoatType.BoatTy peID = Boat.BoatTypeID
WHERE
(((Boat.Supplie rID)=[Forms]![BoatEventssubfo rm].[Form]![SupplierCombo]))
ORDER BY Boat.BoatName, Boat.Location;

I also use the following code in the "After Update" property of
"SupplierCo mbo" to update the list in "BoatNameCombo" :
Private Sub SupplierCombo_A fterUpdate()
Me.BoatNameComb o.Requery
End Sub

The filtering works exactly as planned when I open the sub-form on
it's own, but when it is opened as designed within the main form, the
"Enter Parameter Value" box is displayed with the parameter
"Forms!BoatEven tssubform.Form! SupplierCombo". For debug purposes,
I've put a text box onto the sub-form to display the value of
"SupplierID ". I have noticed that when the parameter value input box
is shown, the text box hasn't yet been updated with the new value.

This behaviour leads me to think that I am not correctly referencing
the first combo box from the second, and that specifically I'm missing
something to do with the tab control on the main form.

I'd be grateful for any suggestions as this is now driving me mad!

Thanks in Advance, Mike

[Rick Brandt]

Mike Jakes wrote:
[snip]

I have a main form "Events" that contains a tab control. The tab
control has 7 pages. The 7th page (named "Boats") contains a subform
called "BoatEventssubf orm". On this sub-form are two combo boxes,
named "SupplierCo mbo" (unbound) and "BoatNameCo mbo" (bound to
"BoatID", an Autonumber Primary key). SQL for the two combo boxes is as follows:
SupplierCombo:
SELECT Suppliers.Suppl ierID, Suppliers.Suppl ierName,
Suppliers.Colle ctionPostalCity
FROM Suppliers;

BoatNameCombo:
SELECT Boat.BoatID, Boat.BoatName, BoatType.BoatBr and,
BoatType.BoatMo del, Boat.Location, BoatType.Berths ,
BoatType.Layout Type
FROM BoatType INNER JOIN Boat ON BoatType.BoatTy peID = Boat.BoatTypeID
WHERE
(((Boat.Supplie rID)=[Forms]![BoatEventssubfo rm].[Form]![SupplierCombo]))
ORDER BY Boat.BoatName, Boat.Location; [snip] The filtering works exactly as planned when I open the sub-form on
it's own, but when it is opened as designed within the main form, the
"Enter Parameter Value" box is displayed with the parameter
"Forms!BoatEven tssubform.Form! SupplierCombo". For debug purposes,
I've put a text box onto the sub-form to display the value of
"SupplierID ". I have noticed that when the parameter value input box
is shown, the text box hasn't yet been updated with the new value.

This behaviour leads me to think that I am not correctly referencing
the first combo box from the second, and that specifically I'm missing
something to do with the tab control on the main form.

[snip]

It's not the TabControl. That doesn't affect how you reference form objects at
all. When referencing a control on a subform you have to "go through" the main
form. Your reference doesn't mention the main form. You need...

WHERE
(((Boat.Supplie rID)=[Forms]![Events]![BoatEventssubfo rm].[Form]![SupplierCombo]))
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Mike Jakes]

On Sat, 28 Jan 2006 22:34:55 GMT, "Rick Brandt"
<ri*********@ho tmail.com> wrote:

[snip]

The filtering works exactly as planned when I open the sub-form on
it's own, but when it is opened as designed within the main form, the
"Enter Parameter Value" box is displayed with the parameter
"Forms!BoatEven tssubform.Form! SupplierCombo"

[snip]

It's not the TabControl. That doesn't affect how you reference form objects at
all. When referencing a control on a subform you have to "go through" the main
form. Your reference doesn't mention the main form. You need...

WHERE
(((Boat.Suppli erID)=[Forms]![Events]![BoatEventssubfo rm].[Form]![SupplierCombo]))
[snip]

Thanks for the quick response Rick - much appreciated

I tried your suggestion, but when running the main form "Events" I
again get the parameter prompt, this time for
"Forms!Events!B oatEventssubfor m.Form!Supplier Combo".

I also now get the same prompt when running the sub-form
"BoatEventssubf orm" on it's own (which I think I understand -
basically the sub-form running in isolation doesn't require the
[Events] reference as in this mode it's not running as an embedded
control - of course, I could easily be misunderstandin g the concept!).

Any other ideas, suggestions or divine inspiration would be more than
welcome!

Thanks again, Mike

[Rick Brandt]

Mike Jakes wrote:

Thanks for the quick response Rick - much appreciated

I tried your suggestion, but when running the main form "Events" I
again get the parameter prompt, this time for
"Forms!Events!B oatEventssubfor m.Form!Supplier Combo".

I also now get the same prompt when running the sub-form
"BoatEventssubf orm" on it's own (which I think I understand -
basically the sub-form running in isolation doesn't require the
[Events] reference as in this mode it's not running as an embedded
control - of course, I could easily be misunderstandin g the concept!).

Any other ideas, suggestions or divine inspiration would be more than
welcome!

First off there is no reference that will work in both circumstances. If
you need to use this form both as a subform and as a stand-alone form then
you need a different strategy. As for the reference when it is a subform
the correct generic syntax is definitely...

Forms!NameOfPar entForm!NameOfS ubformCONTROL.F orm!NameOf Control

Notice the ALL-CAPS portion. The syntax requires the name of the subform
*control* which is not necessarily the same as the form contained within
(often it is though). When in doubt I use the expression builder to navigate
to the control.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com