How can I count records in a form?

[jsnunez]

hi all

how can I can count the nuber of records in a form? I have a text box where I duisplay the number of records.

how can I count the number of records when the form has been filtered by program?

thanks
jsn

 

[RobSindle]

In the form Footer or Header put a text box.
In its control source put:

=Count([YourFieldName])

and it will display the number of records on the form.
Good Luck,
Rob

 

[jsnunez]

Hi

here is how I did it, and it works!!!!

Public Function CountRecordsFiltered(strWhere) As Integer

Dim rsResult As ADODB.Recordset


strWhere = Replace(strWhere, "#", "'")

If strWhere <> "" Then
strQuery = "SELECT count(*) as Total from Customers WHERE " & strWhere
Else
strQuery = "SELECT count(*) as Total from Customers"
End If

Set rsResult = CurrentProject.Connection.Execute(strQuery)

rsResult.MoveFirst
rsResult.MoveLast

CountRecordsFiltered = rsResult("total")



End Function

 

[RobSindle]

I was trying to keep it simple. Yes your way would work, but it does too much "work".

A MUCH cheaper solution uses a RecordsetClone of the form's recordset. It does not have to be "recalculated" and is MUCH faster.

This assumes you have a reference set to Microsoft DAO 3.6 (or some other version) under Tools/References.

dim rs as DAO.recordset
dim lngNumberOfRecords as long
Set rs = me.recordsetclone

if rs.recordcount = 0 then
'You have no records
else
rs.movelast
lngNumberOfRecords = rs.recordcount
endif

rs.close
set rs = nothing

 

[RoyVidar]

I don' think the recordsets of ADP's are DAO. I think they are ADO. Not all ADO recordsets supports RecordCount, but the following, which is doing some testing for it, might work, using the RecordsetClone.

I don't use ADP's much, so I can't be sure I've trapped all relevant possibilities.

[tt]dim rs as adodb.recordset
set rs = me.recordsetclone ' or recordset.clone
dim lngCount as long
if not rs.eof then
if rs.supports(adbookmark) and rs.supports(adapproxposition) then
me!txtCount.value = rs.recordcount
else
do while not rs.eof
lngCount = lngCount +1
rs.movenext
loop
me!txtCount.value = lngCount
end if
else
me!txtCount.value = 0
end if[/tt]

- note - typed not tested

But, I'm inclined to believe jsnunez solution is one of the more reliable ways of getting correct recordcount with ADO, if "=Count(IdField)" as controlsource isn't an option (though it shouldn't be necessary to perform .movelast/.movefirst).

Roy-Vidar

 

[RobSindle]

Agreed, I didn't see the mention of the ADP.

However, substitute "ADODB.Recordset" for "DAO.Recordset"
and my solution will work (with way less "work") and will be MUCH faster.
Also, you might not need to do a .movelast (see the part about testing for support of bookmarks and approxposition) I wasn't aware of those properties being available to see if the recordcount is "available" without going to the last record.

Rob