Problem with RecordCount method

[Neal Tipton]

I have a little sub that contains the following:
Dim i as Integer
Dim db as Database
Set db = CurrentDB
Dim rs as Recordset
Set rs = db.OpenRecordset("qryRecordsQueriedFromTable") ' a query
For i = 1 to rs.RecordCount - 1
blah blah blah
Next i

This produced an error inside the loop due to the value of i going too high.
The instructions in the loop involve copying information from the next
record and so the loop must stop one before the end to work properly.
Locals window revealed that the RecordCount at the time of the error was the
total number of records (not minus one as intended). I tried the following:

Dim i as Integer
Dim stophere as Integer
Dim db as Database
Set db = CurrentDB
Dim rs as Recordset
Set rs = db.OpenRecordset("qryRecordsQueriedFromTable")
stophere = rs.RecordCount - 1
For i = 1 to stophere
blah blah blah
Next i

This produced the same error. On checking the value of the variables, lo
and behold "stophere" was the value of the total number of records from the
query plus 119! What's going on? I even tried putting the following lines
right before the loop:
Debug.Print rs.RecordCount -1
Debug.Print stophere

After the error halted the program, the immediate window contained the
too-large number two times (where is it getting those 119 extra records?).
When I typed in "? rs.RecordCount - 1" I got
the total number of records in the query, minus one, as it was supposed to
be.

Another wrinkle: This part of the subroutine used to work perfectly!

Thanks in advance,
Neal

 

[Dirk Goldgar]

I have a little sub that contains the following:
Dim i as Integer
Dim db as Database
Set db = CurrentDB
Dim rs as Recordset
Set rs = db.OpenRecordset("qryRecordsQueriedFromTable") ' a query
For i = 1 to rs.RecordCount - 1
blah blah blah
Next i

This produced an error inside the loop due to the value of i going
too high. The instructions in the loop involve copying information
from the next record and so the loop must stop one before the end to
work properly. Locals window revealed that the RecordCount at the
time of the error was the total number of records (not minus one as
intended). I tried the following:

Dim i as Integer
Dim stophere as Integer
Dim db as Database
Set db = CurrentDB
Dim rs as Recordset
Set rs = db.OpenRecordset("qryRecordsQueriedFromTable")
stophere = rs.RecordCount - 1
For i = 1 to stophere
blah blah blah
Next i

This produced the same error. On checking the value of the
variables, lo and behold "stophere" was the value of the total number
of records from the query plus 119! What's going on? I even tried
putting the following lines right before the loop:
Debug.Print rs.RecordCount -1
Debug.Print stophere

After the error halted the program, the immediate window contained the
too-large number two times (where is it getting those 119 extra
records?). When I typed in "? rs.RecordCount - 1" I got
the total number of records in the query, minus one, as it was
supposed to be.

Another wrinkle: This part of the subroutine used to work perfectly!

Thanks in advance,
Neal

I could swear I saw an earlier version of this question, with perfectly
good replies explaining how to get the accurate record count. Did you
follow up in that thread? If replies don't answer your question, the
respondents generally want to know it.

I have no idea why you're getting the number you're getting -- in fact,
I can't see how it's possible with the code you've posted -- but I can
say for sure that you're not doing it the right way. First, if it was
your intention to loop through the record in the recordset, a better way
is simply to loop until rs.EOF = True (using .MoveNext inside the loop).
However, the way to get an accurate value from the .RecordCount property
of a dynaset-type recordset is to move to the last record first, then
interrogate the property. Like this:

Set rs = db.OpenRecordset("qryRecordsQueriedFromTable")
With rs
If Not .EOF Then
.MoveLast 'go to last record
.MoveFirst 'go back to first, for looping through records
End If
For i = 1 to .RecordCount - 1
' blah blah blah
Next i
End With

I note that, if rs.RecordCount is accurate, your loop will run one fewer
time than the actual number of records. I trust that's what you
intended.

 

[Peter Russell]

Until you get to the last record, the program does not know what
'recordcount' is. By then, it's already too late.

You need before your loop:
rs.movelast 'to get the recordcount
rs.movefirst ' to get back to the beginning

Are you sure there will always be records to process?
If there are none then the whole process will fail.

So you need to test for (rs.eof And rs.BOF) before moving anywhere.

Regards

Peter Russell

 

[Douglas J. Steele]

In addition to what the others have told you, in the event that you _are_
getting the correct recordcount, perhaps there's something wrong in your
blah blah blah code that's causing the error. Why not post it?