Combobox: is there a way to select which column is displayed?

[PeakH]

I have a combo box with five rows, three of which are visible.

The columns are LotID (Bound Column, not displayed), RoomName, LotNumber, ListOrder(not displayed, only for ordering purposes), and StrainID.

When the record is selected from the combobox, the default is to dislpay the first visible column (RoomName), but I would like it to display the second visible column (LotNumber). I could switch the order of the columns, but it is not as intuitive that way, since the list is ordered by RoomName (actually ordered by ListOrder, but that is directly linked to RoomName).

[ranman256]

in combo box property : COLUMN WIDTHS,
you can set what to see: 1;0;0;.5

the 1st col is 1inch
col 2,3 are hidden,
col 4 is half inch.

[PeakH]

Yes, I have the column widths set to what I want.

It's after the record is selected and the combo box displays that selected record, it displays the first visible column. In your example, the list would display two columns, but after the record is selected, it would only display the field in the 1" column. I want mine to display the second visible column.

I hope I'm making sense.

[ranman256]

then re-arrange the source query, so your wanted item is col 1

[PeakH]

I'm hoping there is a way around that.
I certainly could but the list with a different order is not as intuitive, and because I'm making the database for others who are not computer savvy, I would like to make it as intuitive as possible.

[ArviLaanemets]

My advice is - never use multi-column combos. When you want several field values displayed, use calculated column. Something like:
SELECT ID, Field1 & "; " & Field2 & "; " & Field3 From YourTable [WHERE YourCondition = True] [ORDER BY Field1, Field2, Field3]
or, when you want it look more table-like
SELECT ID, Left(Field1 & Space(10),10) & "; " & Left(Field2 & Space(15),15) & "; " & Field3 From YourTable [WHERE YourCondition = True] [ORDER BY Field1, Field2, Field3]
Set column widths p.e. to 0, 2.5 (with single visible column in combo, the non-zero width indicates only, that the column is visible)

And keeping control source field as first is more intuitive - it is the real value of combo. When you want to display it too, include it into calculated field.

[PeakH]

If I change it from a multi-column combo would that go in the Control source? I know I'm writing it wrong, but I'm playing around with it and can't get it to list anything. It's just blank.

SELECT [T-Lot].LotID, [T-Lot].LotNumber & "; " & tblInventoryRooms.ListOrder & "; " & [T-Strains].StrainName & "; " & tblInventoryRooms.RoomName FROM tblInventoryRooms INNER JOIN ([T-Strains] INNER JOIN [T-Lot] ON [T-Strains].StrainID = [T-Lot].StrainID) ON tblInventoryRooms.RoomID = [T-Lot].Room [ORDER BY tblInventoryRooms.ListOrder, [T-Strains].StrainName]

Why do you advise to never use multi-column combos?

And the reason it's not intuitive to use the real value of the combo as the first column is because it's a very long list of product numbers that look like gibberish. I want most comprehensive category to be the first thing they see to more easily navigate to the correct gibberish.

[ssanfu]

If I change it from a multi-column combo would that go in the Control source?

No, it goes in the combo box "Row Source"


Well, the error I see is the brackets around the ORDER BY clause

SELECT [T-Lot].LotID, [T-Lot].LotNumber & "; " &  tblInventoryRooms.ListOrder & "; " & [T-Strains].StrainName  & "; " & tblInventoryRooms.RoomName 
FROM tblInventoryRooms INNER  JOIN ([T-Strains] INNER JOIN [T-Lot] ON [T-Strains].StrainID =  [T-Lot].StrainID) ON tblInventoryRooms.RoomID = [T-Lot].Room 
[ORDER BY  tblInventoryRooms.ListOrder, [T-Strains].StrainName]

If you paste the above in a query, after deleting the brackets in red, it should return records.
Why are you including "ListOrder" in the 2nd column calculation? You said it was not displayed.

SELECT [T-Lot].LotID, [T-Lot].LotNumber & "; " & [T-Strains].StrainName   & "; " & tblInventoryRooms.RoomName AS LotStrainName
FROM tblInventoryRooms INNER  JOIN ([T-Strains] INNER JOIN [T-Lot] ON  [T-Strains].StrainID =  [T-Lot].StrainID) ON tblInventoryRooms.RoomID =  [T-Lot].Room 
ORDER BY  tblInventoryRooms.ListOrder, [T-Strains].StrainName

After the query returns records, save the query and use it in the combo box "Row Source".
Or paste the SQL into the combo box "Row Source".

Then set these properties:
Set the
bound column to 1
column count to 2
column widths to 0


I also never use a multi-column combo box. The reason is I want to be able to see what I selected. It is like having a table of employees, with fields for First and Last names.
When you open the dropdown list you can see both first and last names, but after selecting a name, you only see either the first name or the last name, but not both.
If you concatenate the first and last name fields, you will be able to see the full name.

[PeakH]

Ah hah! Since I couldn't get it to work before I didn't really get what it was supposed to do which is why I still had the ListOrder in there. SQL is still a bit of a mystery to me, but now I get it and ListOrder is gone.

This is a good solution and will work well.

Thank you!

[ssanfu]

You are welcome.

To help learn more about SQL, create queries in the query designer, then switch to SQL view and look at how the SQL is formed. And read about the SQL language.