Concatenate Fields to Populate Combobox

[RunTime91]

Can't believe I have to bother you guys with this, but after 12 Google searches (and 12 failed explanations)... here I am...



Simple as heck (for you guys, I'm sure)...

My table called: tbl_Employees has two fields: First_Nm and Last_Nm and what I am trying to accomplish is to populate my ComboBox with both the first & last name

I have tried several versions of the code below

SELECT [First_Nm] & " " & [Last_Nm] FROM tbl_Employees ORDER BY tbl_Employees[First_Nm]

Thank you so much for helping out...

[JoeM]

Where are you attempting to combine these? Where does this ComboBox exist (on a Form)?
You wouldn't do that in a Table. Usually, you do it in a Query.

[RunTime91]

Hi Joe ~ and Thanks...

Yes, the CmbBox is on a Form

[June7]

Try adding alias fieldname and space after tbl_Employees in the ORDER BY clause.

SELECT [First_Nm] & " " & [Last_Nm] AS FullName FROM tbl_Employees ORDER BY tbl_Employees [First_Nm];

What do you intend to do with this concatenated value?

[RunTime91]

Hey June ~

I get the following error msg:

Syntax error (missing operator) in query expression 'tbl_Employees [First_Nm]

[JoeM]

Try:

SELECT [First_Nm] & " " & [Last_Nm] AS FullName FROM tbl_Employees ORDER BY 1;

[June7]

Oh crud, my brain crossed wires, not a space but a ! or remove the table name prefix

SELECT [First_Nm] & " " & [Last_Nm] AS FullName FROM tbl_Employees ORDER BY [First_Nm];

[RunTime91]

Get closer perhaps?... It ran without error this time but left an empty ComboBox

[JoeM]

Personally, I prefer to create this in a query, as then use the query itself as the data source of my Combo Box.
I believe that there is a Wizard that will walk you through the set up of the Combo Box.

[RunTime91]

I can't find (or don't know how to invoke) the CmbBox wizard...

I am currently using

Name: [tbl_Employees].[First_Nm] & " " & [tbl_Employees].[Last_Nm]

This works great for combining the two name columns into one but trying to put this or something like this in the row source property... just won't work

I was just informed that this table is a linked table... don't think that matters but in case it does I wanted to provide some additional data.

Thanks...

[June7]

Need "Use Control Wizards" in the Design tab dropdown active then drag combobox from the design tab to form.

Concatenation of fields in a combobox RowSource SQL statement is valid technique - done it.

Still don't know how you intend to use this concatenated value.

Name is a reserved word, should not use reserved words as names for anything - hence my use of FullName as alias.

If you want to provide db for analysis, follow instructions at bottom of my post.

[JoeM]

This works great for combining the two name columns into one but trying to put this or something like this in the row source property... just won't work

Why not try what I suggested, in that creating a query that does this (you already have the code!), and then use the query as the row source property?

If you add a Combo Box Control to your Form, it should automatically invoke the Wizard. Then you just select the Query you created as its source right in the Wizard. I just did it, and it took less than a minute.

[RunTime91]

Update:

This finally worked:

SELECT [First_Nm] & " " & [Last_Nm] FROM tbl_AssocInfo ORDER BY [First_Nm];

Joe...I did try your query idea... and with some modification I was able to get the above to work; unfortunately, I still do no know how to invoke the wizard for a control already on a form

June...The concatenated value is to used as the RowSource for the ComboBox... My apologies for not stating that.

So as it turned out the combobox I was originally working with somehow became 'Bound' (I say somehow became bound because when I started this thread it was unbound) and although I did get this combobox to populate with the above code
when I would select a value from its dropdown I would get an error in the lower left of the access window that read: Control can't be edited; it is bound to an unknown field.

Does anyone know how to 'Unbind' a control or at least find out what it is bound to?

In the end, I just dropped a new ComboBox onto the form, entered the above code into the RowSource property and everything works perfectly.

Thank you everyone for all of your help with this...

[ssanfu]

Does anyone know how to 'Unbind' a control or at least find out what it is bound to?

In form design view, select the the combo box (list box, text box,...), open the property sheet.
Click on the DATA tab.
A bound control will have something in the "Control Source" property.

To unbind the control, delete what ever is in that property.

[June7]

I know you want the concatenated value to show as items in the combobox RowSource. What do you intend to do with the selected value?

Can't invoke the wizard on existing control, only on new one, if the wizard is set as active.