RecordsetClone.Recordcount not counting correctly

  • Thread starter Thread starter Rob Bunocore
  • Start date Start date
R

Rob Bunocore

I have built a form that administers a test to users. At the top of
the form, I want to show the user which question they are on and out
of how many...."Question 3 of 33".

I have two unbound text boxes, txtQuestion and txtQuestionTotal.

I have placed code into the OnCurrent property that says

me.txtQuestion = [CurrentRecord]
me.txtQuestionTotal = me.recordsetclone.recordcount

The code works except for one problem. Lets say there are 33
questions. When I first open the form, it says Question 1 of 1. Then
when I move to the next question, it changes to Question 2 of 33. If
I move back to the first, it says Question 1 of 33.

Why wouldn't the recordsetclone.recordcount be accurate when you first
load a form?

Robby
 
In general terms, writable recordsets will not properly calculate
their recordcount until all the records are accessed. In your case the
number becomes correct after the first one because they are sometimes
fetched in batches, and in this case the batch size is large enough to
get them all.

The way to avoid this is to use a read-only recordset. For reasons I
don't fully understand, this makes the recordcount update even when
it's first opened. I'm not exactly sure how you do this in a form,
however, as I do this in code.

Maury
 
You can us rst.MoveLast and then rst.MoveFirst to get the record count
updated. The .MoveLast forces it to fetch all the rows.
 
You can us rst.MoveLast and then rst.MoveFirst to get the record
count updated. The .MoveLast forces it to fetch all the rows.
Click to expand...

If you're using the form's RecordsetClone, I don't think you need to
bother to move the recordset pointer back to the first record. The
only justification for that would be if you were running code
elsewhere in your form using the RecordsetClone and that code
assumed that you started on the first record. In that event, *that*
code should start with .MoveFirst.

Also, your suggestion of rst.MoveLast is obviously assuming that the
code assigns a recordset to a recordset variable. This is completely
unnecessary, as the RecordsetClone already exists. In any code using
the RecordsetClone, it's easiest to use With/End With:

With Me.RecordsetClone
.FindFirst "[criteria]"
If .NoMatch Then
MsgBox "Not Found!"
Else
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With

This saves setting up a redundant copy of the RecordsetClone and
also saves all the cleanup.
 
Rob Bunocore said:
I have built a form that administers a test to users. At the top of
the form, I want to show the user which question they are on and out
of how many...."Question 3 of 33".

I have two unbound text boxes, txtQuestion and txtQuestionTotal.

I have placed code into the OnCurrent property that says

me.txtQuestion = [CurrentRecord]
me.txtQuestionTotal = me.recordsetclone.recordcount

The code works except for one problem. Lets say there are 33
questions. When I first open the form, it says Question 1 of 1. Then
when I move to the next question, it changes to Question 2 of 33. If
I move back to the first, it says Question 1 of 33.

Why wouldn't the recordsetclone.recordcount be accurate when you first
load a form?

Robby
Click to expand...

A bound form's recordsetclone.recordcount _is_ accurate. No need for
MoveLast because when the form loads, every record has already been
accessed. You can prove this by commenting out the code that fills
txtQuestionTotal and set it's ControlSource to:

=[Form].[RecordsetClone].[RecordCount]

So either do that or try calling the OnCurrent code from the form's OnLoad
event procedure.
 
Stuart McCall said:
A bound form's recordsetclone.recordcount _is_ accurate. No need for
MoveLast because when the form loads, every record has already been
accessed. You can prove this by commenting out the code that fills
txtQuestionTotal and set it's ControlSource to:

=[Form].[RecordsetClone].[RecordCount]
Click to expand...


Thanks not accurate, Stuart.

It can return too low a number if the form is bound to a large (tens of
thousands of records) linked table or query (so the source a dynaset, not a
Table type recordset.)

And it fails in Access 2007 also.
 
A bound form's recordsetclone.recordcount _is_ accurate.
Click to expand...

Not true at all. Anyone who has ever programmed their own custom
navigation controls (including a Record 1 of N display) knows that
it's not true.
 
Allen Browne said:
Stuart McCall said:
A bound form's recordsetclone.recordcount _is_ accurate. No need for
MoveLast because when the form loads, every record has already been
accessed. You can prove this by commenting out the code that fills
txtQuestionTotal and set it's ControlSource to:

=[Form].[RecordsetClone].[RecordCount]
Click to expand...


Thanks not accurate, Stuart.

It can return too low a number if the form is bound to a large (tens of
thousands of records) linked table or query (so the source a dynaset, not
a Table type recordset.)
Click to expand...

I've never run into that, so I didn't know it. Thanks for setting me
straight (David F also). Learning by provocation. The technique works
And it fails in Access 2007 also.
Click to expand...

Ah. I don't have A07 so can't comment. Do you mean it fails as an expression
in the controlsource too?
 
Back
Top