Help with Search feature in Form 2

[tontenn]

Good Afternoon or morning wherever in the world you are.

I have a followup query about searching in forms. I have set up a basic search text box and assigned a button to search for company names.

Its going well but we are finding something that is annoying and slowing us down. The code we have used needs us to type the name of the company absolutely correct.

We would like to be able to type in even a few letters of this company or one or two words and search anmd it come back with a response.

The code I am using is 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 CompanyName1

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

CompanyName1.SetFocus
strStudentRef = CompanyName1.Text
txtSearch.SetFocus
strSearch = txtSearch.Text

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

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


Hope someone can help me. Im sure its very simple.

Thanks in advance

Cheers
Tones

 

[PHV]

What about something like this ?

Private Sub cmdSearch_Click()
Dim rs As DAO.Recordset
'Check txtSearch for Null value or Nill Entry first.
If Trim(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 CompanyName1
Set rs = Me.RecordsetClone
rs.FindFirst "CompanyName1 Like '" & Me![txtSearch] & "*'"
'If matching record found sets focus in CompanyName1 and shows msgbox
'and clears search control
If Not rs.NoMatch Then
  MsgBox "Match Found For: " & Me![txtSearch], , "Congratulations!"
  Me.Bookmark = rs.Bookmark
  Me![CompanyName1].SetFocus
  Me![txtSearch] = ""
'If value not found sets focus back to txtSearch and shows msgbox
Else
  MsgBox "Match Not Found For: " & Me![txtSearch] & " - Please Try Again.", , "Invalid Search Criterion!"
  Me![txtSearch].SetFocus
End If
rs.Close
Set rs = Nothing
End Sub

You need to reference the Microsoft DAO 3.# library

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

 

[TheAceMan1]

How are ya tontenn . . .

Have you considered a [blue]Combobox[/blue] or [blue]Listbox[/blue] for making your selections where the company names are spelled correctly?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[tontenn]

Thanks both of you for unreal responses.

I hadnt thought of Acemans idea. So i tried that first and its exactly what we wanted. Thanks heaps.

And to PHV, another great idea. Im going to test that on another version and see how that goes.

Cheers
Tones