Combining firstname and lastname in combo box

[Yogin]

Hi,

i have a main table which consist of firstname and last name in 2 separate
fields. What i'm trying to do is that when selecting the name in the combo
box, I would like the full name to appear.

Eg

Field 1 -- Field 2 -- Combo box
John --Smith -- John, Smith

Can someone help please.

Thanks

Yogin

 

[Daryl S]

Yogin -

Update the recordsource for the combo box, then adjust the properties of the
combo box to show what you want. You can show only the combined name using
column widths of 0" for the first two fields.

The recordsource query would be something like this:

SELECT FName, LName, FName & ", " & LName
From tableName

 

[Yogin]

Hi Daryl,

Thanks for the quick reply. I'm a complete newbie on Access and learning
with support from people on this forum.

When I click properties for this combo box to input the fomula, I cannot see
the recordsouce, I do see controlsource.

I tried to put the formula in there and I get an error.

This is what I put in controlsouce field of the combo box
SELECT FirstName, LastName, FirstName & ", " & LastName
From tblColleague

clearly i'm doing something wrong?

Can you help please

Thanks

Yogin

 

[John W. Vinson]

Hi,

i have a main table which consist of firstname and last name in 2 separate
fields. What i'm trying to do is that when selecting the name in the combo
box, I would like the full name to appear.

Eg

Field 1 -- Field 2 -- Combo box
John --Smith -- John, Smith

Can someone help please.

Thanks

Yogin

Yor table design may need some rethinking! Names are NOT unique: I once worked
at a university with a professor John W. Vinson. You should have a unique
PersonID primary key in the table.

That said, you can create a Query based on your table. In the first column of
the query grid put the unique ID field (this is what the combo should be
storing); in the second column put

FullName: [Field1] & ", " & [Field2]

to construct a string "John, Smith" (is that really what you want?? More
common would be "John Smith" or "Smith, John").

Put the two fields in the third and fourth columns in the order that you want
the names sorted, and select Ascending as the sort order.

You can then use this Query as the row source of your Combo Box. If you set
the column count to 2 and the ColumnWidths property to

0.0";1.5"

you will *see* the name, but the combo will store the ID.

 

[Duane Hookom]

The SQL goes into the Row Source property since there is no Record Source on
combo boxes.

 

[Daryl S]

Yogin -

Sorry, the row source, not record source is where the SQL goes...

 

[Yogin]

Guy's, Thank You for your help.

The solution works well.

Hi,

i have a main table which consist of firstname and last name in 2 separate
fields. What i'm trying to do is that when selecting the name in the combo
box, I would like the full name to appear.

Eg

Field 1 -- Field 2 -- Combo box
John --Smith -- John, Smith

Can someone help please.

Thanks

Yogin

Yor table design may need some rethinking! Names are NOT unique: I once worked
at a university with a professor John W. Vinson. You should have a unique
PersonID primary key in the table.

That said, you can create a Query based on your table. In the first column of
the query grid put the unique ID field (this is what the combo should be
storing); in the second column put

FullName: [Field1] & ", " & [Field2]

to construct a string "John, Smith" (is that really what you want?? More
common would be "John Smith" or "Smith, John").

Put the two fields in the third and fourth columns in the order that you want
the names sorted, and select Ascending as the sort order.

You can then use this Query as the row source of your Combo Box. If you set
the column count to 2 and the ColumnWidths property to

0.0";1.5"

you will *see* the name, but the combo will store the ID.