If your search with the Find method was unsuccessful—a fact that you can
detect by checking the EOF or BOF property—you're going to need to recover
somehow, because the record pointer will probably end up at the very first or
very last record of the Recordset. As noted in the preceding section, this won't
cause an error, but it probably will be confusing to the user.
Even if you
programmatically check the EOF or BOF property and display some sort of error
message in a message box, your user may still be inconvenienced. This is because
the user would probably just want to carry on with the record that was current
before initiating the unsuccessful search.
Why not put the record pointer back to the record it was on before the unsuccessful
search began? Luckily, the Recordset object supports a BookMark property that
enables you to store and, if necessary, reset the position of the record pointer.
Therefore, the strategy for using the Find method would as follows:
Store the Bookmark property to a Double variable.
Perform the Find.
If the Find is unsuccessful, restore the stored value of the Bookmark property
and show an error message.
Listing 8.12 in the preceding section illustrates how to use the Bookmark property
to restore cursor position after an unsuccessful call to the Find method.
IMPORTANT - Bookmark Property Limited to ClientSide Cursors:
The Bookmark property only contains valid information for ClientSide cursors.
If you try to use a Recordset's Bookmark property with a ServerSide cursor, you
will receive a runtime error
NOTE - BookMark Versus AbsolutePosition: You
might think that the AbsolutePosition property of the Recordset would work for
storing and resetting the record pointer's position. However, a record's AbsolutePosition
can change as other records are added or deleted. A BookMark is more stable because
it always points to the same physical location for the record. Also note that,
like the Bookmark, the AbolutePosition property only works for ClientSide cursors.