I hope that someone can offer a little advice on this one - I've
searched the groups 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 top level 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.

In an effort to provide an absolute reference, I have additionally
tried the following WHERE clause in the SQL for "BoatNameCombo" with
similar results:

WHERE
(((Boat.SupplierID)=[Forms]![Events]![BoatEventssubform].[Form]![SupplierCombo]))

In this instance, the
"Enter Parameter Value" box is displayed with the parameter
"Forms!Events!BoatEventssubform.Form!SupplierCombo ".

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 - Intuitively it
feels like I'm doing/not doing something really trivial and stupid
lilke I said at the start, but I really can't see what it is.

I'd be really grateful for any help that anyone can offer (and
apologies to anyone who gets annoyed by my posting the same request in
other similar groups, but I'm getting desparate...)

Thanks in Advance, Mike

I don't believe that subforms show up in the "Forms" collection. So
(((Boat.SupplierID)=[Forms]![BoatEventssubform].[Form]![SupplierCombo]))
probably doesn't compute unless you're running the form on its own.

And in this case, there is no reason to go there.

If both controls, "suppliercombo" and "boatnamecombo" are on the same
form, then use "me" in the select, not "forms". Refer to your requery.


Mark



On Sun, 29 Jan 2006 09:57:54 +0000, Mike Jakes
<mikejakes (AT) btinternet (DOT) com> wrote: