Record Total on Form

[Joe Blow]

Hello All:

Thanks again for everyone's help.

Here's the deal:
On a form, how do you display the total number of records without using the
navigation buttons on the bottom?

I have a form built from a query.
I wrote a separate query to count the # of records in the first query.
I cannot put that field from the count query onto the form built from the
first query.

Is there another way to say "Record 1 of 75" without the |< < > >|
buttons?
Or even a "Total" number of records?


Please help.


Joe Blow

 

[Allen Browne]

You can set the Control Source of a text box to:
=[Form].[CurrentRecord] & " of " & [Form].[RecordsetClone].[RecordCount]

However, if the form is bound to a query or an attached table, it might
initially show:
Record 1 of 1
instead of the correct count. This happens because Access shows the form
with the first record before loading all the records, so the RecordCount
takes a while to update, and then the calculated control is not updated for
some time after that.

If you need to work around that, you could add something like this to the
Load event of the form to force it to load the rest of the records:
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast
End If
End With