Search based on lookup field? 1

[FancyPrairie]

Assume I have an employee table and one of the columns represents a Department ID. I trying to create a general purpose routine where the user can select the dept they want to search for or filter on.

Now the search can be done 2 ways. User selects the ID from a combobox or the user enters the department name with a wildcard.

Since the employee ID stores the dept id, I can search the employee table for a dept whose ID equals the one the user selected from the combobox.

But how do I search the employee table when the combobox contains a wildcard (i.e. dept*).

Note that I can't create a query to do the search, I need to do the search on the form's recordset (i.e. me.recordset.findfirst).

It's been awhile, but I thought there was a LookUp method to do this (i.e. LookUp.DeptComboBox Like "Hum*")

Example:

DeptComboBox on Employee form:
Control Source ... lngDeptID
RowSource ........ Select lngDeptID, strDeptName From...
Bound Column ..... 1
Column Count ..... 2
Width ............ 0";1"

SearchComboBox on Search form
RowSource ........ Select lngDeptID, strDeptName from...
Bound Column ..... 2 (so I can enter wildcards)
Column Count ..... 2
Width ............ 0";1"


me.recordset.findfirst "LookUp.lngDeptID" Like 'SearchCombobox.value'"

 

[PHV]

Me.Recordset.FindFirst "lngDeptID=" & DLookUp("lngDeptID", "tblDept", "strDeptName Like '" & SearchComboBox.Value & "'")

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

 

[dhookom]

FancyPrairie,
Are you expecting to return employee records for possibly more than department?

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

PHV,

Thanks, works like I wanted it to.

 

[FancyPrairie]

Oooops! Spoke too soon. Although I didn't specify it in my original post, I need to be able to do FindNext, FindPrev, FindLast, and FindFirst also. In addition, the code I'm running could be, but not necessarily, be in my Library database (to which I reference). So, I will need to connect to the correct database.

But, anyway, it's been several years since I did this, and can't seem to find an example, but isn't there a way to search based on the text value of the combobox? I thought the syntax was something like LookUp.combobox