RecordCount problem

[Rob]

Hi all,

I am stumped by this problem. I have a form based on query. I am not
using Navigation buttons on the form, but am using custom buttons and
the technique outlined in Access97 Developers Handbook to navigate and
count records. I also have a text box that lists the number of records
in the underlying query. This works well, until I filter the form and
the remove the filter. For eg. When I had an unfiltered form, my
RecordCount was 593, Filtered the RecordCount was 80, and then remove
the Filter RecordCount 501. This was consistent with each Filter I
applied (except for the filtered count!). After the filter was removed,
and I moved to the next record, the RecordCount would then correctly
display 593 again. I use the same technique each time and I am surprised
that I get a different result after removing the filter.

Here is my code:

Public Function CountTasks(frm As Form) As Integer
Dim rst As Recordset

Set rst = frm.RecordsetClone
CountTasks = rst.RecordCount + IIf(frm.NewRecord, 1, 0)

End Function

Any help would be greatly appreciated,

Cheers
Rob

 

[Allen Browne]

The RecordCount is the number of records accessed so far. To get an accurate
could MoveLast:
If rst.RecordCount > 0 Then
rst.MoveLast
End If
CountTasks = rst.RecordCount + IIf(frm.NewRecord, 1, 0)

Explanation in:
Traps: Working with Recordsets - 10 common mistakes
at:
http://allenbrowne.com/ser-29.html

 

[Rob]

Hi Alan,

Thanks for your response. It worked a treat. I have browsed your site
many times in the past and did not even think to have a look. Good luck
with the new job!

Cheers
Rob