Display multiple fields in combo box

[Guest]

I have a combo box with three columns in the row source. All three columns
are displayed in the combo box drop-down. After the user makes a selection,
I would like the first two columns of info to be displayed in the box.

I see that back in December, someone posed a very similar question (but with
2 columns rather than three). At that time, George Nicholson responded with
the following:
---
A combo box can only show a single value, however, you can combine multiple
values to create that value.

Create a 3rd column for your combobox: ColumnAData & " " & ColumnBData

Set ColumnWidths to 0,0,2" (or however wide it needs to be). Only the 3rd
column will display

Not sure what, if anything, you want to use as the BoundColumn, but all 3
values are available to code (or queries):
MyCombo.Column(1), Column(2) and Column(3).
---

My question is…WHERE do I create that third column? In a new query? In the
original table? Any other details would surely help. Thanks!

Diane

 

[Jeff Boyce]

A combo box control displays a single column. What you include in that
column is up to you. You can use one column to display one field from the
underlying source. Or you can concatenate values from several fields (e.g.,
City, State PostalCode) into a single field in your source.

Another approach would be to keep columns in your combo box displaying only
one field each, but adding one/more text boxes (unbound) to your form.
Then, in the combo box's AfterUpdate event, you could set the text box(es)
value to specific columns from the combo box's source.

By the way, if you use the
Me.cboYourComboBox.Column(n)
approach, be aware that the .Column() method is zero-based ... you start
counting at 0, not 1.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Guest]

Jeff,
Thanks for your response. But exactly WHERE should I concatenate the fields?
- Diane

 

[HKComputer]

I don't quite get it. I can display:
Customer | City | State

all in the same combo based on a query. Its true that only one of the
columns is bound to my source, i.e. only one will be saved into the
field it's bound to.

Something was wrong with your column settings further up. Should look
like this.

0";1.5";1.5";3"

That would represent 4 columns where you don't want the first to
display.

Remember to set the overall all column with to reflect your per-column
setting. You can leave it on Auto, but out of habit, I usually don't.

 

[Guest]

Thanks Jeff. I still couldn't get the concatenation to work, because I
couldn't figure out WHERE to do it. Nothing I tried seemed to work.

However, I went with your second idea, and it worked great. I shrank down
the width of the combo box to just the arrow (except when it's selected it
shows all three columns). Then when the selection is made from the combo
box, the two fields I want to display are in a text box right next to the
combo box arrow. So it even looks good, even though it's a workaround.

And thanks for the info on the zero-based column -- that could have driven
me crazy for hours!

Thanks for your help!
Diane

 

[Jeff Boyce]

Combo boxes have sources, often a query. Add a new field to the underlying
query, something like:

[City] & ", " & [State] & " " & [PostalCode]

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor