Loop doesn't find all occurences of search, need help with recordset

[Liz Malcolm]

Hello and TIA for guidance.

I am building a reusable search procedure (thanks go to Graham Thorpe
for his example that set me on my way). Everything works up until the
2nd match is found, the command doesn't go to the third match, it
exits out of the loop. I've read everything I could find in NG's, but
I can't figure out where I'm going wrong. Here is my code.

Function cmdSearch(ctlSearchText As Control, ctlFoundText As Control,
qrySearch As String)
'author Graham Thorpe 25-01-02
'modification Liz Malcolm 6/1/2004
'call from "On Click" event of a search button
On Error GoTo Err_cmdSearch
Dim dbSearch As DAO.Database
Set dbSearch = CurrentDb()
Dim rstSearch As DAO.Recordset
Set rstSearch = dbSearch.OpenRecordset(qrySearch)
Dim strTextRef As String
'strTextRef is the passed match to ctlFoundText
'ctlFoundText is the passed reference to the control on the form
'with the value you want to match
Dim strSearch As String
'strSearch is the passed value from the unbound search box
'ctlSearchText is the passed reference to the unbound search box
'txtSearch is the name of the unbound search box
'check search box for Null Value or Entry
If IsNull(ctlSearchText) Or (ctlSearchText) = "" Then
Call DataMissing_msg
ctlSearchText.SetFocus
Exit Function
End If
'Perform the search using value entered into txtSearch
rstSearch.MoveFirst
ctlFoundText.SetFocus
DoCmd.FindRecord ctlSearchText
ctlFoundText.SetFocus
strTextRef = ctlFoundText.Text
ctlSearchText.SetFocus
strSearch = ctlSearchText.Text
'If matching record is found, focus is set in matched control,
'and search control is cleared
If strTextRef = strSearch Then
Do While strTextRef = strSearch And Not rstSearch.EOF
DoEvents
If MsgBox("Match Found - Search Again?", vbYesNo, "Search
Results") = vbYes Then
ctlFoundText.SetFocus
rstSearch.MoveNext
If Not rstSearch.NoMatch = True Then
MsgBox ("There are no more matches")
Exit Do
Else
DoCmd.GoToRecord , , acNext 'display next record
End If
Else
Exit Do
End If
Loop
ctlFoundText.SetFocus
ctlSearchText = ""
'if value not found focus is set to a new record and search control is
cleared
Else
Call search_msg
Call NewRec
ctlSearchText.SetFocus
ctlSearchText = ""
End If
rstSearch.Close
Set rstSearch = Nothing
dbSearch.Close
Set dbSearch = Nothing
Exit_cmdsearch:
Exit Function
Err_cmdSearch:
MsgBox Err.Description
Resume Exit_cmdsearch
End Function

Again TIA for any help.

Liz M.

[rkc]

"Liz Malcolm" <em******@bellsouth.net> wrote in message
news:ae**************************@posting.google.c om...

Hello and TIA for guidance.

I am building a reusable search procedure (thanks go to Graham Thorpe
for his example that set me on my way). Everything works up until the
2nd match is found, the command doesn't go to the third match, it
exits out of the loop. I've read everything I could find in NG's, but
I can't figure out where I'm going wrong. Here is my code.

<snip all posted code>

I didn't know where to begin correcting all that so I started with the
basic concept and re-wrote it. The match is on any part of the
target field. That's easily changed if it's not acceptable.

<code>
Function cmdSearch(ctlSearchText As Control, _
ctlFoundText As Control, _
qrySearch As String)
Dim strTextRef As String
'strTextRef is the passed match to ctlFoundText
'ctlFoundText is the passed reference to the control on the form
'with the value you want to match

Dim strSearch As String
'strSearch is the passed value from the unbound search box
'ctlSearchText is the passed reference to the unbound search box
'txtSearch is the name of the unbound search box
'check search box for Null Value or Entry

Dim lngLastRecordFound
'lngLastRecordfound holds the record position of the
'last matching record and is used to determine whether
'a match was found or not by comparison to the
'Me.CurrentRecord value

On Error GoTo Err_cmdSearch

lngLastRecordFound = 0

If IsNull(ctlSearchText) Or (ctlSearchText) = "" Then
'------------------------------------------
'replacement for routine not posted
MsgBox "Missing data message"
'------------------------------------------
GoTo Exit_cmdsearch
End If

'Perform the search using value entered into txtSearch
ctlFoundText.SetFocus
'Match any part of the field
DoCmd.FindRecord ctlSearchText.Value, acAnywhere
'set search and target string values for comparison
'note: could just use the control values directly
strTextRef = ctlFoundText.Value
strSearch = ctlSearchText.Value

'test for initial match
If InStr(strTextRef, strSearch) = 0 Then
MsgBox "No Records Found"
GoTo Exit_cmdsearch
End If

'keep searching until Me.CurrentRecord matches
'lngLastRecordFound or the user exits by choice.
Do While Me.CurrentRecord <> lngLastRecordFound
'update last found postion
lngLastRecordFound = Me.CurrentRecord

If MsgBox("Match Found - Search Again?", _
vbYesNo, "Search Results") = vbYes Then
ctlFoundText.SetFocus
DoCmd.FindNext
Else
Exit Do
End If
Loop

MsgBox "No more records found"

'-----------------
'unknown methods
'Call search_msg
'Call NewRec
'-----------------
Exit_cmdsearch:
'reset state of controls on exit
ctlSearchText.SetFocus
ctlSearchText = ""
Exit Function

Err_cmdSearch:
MsgBox Err.Description
Resume Exit_cmdsearch

End Function
</code>

[Liz Malcolm]

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message news:<dR********************@twister.nyroc.rr.com> ...

"Liz Malcolm" <em******@bellsouth.net> wrote in message
news:ae**************************@posting.google.c om...

Hello and TIA for guidance.

I am building a reusable search procedure (thanks go to Graham Thorpe
for his example that set me on my way). Everything works up until the
2nd match is found, the command doesn't go to the third match, it
exits out of the loop. I've read everything I could find in NG's, but
I can't figure out where I'm going wrong. Here is my code.

<snip all posted code>

I didn't know where to begin correcting all that so I started with the
basic concept and re-wrote it. The match is on any part of the
target field. That's easily changed if it's not acceptable.

<code>
Function cmdSearch(ctlSearchText As Control, _
ctlFoundText As Control, _
qrySearch As String)
Dim strTextRef As String
'strTextRef is the passed match to ctlFoundText
'ctlFoundText is the passed reference to the control on the form
'with the value you want to match

Dim strSearch As String
'strSearch is the passed value from the unbound search box
'ctlSearchText is the passed reference to the unbound search box
'txtSearch is the name of the unbound search box
'check search box for Null Value or Entry

Dim lngLastRecordFound
'lngLastRecordfound holds the record position of the
'last matching record and is used to determine whether
'a match was found or not by comparison to the
'Me.CurrentRecord value

On Error GoTo Err_cmdSearch

lngLastRecordFound = 0

If IsNull(ctlSearchText) Or (ctlSearchText) = "" Then
'------------------------------------------
'replacement for routine not posted
MsgBox "Missing data message"
'------------------------------------------
GoTo Exit_cmdsearch
End If

'Perform the search using value entered into txtSearch
ctlFoundText.SetFocus
'Match any part of the field
DoCmd.FindRecord ctlSearchText.Value, acAnywhere
'set search and target string values for comparison
'note: could just use the control values directly
strTextRef = ctlFoundText.Value
strSearch = ctlSearchText.Value

'test for initial match
If InStr(strTextRef, strSearch) = 0 Then
MsgBox "No Records Found"
GoTo Exit_cmdsearch
End If

'keep searching until Me.CurrentRecord matches
'lngLastRecordFound or the user exits by choice.
Do While Me.CurrentRecord <> lngLastRecordFound
'update last found postion
lngLastRecordFound = Me.CurrentRecord

If MsgBox("Match Found - Search Again?", _
vbYesNo, "Search Results") = vbYes Then
ctlFoundText.SetFocus
DoCmd.FindNext
Else
Exit Do
End If
Loop

MsgBox "No more records found"

'-----------------
'unknown methods
'Call search_msg
'Call NewRec
'-----------------
Exit_cmdsearch:
'reset state of controls on exit
ctlSearchText.SetFocus
ctlSearchText = ""
Exit Function

Err_cmdSearch:
MsgBox Err.Description
Resume Exit_cmdsearch

End Function
</code>

Hello again. Since I am attempting to make this a reusable procedure
for all forms in my database shouldn't I be using the recordset method
instead of the ME method? I've placed the function in my
AllFormsUtilities module.

TIA for any help and guidance.

[rkc]

"Liz Malcolm" <em******@bellsouth.net> wrote in message
news:ae**************************@posting.google.c om...

Hello again. Since I am attempting to make this a reusable procedure
for all forms in my database shouldn't I be using the recordset method
instead of the ME method? I've placed the function in my
AllFormsUtilities module.

In that case, if I were to do things the way you're attempting to do
things, I guess I would pass the form object to the procedure
as well. You would then substitute the variable holding a reference
to the Form for Me.

objFormToSearch.CurrentRecord

As far as I could tell, the procedure you posted wasn't searching
for anything after the initial DoCmd.FindRecord. There was no
search being done in your loop. All it could ever do is exit.