Combo box to display both fields that feature in drop-down..? 1

[andagain]

Hi everyone,

I have a subform in my form. It has a field ClientName (combobox) that shows records from tblClients - showing the fields ClientFirstName, ClientFamilyName, ClientID in the drop down. Fine. But when I choose a record from the drop down it only displays the ClientFamilyName in the control. How do I get it to display both first name and lastname?

TIA

Andrew.

 

[LittleSmudge]

First off the combo box is a CONTROL not a field

A combo box control is a COMBination of a text box and a list box

In the same way that a text box only displays the contents of one field - a combo box only displays the contents of one field

However - there is a way to 'con' it

Set the Row source of the combo box to

SELECT ClientID, ClientFirstName & " " & ClientFamilyName FROM tblClient

Set the Column Widths to 0;
Set Column Count to 2
Set Bound Column to 1

The combo box then thinks that it is getting 2 olumns - the Id and the full concatenated name

Because the first column width is set to zero it will not show it - but it's still there so that you can refer to it. Once collapsed it will display the full concatenated name.

'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[scriverb]

This has to do with the RowSource property. The way I usually do this is create a new column called Name which holds the cancatenation of first and last names. That is what is shown and picked and ultimately shown in the combobox. The clientID then would probably be the BoundColumn.

SQL code for the RowSource of the ComboBox

Select A.[ClientID], A.[ClientFamilyName] & ", " & A.[ClientFirstName]  & " -(" & A.[ClientID] & ")" as [ClientName] 
FROM tblClients as A 
ORDER BY A.[ClientFamilyName] & ", " & A.[ClientFirstName]  & "-(" & A.[ClientID] & ")";

This SQL will display the following in the dropdown:

Scriver, Robert -(A123455Z)

Now in the combobox set the BoundColumn to [highlight]1[/highlight], ColumnWidths to [highlight]0;2.5"[/highlight] When the combobox displays the all of your info in alpha name order with the ClientID identified on the right end. The user can type in the FamilyName and the combo will search alphabetically for it.

The combo value will be the ClientID as we have identifed column 1 as the bound column.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[scriverb]

Referring to my posting above if however your users are wanting to select by ClientID then the second column of the SQL could be changed to start with the ClientID:

Select A.[ClientID],A.[ClientID] & " - " A.[ClientFamilyName] & ", " & A.[ClientFirstName] as [ClientName]
FROM tblClients as A
ORDER BY A.[ClientFamilyName] & ", " & A.[ClientFirstName]  & "-(" & A.[ClientID] & ")";

This would display as follows:

A123456Z - Scriver, Robert

Now the user could be entering the value of the ClientID to find the selection rather than FamilyName alpha. Either will work but all the info from the selection will be displayed after the pick.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[andagain]

Thanks guys.

Your solutions worked a treat.

Cheers,

Andrew.

 

[misscrf]

thanks little smudge. That is exactly what I needed for my forms!


misscrf

It is never too late to become what you could have been ~ George Eliot