recordsetclone not working

[Cmenkedi]

Hello,
I am hiving trouble with recordsetclone.
I have a form that I open and want to display the number of records. The
code that I am using is:
Set rst = Me.RecordsetClone
Me.txtRecordNumber = Me.CurrentRecord
Me.txtRecords = rst.RecordCount & " record" & IIf(rst.RecordCount = 1, "",
"s")

The txtRecords box will only show 1 record when there is more then one. The
wierd part of this problem is that if I go into debug and go line by line
through code it works perfectly. I am at a loss as to why it doesn't work.
I have it on 2 forms and neither works.

thank you

 

[Dirk Goldgar]

Hello,
I am hiving trouble with recordsetclone.
I have a form that I open and want to display the number of records. The
code that I am using is:
Set rst = Me.RecordsetClone
Me.txtRecordNumber = Me.CurrentRecord
Me.txtRecords = rst.RecordCount & " record" & IIf(rst.RecordCount = 1, "",
"s")

The txtRecords box will only show 1 record when there is more then one.
The
wierd part of this problem is that if I go into debug and go line by line
through code it works perfectly. I am at a loss as to why it doesn't
work.
I have it on 2 forms and neither works.

You probably need to move the recordsetclone to the last record to get the
record count. Try this:

Me.txtRecordNumber = Me.CurrentRecord
With Me.RecordsetClone
If Not .EOF Then .MoveLast
Me.txtRecords = .RecordCount & " record" & _
IIf(.RecordCount = 1, "", "s")
End With

 

[Cmenkedi]

Thank you that worked great.
Just for my knowledge what does the .EOF do and mean?

 

[Mike Painter]

EOF End Of File, BOF beginning.
You use it to make sure Access actually has a correct count.
(The technical reason is "because", or possibly ""because we've always done
it that way.")

It's always good to use because sometimes the right number will show up
without it.
I suspect it is a timing process.

 

[Dirk Goldgar]

Thank you that worked great.
Just for my knowledge what does the .EOF do and mean?

EOF (for "end of file") is a property of the recordset that indicates
whether it is positioned beyond the last record. It has the boolean value
True if it is, False if it is not. There's a corresponding value BOF (for
"beginning of file") that indicates whether the recordset is positioned
before the first record. If a recordset is empty (contains no records) then
both EOF and BOF will be True.

The MoveLast method positions the recordset to the last record. But calling
MoveLast will raise an error if the recordset is empty, so the code I posted
tests EOF first to see if the recordset is empty, and only calls MoveLast if
it isn't.