Text Search Facility on Form

[Apschminkey]

I have copied part of a text search facility from:

http://www.microsoft-accesssolutions.co.uk/text_search.htm

onto an application that I have been working on. Originally it searched a field called strStudentID for a particular string and then went to THAT record. I am using it to search a field called "Variety" (vegetable crop variety names). Anyway, it works just as it is supposed to. However, I would like to "tweak" it so that instead of finding exact matches it will search for an occurance of a sub-string and still go to the correct record. For example, I would like to type in "CABAL" and have the form populated with the data associated with the variety "CABALLERO". The reason is that some of the crop varieties have multiple names or codes and they are entered as "CABALLERO (VT3073)". Searching for "Caballero" or "VT3073" alone will not locate the necessary record. The code associated with the search button is shown below:

-------------------------------------------------------------------------------------------------------------------------------

Private Sub cmdSearch_Click()
Dim strStudentRef 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 Variety

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

Variety.SetFocus
strStudentRef = Variety.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

'If matching record found sets focus in Variety and shows msgbox
'and clears search control

If strStudentRef = strSearch Then
MsgBox "Match Found For: " & strSearch, , "Congratulations!"
Variety.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
------------------------------------------------------------------------------------------------------------------------------

I thought I could noodle this one out by using "instr" somehow but in real life I am just a lowly biologist with no formal access or vb training. Would someone be so kind as to show me how to make the necessary modifications to the above code?


btw, what happens if there are two records which match the search string?

Thanks in Advance!

Dan

 

[PHV]

Replace this:
DoCmd.FindRecord Me!txtSearch
By either this:
DoCmd.FindRecord Me!txtSearch, acAnywhere
Or this:
DoCmd.FindRecord Me!txtSearch, acStart

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886