Combo box displaying column, despite width = 0

[PRMiller2]

This is a weird one. I have a combo box called cboProperty. It is populated by a query displaying five columns. My list widths are set to hide the first column (the primary key) and display all others. The list displayed when a user opens the combo box is fine. However, once they select an item, the control displays the primary key, not the other fields. Here's the query:

SELECT property.pk_propertyID, property.streetNumber AS House, IIf([direction1] Is Null,"",[direction1]+" ")+IIf([streetName] Is Null,"",[streetName]+" ")+IIf([streetSuffix] Is Null,"",[streetSuffix]+" ")+IIf([direction2] Is Null,"",[direction2]+" ") AS Street, property.unitNumber AS Unit, city.cityName AS City
FROM property 
LEFT JOIN city ON property.fk_CityID = city.pk_CityID
ORDER BY property.streetName, property.streetNumber, property.unitNumber;

Here are the Format values for the combo box:
Column Count: 5
Column Widths: 0";0.65";1.65";0.65";1.5"
Column Heads: Yes
List Rows: Yes
List Width: 4.375"
Separator Characters: System Separator
Width: 4.375"

Date values:
Control Source: fk_PropertyID
Row Source: qry_SelPropertyByStreet
Row Source Type: Table/Query
Bound Column: 1

I can't figure this one out. Worse, there's another combo box on the same form that displays a different value just fine. What am I missing?

 

[MajP]

Not weird, that is the expected behavior of a combo. It will show multiple fields in a pulldown but only display the first visible field once selected. You would have to use a listbox for that behavior.

But your concatenated field is the second visible field, make it your first visible field.

 

[PRMiller2]

My bad, I was incorrectly assuming the combobox was displaying pk_propertyid instead of house.

I originally had a concatenated field that displayed streetNumber, direction, streetName, streetSuffix, direction2 and unitNumber all together. However, that was not too legible for the user, so we split them to make it easier to scan a large list.

If I switch that to a list box instead of a combo box, can I still bind one field to the underlying table?

 

[MajP]

Yes you can bind it to a field like a combo. You can write some code to fake the listbox to drop down like a combo if real estate is an issue.

 

[PRMiller2]

Interesting... I haven't run across that before. Would you mind posting a link or a sample of some code to make a listbox perform that way?

 

[MajP]

I built a demo at one time. Let me see if I can find it and post it.

 

[MajP]

http://www.4shared.com/document/pIq9pPSm/MultiColumnCombo.html

I will keep looking for the listbox solution, but here is another fake. It works as well.

What you do is lay textboxes over top of the combo.
1)Make sure the textbox are brought to front
2)Lock the textboxes
3)size everything so only the down arrow shows

Be careful with the forms query. Likely will need to be a left join to ensure you show all records.

This trick works also for cascading combos on a continous form.

 

[PRMiller2]

MajP, great tip on hiding the combo box with a text box. I'm really interested in the listbox solution if you can find it!

 

[MajP]

BTW, that link is to a demo database showing the fake. It works pretty well. I will tweak it some more so that if you type into the combo it will auto find. What is neat about that is that you can then auto find on multiple fields. In other words if you type in the name column it will find by name, or if you type in the address it will match by address.