Merging First and Last Name in ComboBox 4

[Elvis72]

Good Morning!~

I have the following Tables:

TblResumes
WorkerID
Last Name
First Name
Cell Phone
Email Address

TblProjectInformation
ProjectID
WorkerID
TitleID
Job/ProposalNo

TblConstructionServices
Job/ProposalNo
Client
Title

TblStaffRequirements
TitleID
Title

The TblConstruction Services is the Main Form

The TblProjectInformation is the Subform

The Worker ID is linked to the TblResume WorkerID field.

This is the Original RowSource for WorkerID:

SELECT TblResumes.WorkerID, TblResumes.[First Name], TblResumes.[Last Name] FROM TblResumes ORDER BY [Last Name];

Then to resolve the issue of the other fields not auto-populating on the selection of the worker in the WorkerID combobox I created this on the AfterUpdate:

[Forms]![FrmConstructionServices].[Form]![Job/ProposalNo].Requery

BUT I really like the idea of merging the First and Last Name in the WorkerID field better...saves redundant fields on the form.

This is what I have which works fine:

SELECT TblResumes.WorkerID, TblResumes.[Last Name] & ", " & TblResumes.[First Name] FROM TblResumes ORDER BY [Last Name];

But it is telling me now that it is creating duplicate records now? And its not auto-populating the other fields such as Cell Phone and Email address from the TblResumes.

It did not do that before?

Any help is GREATLY appreciated!~

 

[CaptainD]

You can merge them in your query

SELECT TblResumes.WorkerID, TblResumes.[First Name]+ " " + TblResumes.[Last Name] AS FullName FROM TblResumes ORDER BY [Last Name];

 

[TheAceMan1]

How are ya Elvis72 . . .

[blue]CaptainD[/blue] is certainly on target, however there's a space missing in the SQL:

[blue]SELECT TblResumes.WorkerID, TblResumes.[First Name][COLOR=red yellow]+ " "[/color]
   [purple][b]should be:[/b][/purple]
SELECT TblResumes.WorkerID, TblResumes.[First Name][COLOR=red yellow]  + " "[/color][/blue]

Now, unless your in a foreign country:

[blue][COLOR=red yellow]+[/color]
   should be replaced with
[COLOR=red yellow]&[/color][/blue]

and for easier view of sorting by [blue]Last Name[/blue], SQL should be:

[blue]SELECT WorkerID, [First Name] & " " & [Last Name] AS FullName FROM TblResumes [b]ORDER BY [Last Name], [First Name][/b];[/blue]

and by [blue]first name[/blue]:

[blue]SELECT WorkerID, [First Name] & " " & [Last Name] AS FullName FROM TblResumes [b]ORDER BY [First Name], [Last Name][/b];[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Elvis72]

You all are awesome!~

It worked like a charm!~

Quick question...can you merge a numeric and a text field in the same way?

I have a Title ID combobox that has the ID and the Position Title.

Thanks so much for your help!~

 

[TheAceMan1]

Elvis72

[blue]SELECT WorkerID, [[purple][B][I]TextFieldName[/I][/B][/purple]] & " " & [[[purple][B][I]NumericFieldName[/I][/B][/purple]] AS FullName FROM TblResumes;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Elvis72]

Good Morning!~

Hope all had a good weekend!~

I tried the above to merge numeric and text fields into one combobox and it is still giving me the:

"The value you entered isn't valid for this field"

For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

So, I don't know how to get around that as the ID field is an AutoNumber and the text field is the Title.

Any help is GREATLY appreciated!~

Thanks!~

 

[TheAceMan1]

there's an opening bracket that doesn't have a closing bracket. remove the bracket in [red]red[/red]:

[blue]SELECT WorkerID, [TextFieldName] & " " & [red][b][[/b][/red][NumericFieldName] AS FullName FROM TblResumes;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Elvis72]

OK...I think I'm just having a moment...

I have tblStaffingRequirements

Which has:

Title ID - Autonumber
Position Title - Text

When I use the above with or without the extra bracket I'm still getting the data error?

 

[Elvis72]

So, I have this:

SELECT [Position Title] & " - " & [Title ID] AS FullName FROM TblStaffRequirements;

which it shows me what I want...just when I select it...its still giving me the error?

 

[PHV]

its still giving me the error
Which error in which line of code when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

The only statement is in the rowsource I have no code on the combobox?

I'm getting the error when I open the mainform and make a selection from the combobox on the subform.

And its just the data is not a numeric value.

 

[PHV]

What is the ControlSource property of this combo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

It's the Title ID Autonumber field

 

[PHV]

So, now you know the reason of the error message...
I suggest the following RecordSource:
SELECT [Title ID],[Position Title] & " - " & [Title ID] AS FullName FROM TblStaffRequirements

And set the BoundColumn property to 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

When I used:

SELECT [Title ID], [Position Title] & " - " & [Title ID] AS FullName FROM TblStaffRequirements;

Then it is only showing the Title ID number not the Position Title?

The Bound Column is set to 1

 

[Pampers]

Maybe,

number of columns = 2
column with: 0;5 (cm)

Pampers
Keeping it simple can be complicated

 

[Elvis72]

All righty...I think we have struck gold!~

Here is the statement:

SELECT [Title ID], [Title ID] & " - " & [Position Title] AS FullName FROM TblStaffRequirements;

Bound Column set to 1
Column Count 2
Column Widths 0 and 1

Now I have another issue all together which I will post under another heading!~

Thanks sooo much for all your help!~