this is an excerpt from an article about ADO and DAO, it is indeed a CursorType issue.
#3: Avoid moving violations
Moving through the records in a Recordset is a common task, but doing so comes with a few hazards. Using any move method will
generate an error if the Recordset object is empty. The most reliable check uses a simple If statement before executing a move method:
If Not(rst.BOF And rst.EOF) Then
...
The BOF property returns True when the current position is before the first record. Similarly, EOF returns True when the current position is
just beyond the last record. If both properties are True simultaneously, the Recordset is empty.
Tip: Including the Not operator is a matter of efficiency. If you expect the Recordset to contain records most of the time, it's more efficient
to include Not in the check.
#4: Expose an erroneous record count
The previous technique uses BOF and EOF to expose an empty Recordset before code can generate an error. Another way to avoid this
type of error is to use the RecordCount property to count the records as follows:
If rst.RecordCount <> 0 Then
This statement is a bit unpredictable with ADO because some ADO Recordset objects return -1 as the count. Use a Static or Keyset cursor to return a true record count in an ADO Recordset.
When using DAO, you must remember to populate (fully) the Recordset before counting, as follows:
rst.MoveFirst
If rst.RecordCount <> 0 Then
...
DAO populates with records as they are needed, and consequently, its RecordCount property considers only the records already
accessed. ADO, on the other hand, defines the RecordCount property as the total number of records, period. That's why this second
check for an empty Recordset is less flexible and can be troublesome.
Whether using RecordCount to check for the existence of records or to count records, you must remember the differences between DAO
and ADO:
When using DAO, populate (fully) the Recordset before checking for an empty Recordset or a total count.
When using ADO, use a static or keyset cursor to return the actual record count.
Tip: Don't use MoveLast with a large Recordset to check for an empty Recordset. Instead, use MoveNext. If RecordCount returns 1, you know the Recordset isn't empty.
#5: You can't move beyond the first or last record
In tip #3, I told you to use the BOF and EOF properties to test for an empty Recordset. You'll also use them individually when moving through records. Moving beyond the first or last record returns an error. For instance, the following code returns an error when the MoveNext method moves beyond the last record:
If rst![fieldname] = value Then
...do something...
rst.MoveNext
If you're moving forward, use EOF to inhibit the error that occurs when you move beyond the last record, as follows:
Do While Not rst.EOF
If rst![fieldname] = value Then
...do something...
End If
rst.MoveNext
Loop
As long as EOF equals False, meaning the current position is a valid record, this loop executes the If statement. Once the EOF property is True, the check eliminates the error that would otherwise occur by bypassing the MoveNext method in the loop.
