Search for text within combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box that is set up like this:
EmployeeID
Last Name
First Name

It is looking up the information from an employee reference table. The
employeeID is the field that is stored into the main table. Users will not
know the employeeID when they want to look up information so I need the
search function to be able to look up the last name. Is this possible?
Thanks for any help
 
Stacey,

It is not clear from your post where you need to apply this. But maybe
this will help... If you hide the EmployeeID column in the combobox
(i.e. set its width to 0 by using the ColumnWidths property of the
combobox), then the EmployeeID will still be the field that the table
relates to, but the Last Name will be what is shown, and in this case,
assuming the combobox's AutoExpand property is set to Yes, the users
will be able to locate the employee in the combobox by starting to type
the Last Name.
 
I had tried that but the problem is that it just changes the value. For
example,

Emp Table Request Table
fld_EmpID fld_EmplID
fld_EmpLName fld_requesttype
fld_EmpFName others

My form is built off of the request table above but the combo box looks up
data from the emp table. It is great for data entry but not for lookup
purposes. When I select a person, it acts as though it is entering the data
not performing a lookup. Does this help any? Thanks for trying to help.
 
You probably need to set the number of columns to 3 so you can see the last
and first names (in case you have more than one Smith)
 
Stacey said:
I have a combo box that is set up like this:
EmployeeID
Last Name
First Name

It is looking up the information from an employee reference table. The
employeeID is the field that is stored into the main table. Users will not
know the employeeID when they want to look up information so I need the
search function to be able to look up the last name. Is this possible?
Thanks for any help
Click to expand...
 
sorry. I didn't understand you wanted the combobox to then go and search
for a record. We thought you just wanted to search for the name in the
list.

Make your combobox unbound!
Put code like the following in the Afterupdate Event.

Private Sub CmbEmpID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[fld_EmpID] = '" & Me![cmbEmpID] & "'"
Me.Bookmark = rs.Bookmark
End Sub
 
Stacey

Yes, if the combobox is bound to the fld_EmplID field in the Request
Table table, then a selection from the combobox will be stored in the
table, and changing the selection in the combobox will change the value
saved in the table. This is how it is supposed to work. If you want a
combobox for the purpose of locating a record, i.e. to give record
navigation functionality, you need an unbound control. For example, it
could go in the Form Header section. Lets say you call it EmpSel.
Then, on its AfterUpdate event property, you can use code similar to...
DoCmd.GoToControl Me.EmpID
DoCmd.FindRecord Me.EmpSel
 
Stacey,

Set the number of columns in the combo box to three and then in the column
width type the following
0";1";1"

This will hide the employee ID and show only the last name.

Mike
 
Back
Top