how show 'count' of filtered items

[djc]

my setup:
I have one 'main' bound form and a separate 'search' unbound form. The
search form is used to enter criteria that ultimately is used for the
criteria of the Filter property. I use the FilterOn property to do the
search. (the table the data is coming from is an ODBC jet connected linked
table from SQL server 2000)

What I want to do:
I have a text box on the search form that I want to display the number of
records the 'search' returned. How would I do that? I am aware of the
'count' jet sql statement but since I am using a filter I don't know if I
could use that here. I am also aware of the DAO recordcount property but
agian I'm not sure if that will work or is what I should use in this
situation. The filter is applied to the 'main' form. The criteria is set and
the number of returned items I want to display is in the separate 'search'
form.

subQuestion:
would the recordcount property of the forms recordset clone recordset return
the filtered count or all records?

any help is appreciated.

 

[Jim McDonald]

Hi
recordcount does not show how many records there are in a set, but which
record in the set you currently point at, so its no good for your purpose. I
wrote a little function...

------------------------------------------------
Function rcount(tn As Variant)
On Error Resume Next
rcount = 0
Dim Thedb As Database, ThisTable As Recordset, Total As Long
Set Thedb = DBEngine.Workspaces(0).Databases(0)
Set ThisTable = Thedb.OpenRecordset(tn)
ThisTable.MoveLast
If Err = 3021 Then
rcount = 0
On Error GoTo 0
ThisTable.Close
Exit Function
End If
rcount = ThisTable.RecordCount
ThisTable.Close
On Error GoTo 0
End Function
--------------------------------------------------
This works for any variant object so examples are c=rcount("tblThis");
rcount(sql) ; rcount("select... where " & filt)
hope this helps.

jimbo