Incorrect count in filtered recordset

[liz malcolm]

Hello and TIA

I have a global search (used Graham Thorpe example) function that will
display a message box with Match found Record 1, 2 etc.

I pass the form, table and query names into the function.

Everything worked, BUT now the end users want to see the number of
clients it finds with the same last name - ex: Match 1 of 6 found.

I found suggestions that say to open a second recordset, I have
rstSearch and rstFiltered, but I must be putting rstFiltered in the
wrong place or not declaring it correctly because the Filtered
Recordcount returns the total records in the recordset not just the
matched records.


Here is the code (snippet)

Dim response As Variant
Dim dbSearch As dao.Database
Set dbSearch = CurrentDb()
Dim rstSearch As dao.Recordset
Set rstSearch = dbSearch.OpenRecordset(qrySearch)
Dim rstFiltered As dao.Recordset
Dim frmSearch As Form
Set frmSearch = Forms(frmname)
Dim num_recs As String
'num_recs holds the number of records found
Dim num_recsAll As String
'num_recs should hold the number of total records found
Dim lngLastRecordFound
'lngLastRecordfound holds the record position
Dim strTextRef As Variant
'strTextRef is the passed match to ctlFoundText
Dim strSearch As String
'strSearch is the passed value from the unbound search box
Dim strFilter As String
'filtered search
On Error GoTo Err_cmdSearch
lngLastRecordFound = 0
num_recs = rstSearch.RecordCount

'check search box for Null Value or Entry and call msgbox datamissing
If IsNull(ctlSearchText) Or (ctlSearchText) = "" Then
Call DataMissing_msg
ctlSearchText.SetFocus
Exit Function
End If

'Perform the search using value entered into txtSearch
With rstSearch
rstSearch.MoveLast
rstSearch.MoveFirst
ctlFoundText.SetFocus
DoCmd.FindRecord ctlSearchText.Value, acAnywhere
ctlFoundText.SetFocus
strTextRef = ctlFoundText.Value
ctlSearchText.SetFocus
strSearch = ctlSearchText.Value
num_recs = 0
strFilter = strSearch
rstSearch.Filter = strSearch
Set rstFiltered = dbSearch.OpenRecordset(qrySearch)
rstFiltered.MoveLast
num_recsAll = 0
num_recsAll = rstFiltered.RecordCount


If InStr(strTextRef, strSearch) <> 0 Then

Do While frmSearch.CurrentRecord <> lngLastRecordFound
lngLastRecordFound = frmSearch.CurrentRecord
num_recs = num_recs + 1

DoEvents
If MsgBox("Match Found - Search Again?" & vbNewLine & "Record"
& " " & num_recs & " of " & num_recsAll, _
vbYesNo, "Search Results") = vbYes Then
ctlFoundText.SetFocus
DoCmd.FindNext
If lngLastRecordFound = frmSearch.CurrentRecord Then
MsgBox "There are no more matches", , "Search Results"
End If
ElseIf response = vbNo Then
Exit Do
End If
Loop

Thanks for any assistance . Liz