Full Version: Search any field and part thereof
UtterAccess Forums > Microsoft® Access > Access Forms
johan
I got the following code form form databasedev
ode
-------------------------
Private Sub cmdSearch_Click()
Dim LastNameRef As String
Dim strSearch As String

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch
'and evaluates this against values in LastName

DoCmd.ShowAllRecords
DoCmd.GoToControl ("LastName")
DoCmd.FindRecord Me!txtSearch

Lastname.SetFocus
Lastname = Lastname.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in LastName and shows msgbox
'and clears search control
If Lastname = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
Lastname.SetFocus
txtSearch = ""

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
, "Invalid Search Criterion!"
txtSearch.SetFocus
End If
End Sub
-----------------------
HAs you can see I'm using the LastName as search criteria but what I actually what is to search any field in my DB and I don't want to enter the whole field but only a part thereof. I also have more than one client with the same LastName so I have to search for next.
How can change the code to do this.
Candace Tripp
Click Here
johan
Hi Candice
Odid look at the link you provided but it doesn't solve my problem. Attached find DB than illustrate my problem. In the DB you will find that there is more than one client with the same lastname as Brown. Everytime I time I type Brown in the ounbound search txt box and click on cmdSearch only the first Brown is found I can't get to the 2, 3rd and so on.
don't want the search to be link only to the lastname but rather any field and also I do not want to type in whole field I want to search but only part thereof.
You will find the code that I used behind the cmdSearch
johan
I did look at the link that Candice provided but it doesn't solve my problem. Attached find DB than illustrate my problem. In the DB you will find that there is more than one client with the same lastname as Brown. Everytime I time I type Brown in the ounbound search txt box and click on cmdSearch only the first Brown is found I can't get to the 2, 3rd and so on.
don't want the search to be link only to the lastname but rather any field and also I do not want to type in whole field I want to search but only part thereof. In othe words I want the seach action to work the same as the build in Access function but without calling up the search msg box.
You will find the code that I used behind the cmdSearch.
johan
I see that there no reply for sometime on this threat. Is there someone that can help me with this problem.
ace
A simple way to do what you want is to just use the form's filter
property.
!--c1-->
CODE
Private Sub cmdSearch_Click()
    If Len(Me.txtSearch) > 0 Then
      Me.FilterOn = False
      Me.Filter = "Lastname Like '*" & Me.txtSearch & "*'"
      Me.FilterOn = True
    End If
End Sub

You can expand on that to include other fields after you understand
what it does.
johan
Thanx Ace
Oenter the code behind the cmdSearch button but do get the following error
-----------------
Run-time error '7752'
Can't apply filter because all records are blocked
------------------
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.