Number of record retrieved on a form - REVISITED

[Catherine M]

On May 6th I asked a questions about placing the record
count in a variable. I received a reply from Cheryl
Fischer, MVP Microsoft Access, that said:

***********************************************
You can use the form's RecordsetClone.RecordCount
property. For example, if you had a label control on your
form named: lblNavigate, you could put the following code
in the On Current event of your form to duplicate the
Record Navigation functionality supplied by Access.


Dim lngrecordnum As Long

lngrecordnum = Me.CurrentRecord
If Me.NewRecord Then
Me!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
End With
Me!lblNavigate.Caption = "Record " &
Me.CurrentRecord & " of " &
Me.RecordsetClone.RecordCount
End If

***************************************

This works corrrectly until I filter and then unfilter the
records. After unfiltering the records, the record count
total is wrong. It gets corrected when I change focus to
a different record. What is causing the incorrect number?
Do I need coding in another event? The incorrect total has
always been 501, even when the true total varied from 9492
to 9494.

Catherine M

 

[Allen Browne]

The RecordCount actually shows the number of records accessed so far (except
for dbOpenTable type recordsets).

To force Access to load all the records and so get the true count, do this
when you turn the filter off:
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
Me!lblNavigate.Caption = "Record " & Me.CurrentRecord & " of " &
..RecordCount
End If
End With