Bugs: Bookmark Bug

This document was revised on September 7, 1998 to take into account further tests, information and revisions.  All such revisions are in blue.

Update: Office Service Pack 2 seems to have fixed this problem.

These tests were developed based on postings to the comp.databases.ms-access and microsoft.public.access.forms newsgroups from August 20, 1998 to August 25, 1998; as well as other information received after that. The bug in question was first reported by "Ness" on August 20.

The purpose of this document is to summarize the information on the many posts to these threads and to report the findings of testing on different scenarios.  A database with some of the manifestations and solutions is available; its use is described at the end of this document.

Thanks to Terry Kreft, Dev Ashish and David Fenton for allowing me to use their sample databases.  And of course thanks to all who posted to the threads on the newsgroups regarding this problem; there are too many to list here.  Most especially thanks to "Ness" for bringing this up in the first place.

I've done my best to be as thorough and careful as possible.  However, if you have more information or find problems with what is presented here or in the sample database, please let me know.

Feel free to reprint this as you like, but make sure to give appropriate credit.  Terry Kreft should be credited for part of the sample database, as well as for great editing and troubleshooting assistance.  Andy Baron should be credited for "Solution4"; the code therein, and the instructions on how to use it.

Keri Hardwick

   Download bookmarkbug.zip (contains Access 97 and Access 2 MDBs).

   191883: Data Changes Are Saved to the Incorrect Record

Disclaimer:

These findings are based on my own tests on my own computer (P200/32mb RAM; Access97ODE. Office SR-1; Jet update both installed. Jet version MSJET35 3.51.623.4). They are certainly not definitive nor exhaustive, they are simply offered as information. I make no claim as to their accuracy beyond reporting behavior I have seen on this machine. I also don't have any answers as to "why it is" - only a compilation of information as to "what it is." The only thing I feel I can state with absolute certainty is that this problem is a problem with bookmarks.

Description of Problem

When a record is deleted from a form's recordset, then bookmarks are used to go to another record more than 262 records away from the deleted one, it appears as though the record with the correct ID is being edited, but in fact a record offset positively from that one   number of records deleted) is actually edited.   What you see is NOT what you get - the wrong record is edited.

Although there may be other problems with using bookmarks and clones, this is the only problem discussed here.

Some additional points:

1.     The problem can be created using a direct bookmark, or by a bookmark based on the form's RecordsetClone.  Bookmarks are the one common feature in any method of creating the problem.

2.     The problem occurs whether the bookmark is set before or after the delete.

3.     The problem occurs whether or not a combo box is used, as long as a bookmark is set.

4.     The problem is not related to the bookmark pointing to the deleted record.

5.     The problem will still exist if you navigate to other records via "acceptable" means, edit them, then use a bookmark to go to another record.  The records you moved to via these acceptable means will be properly edited, but the record you go to via bookmark then edit will cause an edit on the wrong record.  "Acceptable" means include the scroll bar (continuous forms), the Access record selectors (arrows or record number) or via DoCmd.GotoRecord.  I did NOT test custom nav buttons.

6.     I was unable to create the same problem when adding an additional record; in my tests only deleting a record caused this problem.

The sample database includes four methods for creating the problem.

Solutions

There were four solutions that always avoided the problem in my tests, in the original tests.  After further testing, only one solution has proved to be reliable in all situations tested to date - Solution 4, Andy Baron's solution.  Note that the code for this solution was changed on September 6, 1998.  Revised code is included in this document.  Issues with the other solutions are discussed here.  Also, the stack error can now be avoided.

1.     Me.Requery in the form's After Delete Confirm event.  The requery can just be of the form (Me.Requery) , it does not need to be of the clone or of a clone object.  This makes sense as the clone does not need to be involved for the error to occur.

Issue with this solution: If the user turns off the Delete Confirm events, this event - and the Requery - will not occur.  If you can control these settings, this solution may still work for you.

2.     Me.Requery in the form's Before Delete Confirm event.  This requires you to create your own Delete Confirm message box, but allows for some navigation that the After Delete Confirm does not.

Issue with this solution: If the user turns off the Delete Confirm events, this event - and the Requery - will not occur.  If you can control these settings, this solution may still work for you.

3.     Opening a recordset object (rst) set to Me.RecordsetClone in the form's Open event; issuing an rst.Movelast command immediately after the "Set rst = ..." and then using this object for all FindFirst's, bookmark setting, etc.

Issue with this solution: This solution is only reliable for the first delete; it does not work if there are subsequent deletes.  Because of this, Solution #3 is too unreliable, and has been removed from the sample database.

4.     Andy Baron's RecordDelete and Resynch functions. 

Revisions 9/6/98:

· Delete confirmation occurs unless turned off in options.  Prior version provided no delete confirmation.

· New version works from subforms.

· New version work in all versions of Access (line continuation characters must be removed for 2.0)

Put the code that follows here in a global module. .

In every form and subform where bookmark navigation code is used, place the following three function calls:

On Delete: =RecordDeleted()

On Current: =Resynch([Form], "PKField1, PKField2,...")

On AfterDelConfirm: =Resynch([Form], "PKField1, PKField2,...")

 

These functions can, of course, be called from the appropriate event procedures rather than from the property sheet as shown above. If called from within an event procedure, use Me rather than [Form].

The second Resynch argument is a single string that contains a comma-delimited list of the names of all fields in the primary key of the recordsource of the form. If there is only one field in the primary key, then no comma is required: =Resynch([Form], "PKField") Or, in an event procedure: Call Resynch(Me,"PKField")

'-------------------------------------------------------------------------------
'Code Courtesy of
'Andy Baron
'
'-----------------------------------------
Option Compare Database
Option Explicit

Dim mfRecordDeleted As Integer
Dim mfConfirmIsOn As Integer


Function RecordDeleted()
mfRecordDeleted = True
End Function

Function Resynch(CurrentForm As Form, PKFieldNameList As String)
'Call from the Current and AfterDelConfirm events
' of any form or subform that uses bookmarks for navigation.
' (Also call RecordDeleted() from the Delete event.)
'For the CurrentForm argument, use [Form] if calling
' from the property sheet, or Me if calling from
' within the event procedure.
'Expects PKFieldList to be a string containing a
' comma-delimited list of the names
' of the fields in the primary key of
' the recordsource of the form.
' No comma required if there is only one field.
On Error GoTo Resynch_Err
Dim frm As Form
Dim varFieldName As Variant
Dim strWhere As String
Dim strDelimiter As String
Dim intCounter As Integer
If Not mfRecordDeleted Then
GoTo Resynch_Exit
End If
If Application.GetOption( _
"Confirm Record Changes") _
And Not mfConfirmIsOn Then
mfConfirmIsOn = True
GoTo Resynch_Exit
End If
Set frm = CurrentForm
Do
intCounter = intCounter + 1
varFieldName = Trim(GetToken( _
PKFieldNameList, intCounter, ","))
If Not IsNull(varFieldName) Then
strDelimiter = GetDelimiter( _
frm.RecordsetClone(varFieldName).Type)
strWhere = strWhere & " And " _
& varFieldName & "=" & strDelimiter _
& frm(varFieldName) & strDelimiter
Else
Exit Do
End If
Loop
' Strip off leading " And "
strWhere = Mid(strWhere, 6)
mfRecordDeleted = False
mfConfirmIsOn = False
frm.Requery
frm.RecordsetClone.FindFirst strWhere
frm.Bookmark = frm.RecordsetClone.Bookmark
mfRecordDeleted = False
Resynch_Exit:
Exit Function
Resynch_Err:
Select Case Err
Case 3021 'No current record
'Occurs if all records are deleted.
Case 3077 'Missing operator in expression
'Occurs if primary key value has
' an embedded double-quote character.
' Form will return to the first record.
Case Else
MsgBox Err & ": " & Error, , "Resynch"
End Select
mfRecordDeleted = False
Resume Resynch_Exit
End Function

Private Function GetToken( _
strsource As String, _
intItem As Integer, _
strDelim As String) As Variant
Dim intPos1 As Integer
Dim intPos2 As Integer
Dim intCount As Integer

For intCount = 0 To intItem - 1
intPos2 = InStr(intPos1 + 1, _
strsource, strDelim)
If intPos2 = 0 Then
intPos2 = Len(strsource) + 1
End If
If intCount <> intItem - 1 Then
intPos1 = intPos2
End If
Next intCount
If intPos2 > intPos1 Then
GetToken = Mid(strsource, _
intPos1 + 1, _
intPos2 - intPos1 - 1)
Else
GetToken = Null
End If
End Function

Private Function GetDelimiter( _
varDataType As Variant) As String
Select Case varDataType
Case DB_DATE
GetDelimiter = "#"
Case DB_MEMO, DB_TEXT
GetDelimiter = """"
Case Else
'Do nothing for numeric types.
'Return an empty string.
End Select
End Function
'-------------------------------------------------------------------------------

 

Additional Solution:

Additional Solution:

The solution of using Me.Requery immediately before setting a recordset object to the clone also avoids the error when editing the first record you've gone to via bookmark.   However, I encountered stack errors when continuing to use this navigation method to go to subsequent records if the form is in Continuous Form view and more than one record can be seen.  However, if your form is in Single Form view, or in Continuous Form view and sized such that only one record can be seen, this solution will work with no error.

Further testing has shown that the stack error occurs if rst.Requery is used, where rst is a recordset set to the form's RecordsetClone.  It does not occur if Me.Requery is used. 

Issue with this solution: This technique requires requerying the form's underlying recordsource every time the combo or other bookmark navigation technique is used.  This seems to be an unnecessary performance hit.  For this reason, the "requery on navigate" solution is not included in the sample database.  Furthermore, since the problem can be avoided by using "Me" rather than "rst", the sample forms showing this as a problem have been removed.

Other solutions tried/Other observations

1.     Issuing a Me.Dirty = False after the delete or right before setting the clone object does not impact this problem, but is necessary to save an edited but not yet saved record if you navigate off the record via a bookmark.

2.     Me.Refresh after the delete or right before setting the clone object does not impact this problem.

3.     Performing edits via "acceptable" navigation does not reset everything, although these edits will be on the correct records.

Trapping the problem: