Cascading Combo Boxes on Sub-form within tab control

[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 "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

 

[Rick B]

One post per question please. You also posted this in at least one other
newsgroup.

 

[Alp Bekisoglu]

No Expert but, have you tried
Private Sub SupplierCombo_Change()
Me.BoatNameCombo.Requery
End Sub

Hope it makes sense.

Alp

 

[Mike Jakes]

Thanks for the suggestion Alp - I just tried it but with the same
results i.e. received the parameter prompt when using the main form
"Events", and correct behaviour when using the sub-form on it's own.

When I enter a valid Supplier ID into the parameter prompt in the main
form, the second combo box "BoatNameCombo" is updated as expected (it
worked like this previously).

Cheers
Mike

 

[Sandy]

Hello Mike -

I ran across a similar problem. If your main table is storing a sort of
"lookup value" for supplier ID, try something like this in the AfterUpdate
event

Me.SupplierID = Me.cboYourCombo.Column(0)

The Column number relates to the position SupplierID holds in your query and
I believe it's zero based.