By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Cascading Combo Boxes on Subform within Tab Control - Help!

P: n/a
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 "BoatEventssubform". On this sub-form are two combo boxes,
named "SupplierCombo" (unbound) and "BoatNameCombo" (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 "SupplierCombo" list and filtering
the list of craft in the "BoatNameCombo" 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.SupplierID, Suppliers.SupplierName,
Suppliers.CollectionPostalCity
FROM Suppliers;

BoatNameCombo:
SELECT Boat.BoatID, Boat.BoatName, BoatType.BoatBrand,
BoatType.BoatModel, Boat.Location, BoatType.Berths,
BoatType.LayoutType
FROM BoatType INNER JOIN Boat ON BoatType.BoatTypeID = Boat.BoatTypeID
WHERE
(((Boat.SupplierID)=[Forms]![BoatEventssubform].[Form]![SupplierCombo]))
ORDER BY Boat.BoatName, Boat.Location;

I also use the following code in the "After Update" property of
"SupplierCombo" to update the list in "BoatNameCombo":
Private Sub SupplierCombo_AfterUpdate()
Me.BoatNameCombo.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!BoatEventssubform.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
Jan 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 "BoatEventssubform". On this sub-form are two combo boxes,
named "SupplierCombo" (unbound) and "BoatNameCombo" (bound to
"BoatID", an Autonumber Primary key). SQL for the two combo boxes is as follows:
SupplierCombo:
SELECT Suppliers.SupplierID, Suppliers.SupplierName,
Suppliers.CollectionPostalCity
FROM Suppliers;

BoatNameCombo:
SELECT Boat.BoatID, Boat.BoatName, BoatType.BoatBrand,
BoatType.BoatModel, Boat.Location, BoatType.Berths,
BoatType.LayoutType
FROM BoatType INNER JOIN Boat ON BoatType.BoatTypeID = Boat.BoatTypeID
WHERE
(((Boat.SupplierID)=[Forms]![BoatEventssubform].[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!BoatEventssubform.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.SupplierID)=[Forms]![Events]![BoatEventssubform].[Form]![SupplierCombo]))
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Jan 28 '06 #2

P: n/a
On Sat, 28 Jan 2006 22:34:55 GMT, "Rick Brandt"
<ri*********@hotmail.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!BoatEventssubform.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.SupplierID)=[Forms]![Events]![BoatEventssubform].[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!BoatEventssubform.Form!SupplierCombo ".

I also now get the same prompt when running the sub-form
"BoatEventssubform" 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 misunderstanding the concept!).

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

Thanks again, Mike
Jan 29 '06 #3

P: n/a
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!BoatEventssubform.Form!SupplierCombo ".

I also now get the same prompt when running the sub-form
"BoatEventssubform" 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 misunderstanding 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!NameOfParentForm!NameOfSubformCONTROL.Form!N ameOf 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



Jan 30 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.