2-Column Combo box not displaying the second column when not selected (1 Viewer)

[mafhobb]

So I have this two-column combo box which uses a query as source. This is the query:

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));

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

 

[Minty]

You haven't used a lookup field in your table by any chance, have you?

Your column widths should be 0 ; 5 or something similar to hide the first column, but your bound column should still be 1.
Make sure your column count is 2.

 

[cheekybuddha]

SELECT RoomNumber, RoomNumber & " " & RoomName

 

[Gasman]

Concatenate the room number and name into another field and show that field. ?

 

[mafhobb]

You haven't used a lookup field in your table by any chance, have you?

Your column widths should be 0 ; 5 or something similar to hide the first column, but your bound column should still be 1.
Make sure your column count is 2.

No, I have not used lookup on the table
Yes, I have tried to adjust the column widths (and set the first one to 0) and the only result is that the first column (RoomNumber) disapeared, but the second column was still blank. Jut to make it clear, the combobox is working fine when I have it selected, but it does not show the second column when it is not selected.

mafhobb

 

[June7]

Could provide db for analysis.

 

[mafhobb]

Sure.
The issue is in the form "Room Reservations" in the combobox under "Room"
mafhobb

 

[June7]

I really should have recognized this issue from the query posted, even before viewing db.

Combobox RowSource is filtered to only list rooms that are vacant. This means the RoomName alias for reserved room is not available for display. This is a 'cascading' or 'dependent' combobox. What you observe is a known issue with cascading combobox that uses alias. There is no really nice way to deal with, just some workarounds.

1. only filter combobox list for a new record entry

2. include Rooms table in form RecordSource and bind a Locked textbox with TabStop No to RoomName field and sit this textbox on top of combobox

 

[mafhobb]

I am afraid that this is a bit over my level...

-How can I include the Rooms table in the subform's recordsource if the recordsource is a query?

SELECT RoomOccupations.* FROM RoomOccupations ORDER BY RoomOccupations.EntryDate;

Mafhobb

 

[cheekybuddha]

Did you try the suggestion in Post#3 ?

What happened?

 

[MajP]

Something like this?