Hi David. My experience is with JET tables and DAO recordsets. From memory,
you work with other types as well, so you may need others to contribute from
wider experience as well.
Here are 8 cases where the bookmark can be unavailable, not current, wrongly
reported, or unreliable. I do use bookmarks, and use them regularly, but
these are the cases to be aware of AFAIK.
A. New record
==========
Test Me.NewRecord
Note: All versions of Access can report a *spurious* bookmark at the new
record once the form is dirty. My testing suggests that Access is wrongly
reporting the bookmark of the most recently visited record when this
happens. (It's been doing this since version 2, so I guess we have no chance
of getting this bug fixed.)
B. No records
==========
Test Me.RecordsetClone.RecordCount = 0
IIRC, those who work with ADO recordsets as well tend to go for:
Me.BOF And Me.EOF
to avoid the -1.
This also handles the case where the form's detail section goes completely
blank because there are no records and no new record can be added (so
NewRecord is not applicable.)
C. Not found
=========
If one of the Find methods (e.g. FindFirst) failed to locate a match, the
Bookmark is undefined. It may be EOF, but I don't believe you can trust
that. Test NoMatch.
D. Lifetime
========
In Access 1 and 2, it was safe to assume that the bookmark of a form's
RecordsetClone would be where it was previously. When the 32-bit versions
came out, this assumption broke some of my code. I had to learn that the
lifetime of the bookmark was limited, and could not be trusted to be
consistent between references.
There are obvious things that can render the bookmark obsolete, such as a
requery, filter/sort, or reassigning the RecordSource. Even beyond those, I
do not trust the bookmark to be anywhere, i.e. I assume it is not defined
unless I have just defined it explicitly using the object reference where I
need to work with it.
E. After AddNew
============
The Access documentation indicates that the Bookmark of a recordset will not
necessarily be pointing to the new record after an AddNew. It usually seems
to, but apparently you cannot rely on this without setting it to
LastUpdated.
F. During/After Deletion
=================
Access 2002 SP3 wrongly generates the "No Current Record" when you delete a
record in the form. From memory, this occurs in Form_BeforeDelConfirm and
Form_AfterDelConfirm, and it recovers shortly after the delete.
G. Default workspace closed
====================
Sometimes you find a developer has closed the default workspace (typically
after a transaction.) Access is too kind, and just opens it again when it is
needed. However, closing the default workspace has the side effect of
closing all open databases in the default workspace, which includes the
default database and any that the RecordsetClone relies on. The default
database automatically opens again, but the others do not.
Therefore, after this coding error you may find that the RecordsetClone of a
form is an invalid object reference (error 91 from memory) until the form is
closed and opened again.
H. Bookmark bug
=============
Other than an unpatched Access 2000:
http://support.microsoft.com/?id=238134
or linked 3rd party database:
http://support.microsoft.com/kb/903074/
I have not observed this in JET 4.
However, even a fully patched Access 97 database can still mismatch the
form's bookmark and the RecordsetClone's bookmark after a deletion. The
database where I have logged this all involved forms bound directly to large
tables (100k+ records.) We never solved the problem; we simply worked around
it by redesigning the form so it was bound to a single record at a time.
Hopefully that's most of them David.
