How to do FindFirst on split string from combobox

[bob engler]

I have a form with a combobox with EmployeeLastName,EmployeeFirstName which
are 2 fields from table
Employee. My code is
Private Sub Combo31_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
n = split(Me.Combo31.Value, ",")
rs.FindFirst "[EmployeeLastName] = " & n(0) - n(0) has the correct name in
it but gives a runtime error -"The Microsoft Jet database engine does not
recognize 'Hill' as a valid field name or expression" where Hill is the last
name of the employee. I would really
like the code to find the correct LAstName and FirstName selected as we have
several with the same last name.

Thanks.....

 

[Linq Adams via AccessMonster.com]

I'm assuming that you when you say "I have a form with a combobox with
EmployeeLastName,EmployeeFirstName" you mean that when you made the combobox
with the wizard you selected both of these fields. You're then trying (I'm
guessing here) to use

n = split(Me.Combo31.Value, ",")

to separate the last name and first name. The problem with that is that

Me.Combo31.Value

isn't *EmployeeLastName,EmployeeFirstName* but rather is only
EmployeeLastName!

The simplest way to accomplish your goal here, to my mind, would be to

Create a query from your table, simply including all fields from your table.

Create a calculated field in your query by going to a blank field and in the
"field" box enter this:

CombinedEmployeeName: [EmployeeLastName] & " " & [EmployeeFirstName]

Name your query something unique, such as EmployeeQuery

Go back to your form and change the Record Source from the table to the
EmployeeQuery

Delete your original combobox and place a new combobox on your form.

When the Combobox Wizard comes up:

Select "Find a record on my form based on the value I've selected..."
Next
Select the calculated field, CombinedEmployeeName, as the field to appear in
the Combo box
Next
Size the Combo box column
Next
Name the combo box
Finish

When you go to the combobox now, and either start typing a name in or scroll
down, you'll see

EmployeeLastName EmployeeFirstName

and when you click on the wanted name the appropriate record will be
retrieved