Need help with a Search Form and code

  • Thread starter Thread starter BobV
  • Start date Start date
B

BobV

Group:

I have a form that allows the user to search the database for a matching
record. The form has an unbound field named DescriptionSearch where the user
types in a search word. And there are two buttons at the bottom of the form
named OK and Cancel. The user types in a word to locate and presses the OK
button. My code searches the recordset column labeled Description and looks
for a match, and then goes to that record if it finds a match.

The problem is that the code does not recognize a match if the match is with
the first record of the database. It will find matches with all other
records except the first one for some reason. I have included my code below.
(I deleted some of the code that I didn't think was pertinent to the
question at hand.)

Any ideas or suggestions as to how I make the code find a match if it is on
the first record of the database? Or any ideas on how to make the code
better?

Thanks,
BobV


Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If IsNull(Me.DescriptionSearch) Then
MsgBox "You must first enter a Description.", vbExclamation,
"MISSING INFORMATION ERROR"
Me.DescriptionSearch.SetFocus
Exit Sub
Else
Locate = Me.DescriptionSearch
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
rs.FindNext "Description like '*" & Locate & "*'"
If rs.NoMatch Then
rs.MoveFirst
rs.FindNext "Description like '*" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find: " & Locate
rs.MoveFirst
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!AssetID.SetFocus
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
 
BobV said:
I have a form that allows the user to search the database for a matching
record. The form has an unbound field named DescriptionSearch where the user
types in a search word. And there are two buttons at the bottom of the form
named OK and Cancel. The user types in a word to locate and presses the OK
button. My code searches the recordset column labeled Description and looks
for a match, and then goes to that record if it finds a match.

The problem is that the code does not recognize a match if the match is with
the first record of the database. It will find matches with all other
records except the first one for some reason. I have included my code below.
(I deleted some of the code that I didn't think was pertinent to the
question at hand.)

Any ideas or suggestions as to how I make the code find a match if it is on
the first record of the database? Or any ideas on how to make the code
better?


Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If IsNull(Me.DescriptionSearch) Then
MsgBox "You must first enter a Description.", vbExclamation,
"MISSING INFORMATION ERROR"
Me.DescriptionSearch.SetFocus
Exit Sub
Else
Locate = Me.DescriptionSearch
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
rs.FindNext "Description like '*" & Locate & "*'"
If rs.NoMatch Then
rs.MoveFirst
rs.FindNext "Description like '*" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find: " & Locate
rs.MoveFirst
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!AssetID.SetFocus
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
Click to expand...


THe problem is that FindNext starts its search with the
record after the current record. You need to use FindFirst
to start at the beginning of the recordset.
 
Thanks Marshall for the help. Sometimes the most obvious point eludes me. I
made the change to re.FindFirst, and now my search does what I want it to.

Thanks again,
BobV


Marshall Barton said:
BobV said:
I have a form that allows the user to search the database for a matching
record. The form has an unbound field named DescriptionSearch where the
user
types in a search word. And there are two buttons at the bottom of the
form
named OK and Cancel. The user types in a word to locate and presses the OK
button. My code searches the recordset column labeled Description and
looks
for a match, and then goes to that record if it finds a match.

The problem is that the code does not recognize a match if the match is
with
the first record of the database. It will find matches with all other
records except the first one for some reason. I have included my code
below.
(I deleted some of the code that I didn't think was pertinent to the
question at hand.)

Any ideas or suggestions as to how I make the code find a match if it is
on
the first record of the database? Or any ideas on how to make the code
better?


Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If IsNull(Me.DescriptionSearch) Then
MsgBox "You must first enter a Description.", vbExclamation,
"MISSING INFORMATION ERROR"
Me.DescriptionSearch.SetFocus
Exit Sub
Else
Locate = Me.DescriptionSearch
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
rs.FindNext "Description like '*" & Locate & "*'"
If rs.NoMatch Then
rs.MoveFirst
rs.FindNext "Description like '*" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find: " & Locate
rs.MoveFirst
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!AssetID.SetFocus
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
Click to expand...


THe problem is that FindNext starts its search with the
record after the current record. You need to use FindFirst
to start at the beginning of the recordset.
Click to expand...
 
Back
Top