how show 'count' of filtered items

  • Thread starter Thread starter djc
  • Start date Start date
D

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.
 
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
 
Back
Top