Count number of records in recordset

[LWCARAB]

I have a sql to search through a table but I need to count the number of records after the filter has been run. The database is too large to attach but here is the sql search code (that works fine):




Private Sub DoFilter()
Dim stSQL As String
'construct sql string based on the search criteria boxes
If qtitle <> "" Then
stSQL = "[title] like '*" & qtitle & "*'"
End If

If qjournal <> "" Then
If stSQL <> "" Then
stSQL = stSQL & " AND "
End If
stSQL = stSQL & "[journal] like '*" & qjournal & "*'"
End If

If qauthor <> "" Then
If stSQL <> "" Then
stSQL = stSQL & " AND "
End If
stSQL = stSQL & "[author] like '*" & qauthor & "*'"
End If

If qissue <> "" Then
If stSQL <> "" Then
stSQL = stSQL & " AND "
End If
stSQL = stSQL & "[issueNo] = " & qissue
End If

If qsubject <> "" Then
If stSQL <> "" Then
stSQL = stSQL & " AND "
End If
If qsubject2 <> "" Then
stSQL = stSQL & "([subject] like '*" & qsubject & "*' OR [subject] like '*" & qsubject2 & "*')"
Else: stSQL = stSQL & "[subject] like '*" & qsubject & "*'"
End If
ElseIf qsubject2 <> "" Then
If stSQL <> "" Then
stSQL = stSQL & " AND "
End If
stSQL = stSQL & "[subject] like '*" & qsubject2 & "*'"
End If
'Apply filter if the sql string is equal to something, other wise show all records
If stSQL <> "" Then
DoCmd.ApplyFilter , stSQL
Else
DoCmd.ShowAllRecords
End If
End Sub

 

[RuralGuy]

You can use

Me.RecordsetClone.MoveLast
YourVariableName = Me.RecordsetClone.RecordCount

...to record the current RecordCount. Using *your* variable name of course.