Names in a Combo Box.

[Guest]

I am building a contacts database. It stores the names in three fields; LastName, FirstName, and Title. In my form I am trying to make a Combo Box that will act as a lookup (record navigation) using the names. I am trying to have the box list the names as "LastName, FirstName Title" using the LastName as the Row Source. The box is unbound (no control source)

Any suggestions on how to accomplish this would be greatly appriceated.

 

[Gerald Stanley]

The RowSource SQL should be something like
"SELECT lastName & ", " & firstName & " " & title, lastName
FROM ContactTable ORDER BY lastName & ", " & firstName & "
" & title

The BoundColumn should be set to 2
The ColumnCount should Be set to 2

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I am building a contacts database. It stores the names in

three fields; LastName, FirstName, and Title. In my form I
am trying to make a Combo Box that will act as a lookup
(record navigation) using the names. I am trying to have
the box list the names as "LastName, FirstName Title" using
the LastName as the Row Source. The box is unbound (no
control source).

 

[Guest]

Thanks for the help.

I had to play with your code a little to get it it fit with my form. Here is what I'm using now:

SELECT [Job Search Table 1].[Company Name], [Job Search Table 1].[Contact Last Name], [Job Search Table 1].[Contact First Name], [Job Search Table 1].[Contact Title] FROM [Job Search Table 1] WHERE ((Not ([Job Search Table 1].[Contact Last Name])="")) ORDER BY [Job Search Table 1].[Contact Last Name];

This works but has two problems. The first is that after a selection is made the combo box only displays the last name. The second is that after modifying the pertinent name fields in the form the changes are not reflected in the Combo Box list. I've tried to correct the second issue by putting the following code in the before update field of the Combo Box:

Private Sub Combo181_BeforeUpdate(Cancel As Integer)

Dim SQL As String

SQL = "SELECT [Job Search Table 1].[Company Name], [Job Search Table 1].[Contact Last Name], [Job Search Table 1].[Contact First Name], [Job Search Table 1].[Contact Title] FROM [Job Search Table 1] WHERE ((Not ([Job Search Table 1].[Contact Last Name])="")) ORDER BY [Job Search Table 1].[Contact Last Name]; "

DoCmd.RunSQL SQL

End Sub

This is the same SQL line as used in the Record Source Line.

This code doesn't work. It gives me a syntax error because of the double quotes at the end of the WHERE section. It reads them as an end quotes for the whole string.

Your continued help for this rookie is much appriceated.