Showing first and last name after selecting choice in combo box

[jmuirman1]

I've seen solutions on line but just don't understand.



I select the client and see the first and last name. but after selecting, i only see the last name. here is the row source:

SELECT [tContacts].[Client ID], [tContacts].[CLastName], [tContacts].[CFirstName], [tContacts].[CClient] FROM tContacts ORDER BY [CLastName];

Bound column is set to 1. when i change to 2, i just see the ClientID number.

Thanks,

John

[davegri]

SELECT [tContacts].[Client ID], [tContacts].[CLastName] & ", " & [tContacts].[CFirstName] as LastFirst, [tContacts].[CClient] FROM tContacts ORDER BY [CLastName]; 

Try this.

[Micron]

I've seen solutions on line but just don't understand.

A combo list can show as many columns (which usually refer to table or query fields) as you want but the control can only show a value from one column without concatenating multiple columns into one. In effect, that's still only one column - a single column of concatenated values from other fields. I figured I'd better elaborate on that or I'd get some disagreement. Also, the control can only be bound to one field and is a 1 based collection. Column(0) is available, but I rarely use it because it provides the list row index of the selected record.

Bound column is set to 1. when i change to 2,

This sounds like you are showing the ID column, which is usually not done. While the ID is often part of a combo, listbox, query etc. it's typically never shown to the user in order to avoid confusion and questions about it. In order to bind a combo selection of "Smith" to ID 55, the ID is the first field in the query or record set, it's column width is set to 0 so as to hide it, and the bound column is set to 1. The order of list columns will match the order of the query fields.

[jmuirman1]

how do i select the name but show the first and last?

[davegri]

how do i select the name but show the first and last?

What's the purpose of the combo box?
To look up an existing record to show on the form?
OR Enter the dropdown name into an existing record being currently shown on the form?

[Micron]

Did you try the suggestion? Answer would be to use the sample row source sql provided.
You'll see a column with first and last names together and select the pair that you want. In the list you could show last, first and the concatenated pair as 3 columns if that's what you're asking, but that doesn't make sense to me.

[June7]

Combobox properties:

RowSource: SELECT [Client ID], CLastName & ", " & CFirstName AS FullName, CClient FROM tContacts ORDER BY CLastName, CfirstName;
ColumnCount: 3
ColumnWidths: 0",1.5", 1.5"
BoundColumn: 1
ControlSource: name of field saving ID into if needed


Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

[jmuirman1]

perfect! that worked.

thanks,

John