Unbound Combo Box with 2 Columns

[dftjsn]

I've got an unbound combo box with 2 columns, state and store name. The combo box is sorted by the first column, state, and then the second column, store name. The bound column is the second column, store name. When the user selects an item from the combo box, I want the combo box control to show the second column. Unfortunately, it will only display the first column. Examining the contents of the combo box in the AfterUpdate event reveals that cbobox.value is getting set to the second column, store name, but cbobox.text contains the first column, state and thus state is displayed in the text portion of the combo box. I've tried assigning cbobox.text=cbobox.value but that generates a "Limit to List" error since Access seems to want the list to always be limited to the first column.

Does anyone have any suggestions for making the second column, the bound column, show up in the text portion of the combo box rather than what is in the first column?

Thanks!

dftjsn

 

[Trendsetter]

If I understand you correctly, you need to set the column widths to 0;3.5 - this way the combo only shows the content of the second column.

 

[dftjsn]

Thanks for the response Trendsetter. I do want to show both columns because I want users to see what state the particular store is in. I could move the store name into the first column and the state in the second, but it seems more natural to see the state first. I could also have a separate box where the user picks the state first, but in my application, the user doesn't necessarily know the state, so I want to show all stores and their states. Any other thoughts?

dftjsn

 

[Trendsetter]

How about letting the user select how he wants to see the list - State > Store ... RowSource = Query 1
Store > State ... RowSource = Query 2

 

[dftjsn]

Thanks again Trendsetter. I can think of lots of work arounds ... I just can't quite figure out why it doesn't work the way I reckon it should. Why doesn't the second (the bound) column get filled in the text portion of the combo box?