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") ' 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
 
Try: (*untested*)

****
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs = db.OpenRecordset("qryRecordsQueriedFromTable")

'Ensure non-empty Recordset before moving to 1st Record
With rs
If (.EOF=False) Then
.MoveFirst
Do While (.EOF = False)
'Do something with the current Record
.MoveNext
Loop
Else
'Recordset is empty
End If
End With
rs.Close
Set rs = Nothing
Set db = Nothing
****

HTH
Van T. Dinh
MVP (Access)
 
Back
Top