Showing multiple fields in combo box display (1 Viewer)

jobrien4 · Sep 21, 2011

I have a combo box for CustomerNumber that is a lookup from another field. When the user clicks on the down arrow, it shows CustomerNumber (the primary key) and in the next column CustomerName.

However, when the user selects one, it only shows the Customer Number in the combo box display. Is it possible to make it show both Customer Number and Customer Name in the display?

Mr. B · Sep 21, 2011

It is not possible to display both values using a combo box. it is possible to display the "CustomerName" field and not see the "CustomerNumber " field. Assuming that you have 2 columns defined as the column count, and the bound column is the "CustomerNumber" field, you can hide the first column by setting the column width of the first column to 0 in the Column Widths property of your combo box. If you have more that two columns defined in your combo box you can also specify the width of each column by adding values after the starting zero to specify how wide each column should be. These widths only apply to the width of the columns displayed when the combo box is dropped down.

You will need to use a Listbox and display multiple columns after a selected is made.

jobrien4 · Sep 21, 2011

I guess my goal is to display both customer number and customer name at the top after the user makes a selection.

What would be the best way to do this then?

Here is what I'm thinking but I'm not sure exactly how to do this:

1. Create a query that shows Customer Number and Customer Name (qry_CustomerName)
2. Run the query AfterUpdate on the combo box
3. strCustomerName = query result (here is how I'm not sure to do it)
4. Label.Cpation = strCustomerName
5. DoCmd.Close qry_CustomerName

Is there a better or simplier way?

Mr. B · Sep 21, 2011

There is a simpler way.

If you have the fields being returned by your combo box, then you can display the selected "customNumber" in the combo box and then create a text box on the form and set the record source of the text box to: =[NameOfYourCombobox].[column](1)

The number value at the right of the "column" is a zero based numbering of the columns available in your combo box. You would also need to set the "Locked" property of the text box to Yes to keep users form trying to change this value.

vbaInet · Sep 21, 2011

I know Mr.B has kindly given you an alternative, but here's how you can achieve your original request.

In the Combo box:

Column 1: [CustomerNumber]
Column 2: [CustomerNumber] & " " & [CustomerName]

This will go in the combo box's Row Source.

Mr. B · Sep 21, 2011

vbaInet, that is a very neat approach to providing a solution to the issue. Don't know why I did not think of that. Thanks for the pointer.

Brando · Jan 29, 2015

vbaInet said "This will go in the combo box's Row Source." I wonder if someone could explain how to put it there considering the Row Source has only one line in properties?

Thank you in advance.

Brando

pbaldy · Jan 29, 2015

I believe what was meant was that the row source would be:

SELECT [CustomerNumber], [CustomerNumber] & " " & [CustomerName] AS BothValues
FROM TableName

The customer number would be the bound column (what gets saved), but could be hidden so the user sees the concatenated value.

Brando · Jan 29, 2015

Thank you Paul. I put it in the row source and the concatenated value appeared in the dropdown of the combo box. However, as with the original post above, I'm trying to have the user's selection appear in the combo box after they've selected it - with both the number and name appearing. As I read this thread, that's what vbaInet was suggesting was possible.

Right now the dropdown has the following example:

403 403 John Smith

But when this is selected from the dropdown, the following appears in the combo box:

403

Here's what I want to appear in the combo box after selection:

403 John Smith

Thank you again.

bob fitz · Jan 29, 2015

Set the Column Widths property of your combo box to: 0;2

pbaldy · Jan 29, 2015

As Bob said, which is what I meant by

pbaldy said:
could be hidden so the user sees the concatenated value.

Brando · Jan 30, 2015

It works perfectly. Thank you both for your time!

Brando

pbaldy · Jan 30, 2015

Happy to help!

CJ_London · Jan 30, 2015

since this thread has revived, I thought I would put in my twopenneth

Using the space function will ensure customer names line up if the length of customernumber varies. I've used 8 but any number that suits is OK so long as it is more than the longest customernumber.

Code:

 SELECT [CustomerNumber], [CustomerNumber] & [COLOR=red]Space(8-len([CustomerNumber]))[/COLOR] & [CustomerName] AS BothValues
FROM TableName

cdoner · Dec 8, 2021

I was able to use this method to work around a union query not allowing me to execute a calculated field in a continuous form. So glad I found it and a big thank you Mr. B.

Quoting Mr. B above:

If you have the fields being returned by your combo box, then you can display the selected "customNumber" in the combo box and then create a text box on the form and set the record source of the text box to: =[NameOfYourCombobox].[column](1)

The number value at the right of the "column" is a zero based numbering of the columns available in your combo box. You would also need to set the "Locked" property of the text box to Yes to keep users form trying to change this value.

Pat Hartman · Dec 8, 2021

Except that vbalnet's solution is better.

Showing multiple fields in combo box display (1 Viewer)

jobrien4

Registered User.

Mr. B

"Doctor Access"

jobrien4

Registered User.

Mr. B

"Doctor Access"

vbaInet

AWF VIP

Mr. B

"Doctor Access"

Brando

Enthusiastic Novice

pbaldy

Wino Moderator

Brando

Enthusiastic Novice

bob fitz

AWF VIP

pbaldy

Wino Moderator

Brando

Enthusiastic Novice

pbaldy

Wino Moderator

CJ_London

Super Moderator

cdoner

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread