Search Using Textbox

[CharleneA. via AccessMonster.com]

I have a textbox on my form to search for a record. I'm using one textbox to
do a multiple search. The search for trackingNo(number field) works just fine,
but the search for name(text field)does not work. Can someone look at my
code and tell me what I'm doing wrong? Here's my code:


Private Sub TextJO_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
If Val(Me.TextJO) = 0 Then
'They typed a name
'(Name Search code goes here)
rs.FindFirst "TempName = ' " & [TextJO] & " ' "
Me.Bookmark = rs.Bookmark
Else
'They typed TrackingNo
'(Number Search code goes here)
rs.FindFirst "[TrackingNo] = " & Str(Nz(Me![TextJO], 0))
Me.Bookmark = rs.Bookmark
End If

End Sub

 

[Marshall Barton]

I have a textbox on my form to search for a record. I'm using one textbox to
do a multiple search. The search for trackingNo(number field) works just fine,
but the search for name(text field)does not work. Can someone look at my
code and tell me what I'm doing wrong? Here's my code:


Private Sub TextJO_AfterUpdate()
Dim rs As Object
Set rs = Me.RecordsetClone
If Val(Me.TextJO) = 0 Then
'They typed a name
'(Name Search code goes here)
rs.FindFirst "TempName = ' " & [TextJO] & " ' "
Me.Bookmark = rs.Bookmark
Else
'They typed TrackingNo
'(Number Search code goes here)
rs.FindFirst "[TrackingNo] = " & Str(Nz(Me![TextJO], 0))
Me.Bookmark = rs.Bookmark
End If

End Sub

There are extra space characters in the name search.
However, even with that fixedm you will runinto problems
with names like O'Brian.

Another point is that you should check for the situation
where the name (and tracking number) is not found so you
don't try to use an invalid bookmark.

Try something more like this:

If Val(Me.TextJO) = 0 Then
rs.FindFirst "TempName = """ & [TextJO] & """"
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Else
rs.FindFirst "TrackingNo = " & Str(Nz(Me![TextJO], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
End If