Combo Box Contacts Lookup with concatenation - Pls help!

[Jon-G]

Hi,
Im having a problem with a combo box (cbClientNombre) Im trying to use to pull information from a contact table to a form associated with a plots table.



I have used the wizard and built the control, and at the moment when I drop down the list I can see the data from the contacts table.

Im pulling back all fields in the table and have set the column widths to 0 for the fields other than salutation, name and surname that this query is pulling back. I want to use the other fields to populate text boxes on my form with the telephone number. I have also created a numeric field (called client) in the Plots table, which stores the ID from the Contacts table, and created a relationship between the two. Currently the Row Source query looks like this (as created by the wizard)

SELECT [tblClientes].[ClienteID], [tblClientes].[Saludo], [tblClientes].[Apellido], [tblClientes].[Nombre], [tblClientes].[Telèfono], [tblClientes].[Email], [tblClientes].[Numero_de_casa], [tblClientes].[Calle], [tblClientes].[Zona], [tblClientes].[Colonia], [tblClientes].[Ciudad], [tblClientes].[Departamento], [tblClientes].[Codigo_postal] FROM tblClientes ORDER BY [Apellido], [Nombre];

The first problem that I have is that when I select a client from the contacts table, I just get the salutation displayed in the combo and nothing more. I have tried playing around with the column widths and column count properties to no avail.

I'd also like to be able to concatenate the information from the salution name and surname fields to display in the combo box after the selection has been made. I tried the following but Access throws an error saying there are characters detected after the SQL statement

SELECT [tblClientes].[ClienteID], [tblClientes].[Saludo] & " " & [tblClientes].[Nombre] & " " & [tblClientes].[Apellido] As [Buyer Full Name], [tblClientes].[Telèfono], [tblClientes].[Email], [tblClientes].[Numero_de_casa], [tblClientes].[Calle], [tblClientes].[Zona], [tblClientes].[Colonia], [tblClientes].[Ciudad], [tblClientes].[Departamento], [tblClientes].[Codigo_postal] FROM tblClientes ORDER BY [Apellido];

I have also tried adding the following to the control source of the 'telephone number' unbound textbox on the same form, but this didnt work:

=[cbClientNombre].[Column]([5])

I have searched and searched trying to find an example of how to do this correctly (which I thought would be all over the net!)

Any help would be greatly appreciated!

[ketbdnetbp]

...Currently the Row Source query looks like this (as created by the wizard)

Nothing in box to view!
Can you post the RowSource?


...I tried the following but Access throws an error saying there are characters detected after the SQL statement

Nothing in box to view!
Can you post the SQL?


Lastly,

...=[cbClientNombre].[Column]([5])

try, =[cbClientNombre].Column(5)

All the best,

Jim