Me.Recordset.RecordCount - value off

[tdmailbox]

I have a oncurrent event thatd diplays the record count.

Me.rec_count.Caption = "Record " & Me.CurrentRecord & " of " &
Me.Recordset.RecordCount

However on tables that have a few hundred records it seems to always
start out 1 of 101 and then refreshes to the real count as I flick
through the records.

How can I get it display the correct record count from the start?

 

[Dirk Goldgar]

I have a oncurrent event thatd diplays the record count.

Me.rec_count.Caption = "Record " & Me.CurrentRecord & " of " &
Me.Recordset.RecordCount

However on tables that have a few hundred records it seems to always
start out 1 of 101 and then refreshes to the real count as I flick
through the records.

How can I get it display the correct record count from the start?

I'm not 100% sure this will work, but try this in the form's Load event:

'----- start of code -----
Private Sub Form_Load()

Me.RecordsetClone.MoveLast

End Sub

'----- end of code -----

If that doesn't work, you may have to modify your Current event code
like this instead:

With Me.RecordsetClone
.MoveLast
Me.rec_count.Caption = _
Record " & Me.CurrentRecord & " of " & .RecordCount
End With

 

[Bob Howard]

I have a similar need and I have successfully used the DCount function in
several places to count the records at whatever time I need to know the
precise count. You need to point it to a field, so pick something unique
(like a unique key fields, etc.). Bob.

 

[Dirk Goldgar]

I have a similar need and I have successfully used the DCount
function in several places to count the records at whatever time I
need to know the precise count. You need to point it to a field, so
pick something unique (like a unique key fields, etc.). Bob.

FWIW, you don't need to point DCount to a field. You can write

DCount("*", "MyTable")

to get a count of the records without regard to any field. If you write

DCount("MyField", "MyTable")

the count will only include those records where MyField is not Null.

 

[Bob Howard]

Thanks for the hint!

I get most of my knowledge from either this newsgroup or the Help screens
and never saw this in Help.

I'll go back and change those on the next release of the application.

Bob.

 

[Dirk Goldgar]

Thanks for the hint!

I get most of my knowledge from either this newsgroup or the Help
screens

Me, too -- I heartily recommend that approach.

 

[Guest]

I'm not 100% sure this will work, but try this in the form's Load event:

'----- start of code -----
Private Sub Form_Load()

Me.RecordsetClone.MoveLast

End Sub

'----- end of code -----

If that doesn't work, you may have to modify your Current event code
like this instead:

With Me.RecordsetClone
.MoveLast
Me.rec_count.Caption = _
Record " & Me.CurrentRecord & " of " & .RecordCount
End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I have had to do this very thing multiple times in different applications.
What I do is, right after I open the recordset, table, etc., I do the
following code:

If MyRS.Recordcount > 0 then
MyRS.MoveLast
MyRS.MoveFirst
End If

This will force the recordset to fully populate and leave the current record
at the same location it was in. The If statement will trap for an empty
recordset.

HTH
Texernie1

 

[Dirk Goldgar]

I have had to do this very thing multiple times in different
applications. What I do is, right after I open the recordset, table,
etc., I do the following code:

If MyRS.Recordcount > 0 then
MyRS.MoveLast
MyRS.MoveFirst
End If

This will force the recordset to fully populate and leave the current
record at the same location it was in. The If statement will trap for
an empty recordset.

That's right, but the MoveFirst isn't necessary in this case, since we
don't care where the form's RecordsetClone is actually positioned.

 

[Lynn Trapp]

I do the following in the Current event of a form and it's alway successful:

Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

Me.txtRecordNumber = "Record number " & Me.CurrentRecord & " of " & lngCount
& " records"


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Dirk Goldgar]

I do the following in the Current event of a form and it's alway
successful:

Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

Me.txtRecordNumber = "Record number " & Me.CurrentRecord & " of " &
lngCount & " records"

Lynn, why would you bother to .MoveFirst?

I have a theory that you really only need to visit the end of the
recordsetclone once (as in the code I posted), but I only tested it on
one form. It worked there, but I don't know if it works always.

 

[Lynn Trapp]

Lynn, why would you bother to .MoveFirst?

I have a theory that you really only need to visit the end of the
recordsetclone once (as in the code I posted), but I only tested it on
one form. It worked there, but I don't know if it works always.

Dirk,
You may very well be right that the .MoveFirst isn't necessary, but I like
to cover all my bases.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html