Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / August 2007

Tip: Looking for answers? Try searching our database.

How to do FindFirst on split string from combobox

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bob engler - 28 Aug 2007 02:21 GMT
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 - 28 Aug 2007 03:40 GMT
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

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.