Problems with RecordCount method

  • Thread starter Thread starter Neal Tipton
  • Start date Start date
N

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")
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. 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
 
Neal,

From the helpfile:
Use the RecordCount property to find out how many records
in a Recordset or TableDef object have been accessed. The
RecordCount property doesn't indicate how many records are
contained in a dynaset-, snapshot-, or forward-only–type
Recordset object until all records have been accessed.
Once the last record has been accessed, the RecordCount
property indicates the total number of undeleted records
in the Recordset or TableDef object. To force the last
record to be accessed, use the MoveLast method on the
Recordset object. You can also use an SQL Count function
to determine the approximate number of records your query
will return.

To solve your problem, Place rs.Movelast and then
rs.Movefirst before the For loop

Dave
 
Back
Top