Display a message box with a count of filtered records.

[stockton12]

I have a search form that searches criteria based on a user's response. I
would like to display a count of all the filtered records that this code
produces. Can someone assist me with this?



Private Sub cmdSearch_Click()


If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else


'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter frmQA based on search criteria
Form_frmQA.RecordSource = "select * from tblQA where " & GCriteria
Form_frmQA.Caption = "Q&A (" & cboSearchField.Value & " contains '*"
& txtSearchString & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."


End If

End Sub

 

[Brian]

Use DCount, and use your GCriteria as the Where clause, something like this:

Dim RecCount as Integer
RecCount = DCount("*","[tblQA]",GCriteria)
MsgBox "Record count " & RecCount