How to get Combo Box 'always' display Multiple Fields

[Will]

I can create my combo box to display multiple fields when the client click
on it & it drops down but...

How can I have it display Multiple Fields always?

In my case I'm having the user select from a list of names and the fileds I
have are...

idName, Fname, Mi, Lname

Combo box works great but after the user selects the name the Combo Box will
only display one of the fields...

So I have to create additional fields on the form so that it will show
Fname, MI, & Lname... confusing to the user and a waste of space.

It works... but it Ain't Purty

thanks for any help.

 

[Allen Browne]

Change the Row Source property of the combo to a query statement that
concatenates the fields into one, e.g.:
SELECT [idName], Trim([Fname] & " " & [Mi] & " " & [LName]) AS FullName
FROM Table1;

Then set these properties for the combo:
Column Count 2
Column Widths 0
The first column is zero-width, so does not show.
The next column contains all the names in one field, so that's what shows,
even when the combo is not dropped down.

 

[Will]

OK, I figgured it out... and for anyone else interested... here is how to do
it...

You must "Concatenate" or 'join together' fields in the SQL query... not as
hard as you think...

Create your Combo Box using the menus and wizard in Access... and then look
at the properties...

On the Data Tab you will find a line named Row Source...

This contains the SQL query that selects the data that will be displayed in
the Combo Box...

You may see something like...

SELECT tblNames.FName, tblNames.MI, tblNames.Lname, tblNames.idName FROM
tblNames ORDER BY [LName];

This tells Outlook to select the Fname, MI (middle initial), Lname, and the
idName fields from the table named tblNames... and to display them... all in
4 columns...

OK, you will see all fields when you click on the Combo Box but you won't
when the Combo Box doesn't have the Focus... that is when you are clicking
on something else... then you will only see one field... not always cool for
the user.

To fix this modify the SELECT statement above like the below...

SELECT tblNames.FName+' '+tblNames.MI+' '+tblNames.Lname, tblNames.idName
FROM tblNames ORDER BY [LName];

Note the + signs added.... and all before the first comma... this means in
the first column of the Combo box to display...

the Last name
followed by a space
followed by the Middle Initial
followed by a space
followed by the last name...
and to display them all in the first column of the combo box...

the id of the Name (idName) will be displayed in the 2nd column (or you can
hide it... by making its width 0 inches on the Format Tab at the Column
Widths line)

And, the name list in the combo box will be sorted alphabetically by last
name.

Cool... huh?

Now your user can see the entire name... or any combination of fields you
want... in a combo box... even when it doesn't have the focus...

Hope this helps someone else save some time in creating their database and
makes it more user friendly.

Will

 

[Will]

OK Allen... good answer and shorter than mine that I posted about the same
time you were posting yours...

But, I'm still having one issue...

There is something about the MI field... on some records only... that will
prevent the Combo Box from displaying 'anything' for that record...

I think it may have to do with weather or not there was ever any data in the
MI field...

So maybe this is an "empty field" vs a "null field" issue.

I started to change the property of the field in the table to not allowing
zero lenght fields... but then Access told me that may do something to my
database so I didn't change the property...

What do think this may be?

thanks for the Help

Will

Change the Row Source property of the combo to a query statement that
concatenates the fields into one, e.g.:
SELECT [idName], Trim([Fname] & " " & [Mi] & " " & [LName]) AS FullName
FROM Table1;

Then set these properties for the combo:
Column Count 2
Column Widths 0
The first column is zero-width, so does not show.
The next column contains all the names in one field, so that's what shows,
even when the combo is not dropped down.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can create my combo box to display multiple fields when the client click
on it & it drops down but...

How can I have it display Multiple Fields always?

In my case I'm having the user select from a list of names and the fileds
I have are...

idName, Fname, Mi, Lname

Combo box works great but after the user selects the name the Combo Box
will only display one of the fields...

So I have to create additional fields on the form so that it will show
Fname, MI, & Lname... confusing to the user and a waste of space.

It works... but it Ain't Purty

 

[Van T. Dinh]

Use "&" concatenation operator like Allen's rather than "+" concatenation
operator in your SQL to fix the problem.

The reason is that + propagates Null while & doesn't, i.e.

AnyString + Null gives Null
AnyString & Null gives AnyString

 

[Will]

Van,

Thanks!

Will

Use "&" concatenation operator like Allen's rather than "+" concatenation
operator in your SQL to fix the problem.

The reason is that + propagates Null while & doesn't, i.e.

AnyString + Null gives Null
AnyString & Null gives AnyString