How to return table ID and Name from a linked table in a query

My DB (below) contains a complex (SQL code wise) query “qryTripSectors” ( thanks to @Ratslinger’s assistance ) which is working as I want.

A form “TripSectors” based on the table “TripSectors” which is used to enter information such about each part of a trip. TripLogID and PreviousAccommodationID are selected using Listboxes on the form.

To display trip details for each TripLogID the form “Trips” is used with controls linked to the Table “TripSegments” and Subform controls linked to the query “qryTripSectors” mentioned previously.

This query returns previous values from the table “TripSectors” and also formatted and calculated fields for use in the form “Trips” pic below!

There is no need to use a listbox in the Previous Accommodation details section of the form. I have attempted basing “qryTripSectors” on a new Query using " *.TripSectors " and “Accommodations”.“Name” without success. It returns DATA CONTENT COULD NOT BE LOADED Errror (Cannot be in ORDER BY clause in statement).

Am I making this too complex, is there some other way to do this. I would rather use a Textbox on the form “Trips” Depart Details section in place of the Listbox “PrevAccom”.
How can I display the name of the Accommodation not the ID number using a Textbox. Or is there a way to display the contents of the Listbox similar to a Textbox. Tried turning off the dropdawn and disabling the tab stop but then it looks weird with a slidebar at the bottom. See Pic.




Have actually provided this information before in another sample → Query previous record for form control in sample: EditedFromMycle.odb in Query1.

Have taken this and applied to the attached sample. Form is TripsModified and SubForm1 uses query qryTripSectors3.

Sample ---- NameVsID.odb

Thank you, I had been mucking around with the query and lost it obviously.