Running a query in expression builder

[nathbooth]

I would like to take the values of two combo boxes in a form, pass them to a query, and if there is a result display that in a text box in the form, i would like to do this in expression builder if possible, and if it is if there is no result fromt he query can you display some text such as "not valid", here is how i thought it might work but didnt:

=( [Flights Query]![SourceID] = [Source ID], [Flights Query]![DestinationID] = [DestinationID] ) SELECT «Expr» [Flights Query]![FlightID]

The flightID is what i would like to be the result if the SourceID and DestinationID exist as a flight.

Thanks

Nathan

[MMcCarthy]

Try this ...

=nz(DLookup("[FlightID]", "Flights Query", "[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)

This will return 0 if no FlightID is found. If FlightID is a number you can't have a text value like "not valid"

Mary

[nathbooth]

Arghh i got a little ahead of myself i think im having trouble with this

SELECT flights.SourceID, AirportName FROM Airports ORDER BY Airports.AirportName;

I am trying to make a Combo Box display the names of the Airports, Airports have an AirportID as Primary key and then within flights SourceID is a foreign key attached to AirportID, so i am trying to make the destination selected by name become SourceID

Thanks

[nathbooth]

Corrected, it should be AirportID it finds and holds as the field value

Arghh i got a little ahead of myself i think im having trouble with this

SELECT Airports.AirportID, AirportName FROM Airports ORDER BY Airports.AirportName;

I am trying to make a Combo Box display the names of the Airports, Airports have an AirportID as Primary key and then within flights SourceID is a foreign key attached to AirportID, so i am trying to make the destination selected by name become AirportID i think

Thanks

[MMcCarthy]

Corrected, it should be AirportID it finds and holds as the field value

Sounds about right.

[nathbooth]

i have control source as:

=[Flights]![DestinationID]

and Row Source as;

SELECT Flights.DestinationID, Airports.AirportName FROM Airports INNER JOIN Flights ON (Airports.AirportID=Flights.ViaID) AND (Airports.AirportID=Flights.DestinationID) AND (Airports.AirportID=Flights.SourceID) ORDER BY Airports.AirportName;

The combobox shows all the airport as is wished but when u select an airport it says: control cannot be edited, it is bound to the expression [Flights]![DestinationID], am i just missing something that I shoudl be doing thats really obvious?

[MMcCarthy]

i have control source as:

=[Flights]![DestinationID]

and Row Source as;

SELECT Flights.DestinationID, Airports.AirportName FROM Airports INNER JOIN Flights ON (Airports.AirportID=Flights.ViaID) AND (Airports.AirportID=Flights.DestinationID) AND (Airports.AirportID=Flights.SourceID) ORDER BY Airports.AirportName;

The combobox shows all the airport as is wished but when u select an airport it says: control cannot be edited, it is bound to the expression [Flights]![DestinationID], am i just missing something that I shoudl be doing thats really obvious?

You cannot set the control source to a reference like that

The control source should be just DestinationID and this should be available in the table or query that is bound in the Record Source of the form.

Mary

[nathbooth]

Sorry have sorted that now:)

But i do have another thing i am struggling with instead,
I have this to find the flight ID, is it possible for me to make this into a combo box that runs the query and if there are multiple results you can choose from them with flightDate displayed next to the flight number?;

=nz(DLookUp("[FlightID]","Flights Query","[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)

Thanks
Nathan

[MMcCarthy]

Sorry have sorted that now:)

But i do have another thing i am struggling with instead,
I have this to find the flight ID, is it possible for me to make this into a combo box that runs the query and if there are multiple results you can choose from them with flightDate displayed next to the flight number?;

=nz(DLookUp("[FlightID]","Flights Query","[SourceID]=" & [SourceID] & " AND [DestinationID]=" & [DestinationID]),0)

Thanks
Nathan

Nathan

The DLookup won't work for multiple returns. Set up your combo box and set the Row Source to ...

 
Me.Combobox.RowSource = "SELECT FlightID, FlightDate FROM [Flights Query] WHERE SourceID=" & Me.SourceID & " AND [DestinationID]=" & Me.DestinationID

This will have to be done using code in the AfterUpdate event of the SourceID or DestinationID textboxes. Have a look at this tutorial.

Cascading Combo/List Boxes

Mary