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
