Refine Search

[hpunaro]

Hi guys,

I downloaded a sample of how to search for text in MS Access using a query.

My problem now is that I can find the record only if I write the complete name of the employee, but let`s suppose that I would like to find them just by the first name/surname, or part of it and all this data is stored in the same field.

I have the code here, if you could help me with a suggestion for this...I will be very glad. tks.

[LEFT]Private Sub cmdSearch_Click()
   Dim strStudentRef As String
   Dim strSearch As String[/LEFT]
 
[LEFT]'Check txtSearch for Null value or Nill Entry first.[/LEFT]
 
[LEFT]   If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
       MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
       Me![txtSearch].SetFocus
   Exit Sub
End If
'---------------------------------------------------------------[/LEFT]
 
[LEFT]'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID[/LEFT]
 
[LEFT]   DoCmd.ShowAllRecords
   DoCmd.GoToControl ("strStudentID")
   DoCmd.FindRecord Me!txtSearch[/LEFT]
 
[LEFT]   strStudentID.SetFocus
   strStudentRef = strStudentID.Text
   txtSearch.SetFocus
   strSearch = txtSearch.Text[/LEFT]
 
[LEFT]'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control[/LEFT]
 
[LEFT]   If strStudentRef = strSearch Then
       MsgBox "Match Found For: " & strSearch, , "Congratulations!"
       strStudentID.SetFocus
       txtSearch = ""[/LEFT]
 
[LEFT]   '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[/LEFT]

 

[rainman89]

take a look at the LIKE condition

 

[gemma-the-husky]

there is an enormous performance difference between finding an entire field, and finding a partial match. in the first case, access can use an index - in the second every record has to be read and searched for a match.

however, the easiest way for you to do what you want is - click in the name field you want to search - then click the binocular icon, and use that search form. - this wil ltake you to any record matching your search.

most vba user defined searches mostly duplicate this functionality, but in a simplified manner

 

[hpunaro]

Thanks for your replies.
I`ve been working with Like, but now I`m trying to do the search inside the form, that uses VBA and I guess that the like operator doesn`t work.

In the access query I can build it without problems, but the problem is coding it using vba.

any suggestion?

 

[rainman89]

are you trying something like this

Like '" & yourVariableName & "*'"

 

[hpunaro]

Yes, this is exactly what I`m trying to do, but I would like to have some help inserting this "Like" inside my code.

I did not have success so far.

 

[MSAccessRookie]

Yes, this is exactly what I`m trying to do, but I would like to have some help inserting this "Like" inside my code.

I did not have success so far.

Show us your updated code that does not work and we can assist you.

 

[hpunaro]

Ok, just to remember this code is not mine, I got from a template that I downloaded. I`m a beginner with VBA...so I don`t have any idea to make it work properly. This is what I thougt initially, but it`s not working.

Please I would appreciate some help.

[LEFT]Private Sub cmdSearch_Click()
   Dim strStudentRef As String
   Dim strSearch As String[/LEFT]
 

[LEFT]'Check txtSearch for Null value or Nill Entry first.[/LEFT]
 

[LEFT]   If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
       MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
       Me![txtSearch].SetFocus
   Exit Sub
End If
'---------------------------------------------------------------[/LEFT]
 

[LEFT]'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID[/LEFT]
 

[LEFT]   DoCmd.ShowAllRecords
   DoCmd.GoToControl ("strStudentID")
   DoCmd.FindRecord Me! Like " & txtSearch & "*"[/LEFT]
 

[LEFT]   strStudentID.SetFocus
   strStudentRef = strStudentID.Text
   txtSearch.SetFocus
   strSearch = txtSearch.Text[/LEFT]
 

[LEFT]'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control[/LEFT]
 

[LEFT]   If strStudentRef = strSearch Then
       MsgBox "Match Found For: " & strSearch, , "Congratulations!"
       strStudentID.SetFocus
       txtSearch = ""[/LEFT]
 

[LEFT]   '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[/LEFT]

 

[Thinh]

try the filter function
me.Filter = "Product Like '*" & me.txtsearch & "*'"
me.FilterOn = true

that should solve your problem

 

[hpunaro]

Well I got your idea, which could help me a lot, but I have the search box and the form all in one.

I just attached my mdb to this topic, than you can take a look if you can. The search is working in this mode, what I need is just an entry to perform a wide search with part of the characters.

Thank you.

 

[rainman89]

Hope thats not REAL student information in those tables

 

[hpunaro]

Don`t worry this information is fake...

anyway...i have downloaded this example from another site...no idea if they are real