problems 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

 

[Larry Linson]

RecordCount is the number of Records traversed. It will not _necessarily_
reflect the number of Records in the Recordset unless you have done a
..MoveLast. Try doing that before you use the property and see if that
doesn't fix the problem.

Doing the manual actions apparently modifies the value... in one case
correctly, in the other, incorrectly.

Clarifications and followups should be here to the newsgroup, not by e-mail.
Thanks.

Larry Linson
Microsoft Access MVP

 

[Tim Ferguson]

Set rs = db.OpenRecordset("qryRecordsQueriedFromTable") ' a query
For i = 1 to rs.RecordCount - 1
blah blah blah
Next i

More typical method for traversing a recordset is to use the .MoveNext and
..EOF methods:

Set rs = db.OpenRecordset(etc, dbDontForgetToSpecifyTheTypeOfDynaset)

Do While Not rs.EOF
' do something here

rs.MoveNext

Loop

' come here when records finished
' check for empty set

If rs.BOF Then
' there weren't any records to process

End If


If you need the total number of records, then get the server to do it
properly, because there are too many gotchas with .RecordCount

dwRecordCount = DCount("qryRecordsFromTable")


Hope that helps

Tim F