Part II: Programming Microsoft Access
498
The Use_EOF_BOF procedure illustrates using EOF and BOF in an ADO Recordset:
Public Sub Use_EOF_BOF()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.Open “SELECT * FROM tblCustomers “ _
& “WHERE State = ‘NY’ “ _
& “ORDER BY Company”
Do Until rs.EOF
Debug.Print rs!Company
rs.MoveNext
Loop
rs.MoveLast
Do Until rs.BOF
Debug.Print rs!Company
rs.MovePrevious
Loop
rs.Close
Set rs = Nothing
End Sub
Previous examples in this chapter have included code like this. The main differences are checking
EOF and BOF state before executing the MoveLast and MovePrevious methods. Notice that
these properties change to True only after these methods have executed. When moving toward
the end of the recordset, the EOF value is checked after MoveNext has executed (at the top of the
Do Until loop).
Counting records
It’s often very useful to know how many records are in a recordset before beginning operations
that may take a long time. Otherwise, a user may unwisely select criteria that return too many
records to handle efficiently. Fortunately, ADO Recordset objects provide a RecordCount
property that tells you exactly how many records are present in the recordset:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.Open “SELECT * FROM tblCustomers”
Debug.Print “RecordCount: “ & rs.RecordCount
The RecordCount property is not valid for forward-only recordsets. Notice that the
CursorType is set to adOpenStatic in this code fragment. If it’s set to adOpenFor-
wardOnly, the RecordCount property is set to –1 and does not change while the recordset is
in memory.
