So I have this two-column combo box which uses a query as source. This is the query:
The query returns a room number and room name.
When I click on the combo box I can see numbers and names as expected, but once a line is selected, then only the number shows up in the combo box, not the name. I know that a combo box only displays one column when not selected, so I tried to set the first column to zero length in an effort to shown the names (column 1) and not the numbers (column 0) but that just does not seem to work as I get a blank combobox. Then I tried to add a textbox and set its source to the second column, but that did not work either. I even tried to reverse the two variables in the query (Roomnumber and Roomname) and that reverses the query order, but the combo box still only shows "RoomNumber". What am I doing wrong?
Thanks
Mafhob
Code:
PARAMETERS Forms!frmOccupants!sfcReservations!EntryDate DateTime, Forms!frmOccupants!sfcReservations!DepartureDate DateTime;
SELECT RoomNumber, RoomName
FROM Rooms
WHERE NOT EXISTS
(SELECT *
FROM RoomOccupations
WHERE Rooms.RoomNumber = RoomOccupations.RoomNumber
AND WithinDateRange(Nz(Forms!frmOccupants!sfcReservations!EntryDate,#1900-01-01#),
Nz(Forms!frmOccupants!sfcReservations!DepartureDate,#2100-01-01#),
EntryDate,DepartureDate));
When I click on the combo box I can see numbers and names as expected, but once a line is selected, then only the number shows up in the combo box, not the name. I know that a combo box only displays one column when not selected, so I tried to set the first column to zero length in an effort to shown the names (column 1) and not the numbers (column 0) but that just does not seem to work as I get a blank combobox. Then I tried to add a textbox and set its source to the second column, but that did not work either. I even tried to reverse the two variables in the query (Roomnumber and Roomname) and that reverses the query order, but the combo box still only shows "RoomNumber". What am I doing wrong?
Thanks
Mafhob