Combo box get values from Sub form

[Gavx]

I have a form that contains 2 sub forms, lets call them subform1 and subform2.

The main form contains a combo box (1st combobox) listing master records - selecting one populates subform1 with sub records of the master record that has been selected from the combo box. This is controlled by using the Link Master/Child fields.

And this is what I want;
Another combo box (2nd combobox) on the main form whose records are populated from a particular column of the sub records that are displayed in subform1.
Selecting one of the combo box options then lists fields relevant to that sub record in subform2.

How do I achieve this?

I might add the the records that populate both sub forms and 1st combo box come from the very same query. I have to split the query because the full query is not updateable, whereas cut in half makes it editable.

 

[CJ_London]

your second combobox rowsource would need the same sql as the subform recordsource but with only the required column selected.

If it is further limited by filtering then you would need to add that to the combobox rowsource
Something like

Combo2.rowsource="SELECT ColRequired FROM (" & subform1.form.recordsource & iif(subform1.form.filter<>""," WHERE " & subform1.form.filter,"") & ")"

 

[Gavx]

Sorry for the bother, this is complicated.

So the current statement is;

SELECT DISTINCT TransportBookings.CustomerName, TransportBookings.CustomerID, TransportBookings.BookingID, Guest.GuestName, Guest.GuestID
FROM TransportBookings INNER JOIN Guest ON TransportBookings.GuestID = Guest.GuestID;

And you are suggesting;

SELECT DISTINCT TransportBookings.CustomerName, TransportBookings.CustomerID, TransportBookings.BookingID, Guest.GuestName, Guest.GuestID
FROM TransportBookings INNER JOIN Guest ON TransportBookings.GuestID = Guest.GuestID & iif(TransportBookingsSubForm INNER JOIN Guest ON TransportBookings.GuestID <>""," 
WHERE " & TransportBookingsSubForm INNER JOIN Guest ON TransportBookings.GuestID,"") &  ")"

I couldn't have that correct 'cause I am getting a syntax error

 

[CJ_London]

And you are suggesting

No - I'm suggesting what I posted

I couldn't have that correct 'cause I am getting a syntax error

better to solve the syntax error

and just to clarify

So the current statement is;

is the statement for what? the form or the combobox?