Need Help With FindNext Statement

B

BobV

Group:

I am trying to write a search subroutine that will search for a user
provided search string, and once the user has found all of the instances,
the subroutine will allow the user to re-start the same search from the
beginning of the recordset. However with my code (below), once all instances
of the search string have been found, the user is not allowed to search on
that string again, it seems that "rs.NoMatch" is always set to TRUE for
that search string.

How do I allow the user to restart the search for the same search string
from the beginning of the recordset? The code below will not restart the
search. It will always tell the user that there are no matches if the user
has found all instances of the search string. I have tried closing the
recordset with "rs.close", but that didn't solve the problem.

Thanks,
BobV

Note: This code is behind a Form called SearchForm, and it executes when the
user has pressed the OK button to initiate the search.

'OK BUTTON
Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If SearchBy = 1 Then
If IsNull(Me.AssetIDSearch) Then
MsgBox "You must first enter an Asset ID Number.",
vbExclamation, "MISSING INFORMATION ERROR"
Me.AssetIDSearch.SetFocus
Exit Sub
Else
Locate = Me.AssetIDSearch
End If
ElseIf SearchBy = 2 Then
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
Else
'No Selection Made
GoTo ExitHandler
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
If SearchBy = 1 Then
rs.FindNext "AssetID like '" & Locate & "*'"
ElseIf SearchBy = 2 Then
rs.FindNext "Description like '" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find. Try again."
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!Group.SetFocus
rs.Close
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
 
M

Marshall Barton

BobV said:
I am trying to write a search subroutine that will search for a user
provided search string, and once the user has found all of the instances,
the subroutine will allow the user to re-start the same search from the
beginning of the recordset. However with my code (below), once all instances
of the search string have been found, the user is not allowed to search on
that string again, it seems that "rs.NoMatch" is always set to TRUE for
that search string.

How do I allow the user to restart the search for the same search string
from the beginning of the recordset? The code below will not restart the
search. It will always tell the user that there are no matches if the user
has found all instances of the search string. I have tried closing the
recordset with "rs.close", but that didn't solve the problem.
Click to expand...

Do not try to close a forms recordsetclone, its not your
recordset so you could mess over whoever (the form) did
create it.

I'm pretty sure the problem is that you only use FindNext,
which will only search from the recordset's current record
forward. So, once you've found the last match, it will
aways report NoMatch on subsequent searches. You should
either do a FindFirst or MoveFirst when you want to start a
new search or restart the same search.
--
Marsh
MVP [MS Access]



Note: This code is behind a Form called SearchForm, and it executes when the
user has pressed the OK button to initiate the search.

'OK BUTTON
Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If SearchBy = 1 Then
If IsNull(Me.AssetIDSearch) Then
MsgBox "You must first enter an Asset ID Number.",
vbExclamation, "MISSING INFORMATION ERROR"
Me.AssetIDSearch.SetFocus
Exit Sub
Else
Locate = Me.AssetIDSearch
End If
ElseIf SearchBy = 2 Then
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
Else
'No Selection Made
GoTo ExitHandler
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
If SearchBy = 1 Then
rs.FindNext "AssetID like '" & Locate & "*'"
ElseIf SearchBy = 2 Then
rs.FindNext "Description like '" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find. Try again."
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!Group.SetFocus
rs.Close
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
Click to expand...
 
B

BobV

Thanks Marshall for your help. It is what I needed.
BobV

Marshall Barton said:
BobV said:
I am trying to write a search subroutine that will search for a user
provided search string, and once the user has found all of the instances,
the subroutine will allow the user to re-start the same search from the
beginning of the recordset. However with my code (below), once all instances
of the search string have been found, the user is not allowed to search on
that string again, it seems that "rs.NoMatch" is always set to TRUE for
that search string.

How do I allow the user to restart the search for the same search string
from the beginning of the recordset? The code below will not restart the
search. It will always tell the user that there are no matches if the user
has found all instances of the search string. I have tried closing the
recordset with "rs.close", but that didn't solve the problem.
Click to expand...

Do not try to close a forms recordsetclone, its not your
recordset so you could mess over whoever (the form) did
create it.

I'm pretty sure the problem is that you only use FindNext,
which will only search from the recordset's current record
forward. So, once you've found the last match, it will
aways report NoMatch on subsequent searches. You should
either do a FindFirst or MoveFirst when you want to start a
new search or restart the same search.
--
Marsh
MVP [MS Access]



Note: This code is behind a Form called SearchForm, and it executes when the
user has pressed the OK button to initiate the search.

'OK BUTTON
Private Sub OKButton_Click()
Dim rs As DAO.Recordset
Dim Locate As String
On Error Resume Next
If SearchBy = 1 Then
If IsNull(Me.AssetIDSearch) Then
MsgBox "You must first enter an Asset ID Number.",
vbExclamation, "MISSING INFORMATION ERROR"
Me.AssetIDSearch.SetFocus
Exit Sub
Else
Locate = Me.AssetIDSearch
End If
ElseIf SearchBy = 2 Then
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
Else
'No Selection Made
GoTo ExitHandler
End If
Set rs = Forms![Add / Edit Assets].RecordsetClone
If SearchBy = 1 Then
rs.FindNext "AssetID like '" & Locate & "*'"
ElseIf SearchBy = 2 Then
rs.FindNext "Description like '" & Locate & "*'"
End If
If rs.NoMatch Then
MsgBox "Could not find. Try again."
Else
Forms![Add / Edit Assets].Bookmark = rs.Bookmark
End If
Forms![Add / Edit Assets]!Group.SetFocus
rs.Close
Set rs = Nothing
ExitHandler:
DoCmd.Close acForm, "SearchForm", acSaveNo
DoCmd.SelectObject acForm, "Add / Edit Assets"
DoCmd.Maximize
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Need help with a Search Form and code 2
Building dynamic where clause with like & "*" 10
How do I FindNext in Recordset.clone 0
Need help modifying code 0
Extract name, address and ph# 8
Unexplained failure of wdFindStop at end of search 0

Top