RS MoveFirst

[MajP]

If you instantiate and open a recordset are there conditions where the pointer is not at the first record? I see a lot of code posted where people open a recordset, check to see if any records exist, move first, then loop through the records.

Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do While Not rs.EOF
loop
end if

I normally just write

Set rs = CurrentDb.OpenRecordset(strSQL)
Do While Not rs.eof
loop

Is the extra if check and movefirst redundant or can certain types of RS not open at the first record. Thanks.

 

[ddiamond]

In the scenerio you described, I believe it is redundant. If you had used the option dbOpenForwardOnly, not only would it have been redundant, it would have caused an error. Also, the rs.BOF is not necessary. In my experience the rs.EOF should be sufficient.

That being said, there is one case where the above code is relevent. If you need the rs.RecordCount. The RecordCount method is only accurate after the movelast method is called. So you might see the following:

Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveLast
cnt = rs.RecordCount
Do While Not rs.EOF
loop
end if

 

[JerryKlmns]

MajP

Set rs = CurrentDb.OpenRecordset(strSQL)

opens a DAO recordset (I'm sure you already know that). But ADO recordsets when opened, the current record is always the first. Checking for .EOF And .BOF says if there are any records returned (DAO & ADO)

 

[ddiamond]

Jerry,

Is there ever a case where an empty recordset would not cause the EOF method to return true but would cause a BOF method to return true?

- Dan

 

[MajP]

Thanks.

 

[JerryKlmns]

Dan

If there are no records, the BOF and EOF property settings are True

So the answer for your question is negative.
But, when moving back and forwards that's how to protect your "strol" ending in the ditch (but I think you already know that )

Sub MoveToRecord(strTowards As String)
....
Select Case
   Case "First"
      rs.MoveFirst
   Case "Back"
      rs.MovePrevious
      If rs.BOF Then rs.MoveFirst
   Case "Next"
      rs.MoveNext
      If rs.EOF Then rs.MoveLast
   Case "Last"
      rs.MoveLast
   Case Else
End Select
End Sub