Subject: Your article on the Access bug
Date: Tue, 25 Aug 1998 22:39:14 -0400
From: "David W. Fenton" <DFENTON@BWAY.NET>
To: miker@cnet.com
I think it's great you got this out quickly.
I offer these corrections:
Duncan said the problem occurs under a specific
scenario. First, a person must be working with a
long set of records in an Access form. Users
report that the flaw affects forms displaying
more than 200 records.
It actually occurs in recordsets where you move to the 263rd record or
later. This has been demonstrated beyond doubt.
Duncan would not confirm the exact number of
records. For the flaw to occur, users must delete
a record from the record set, use Access's
Combo Box (a feature intended to ease access to
database records) to edit another record, and
then save the changes. . .
You don't need to use a combo box. You need to use a method of
navigation around the record set that uses a copy of the data (called a
"RecordsetClone") to find a pointer to the real records (a Bookmark).
You navigate to a matching record in the RecordsetClone with the
FindFirst method, and then set the main recordset's Bookmark equal to
the Recordset's Bookmark. This effectively makes the sought after record
the current record.
One can do this with a combo box, a command button, or type it in the
"Immediate Window," which is like command line for Visual Basic
commands.
The purpose of a combo box is to act as a lookup. Most people call it a
"list box" or "dropdown list." Usually it's used for small numbers of
pre-defined choices in order to make sure that data gets entered in a
regularized fashion. But it can also be used as a way of presenting a
"find" function to the user, for example, presenting "Lastname,
Firstname" for each of the records in a database. When implemented with
the RecordsetClone.FindFirst and the Bookmark, choosing one of the names
in the combo box moves to the record for the name chosen.
This is but one use for a combo box, and but one way of triggering the
bug.
. . . Access applies the
changes to the record just before the intended
target of the change, Duncan said.
Wrong. It's the record just _after_ the intended target. That is, if you
delete one record, and navigate to a later record by the method outlined
above (with or without a combo box) the change gets applied to the
_next_ record beyond the displayed one. Delete two records, and it's
_two_ records past the displayed one. The edits are always posted n
records past the displayed record, where n is the number of records that
have been deleted before the edited record.
"If a user were to delete a record at the
beginning of a record set and then edit a later
record without using the Combo Box, the error
probably will not occur," Duncan said.
Duncan is wrong. A combo box is not necessary. It is the
Recordset.FindFirst combined with setting the Bookmark. This can be done
with any user interface control available to the Access developer.
He said the workaround is a simple process.
First, users need to go into Access's Design
View and right-click on Combo Box, which
displays a dialog box. Then, users need to type
one line (me.requery) into the dialog box and
save it.
This is gibberish.
Me.requery will requery the underlying recordset. However, if you put it
in your find combo box, it will cease to function properly (I know --
I've tested: see
<http://www.bway.net/~dfassoc/download/Access/rsClone.zip>).
To _really_ fix it, there are several alternatives:
1. Make sure you do Me.Requery each time a record is deleted. This
synchronizes the RecordsetClone with the actual recordset of the form.
You can then navigate with RecordsetClone.FindFirst and Bookmarks
without fear.
2. Requery the RecordsetClone before doing the FindFirst. This
synchronizes it with the form's underlying recordset and prevents the
problem from occuring.
3. Save the current record before setting the form's Bookmark equal to
the RecordsetClone's Bookmark. I do not know why this one works, but it
does.
David W. Fenton
http://www.bway.net/~dfenton
dfenton@bway.net
http://www.bway.net/~dfassoc