Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Access 97 Bookmark Bug

        0 votes: *****     6,202 views      No comments
by Keri Hardwick, 07 September 1998    (for Access 97)

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 Update Site 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   Download bookmarkbug.zip (contains Access 97 and Access 2 MDBs).

External Link   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:

Because these tests were not exhaustive, I developed a method for trapping the wrong record being edited.  I hope that if it turns out the problem can manifest itself in a way we have not yet identified (such that the solutions provided are not sufficient), this code will at least identify the problem so the data can be remedied and the developer can take steps to fix the problem.  This code works when there is a key field that uniquely identifies the record in the forms recordset.   ID is that field in this example.  No testing has been done for records with multiple field keys.

Declarations:

'-------------------------------------------------------------------------------
Dim idcheck

Private Sub Form_AfterUpdate()
Dim strMsg As String

If Me.ID <> idcheck Then
strMsg = "Inconsistency in record update." & vbCrLf & vbCrLf
strMsg = strMsg & "Current ID is " & Me.ID & vbCrLf
strMsg = strMsg & "Edited ID is " & idcheck & vbCrLf & vbCrLf
strMsg = strMsg & "This error indicates a problem. Please verify and correct data."
MsgBox strMsg, vbCritical
Me.Requery
Dim newrst As Recordset
Set newrst = Me.RecordsetClone
newrst.FindFirst "id = " & idcheck
Me.Bookmark = newrst.Bookmark
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
idcheck = Me.ID
End Sub
'-------------------------------------------------------------------------------

How to use sample database

v Open database TestEditWrongRecord. 

v Open form frmSwitch.  Create test data. 

These examples all use 1000 records.  Anytime you see "Create a new set of test data" in these instructions, open this form and create a new set of 1000 records.

The Problem

There are three forms which simply demonstrate different ways to cause the problem:  ShowProblem1, ShowProblem2 and ShowProblem3.  In all instructions, "Record X" means "the Record With ID = X", it does not refer to the record number in the navigation buttons.

             ShowProblem1:

This form demonstrates the problem without using RecordsetClone or FindFirst - only by using bookmarks.

1.     Open the form, delete record 1.

2.     Scroll to record 500.  Click "Bookmark this record."

3.     Click "Find It Again".  Notice that the record pointer is on record 501.

4.     Without changing the record pointer, scroll the form such that the records 500 and 501 are both off the screen, then scroll back so they are once again visible.

5.     Notice the record pointer is now on record 500.  Depending which way you scroll, record 501 may appear to vanish.  If you do the same set of scrolls in the opposite direction, all the records will show again, and the pointer should be on record 500.

6.     Edit the Num field of record 500. 

7.     Click in another record.  Notice that the record edit appears to be saved on record 500.

8.     Again scroll the form such that records 500 and 501 are both off the screen, then scroll back so they are once again visible.  You will now see that it was the record for  501 that was actually changed.

ShowProblem2:

This form also demonstrates quite clearly that the problem is caused by bookmarks alone. Note that you can’t task switch during this exercise as the view of the form will change.

1.     Create a new set of test data.

2.     Open form ShowProblem2, delete record 1.

3.     Use the scrollbar to move to and select record 500, make sure record 501 is visible

4.     Click on the info button and note that id = 500

5.     Click on the Set Bookmark button

6.     Note that the record pointer move to record 501

7.     Click on the info button and note that id = 500

8.     Edit the value in the num field on the record selected

9.     Before the value is written click on the info button and note that id=501

10.    Commit the record

11.    Click on the info button and note that the id=500

12.    Scroll both record 500 and record 501 off the screen and scroll them back onto the screen

13.    Note that the record-pointer is pointing at record 500 and that record 501 has actually been edited.

 

Now get ready to crash Access, using the form ShowProblem2.

1.     Create a new set of test data.

2.     Open form ShowProblem2, delete record 1.

3.     Use the record-selectors to move to the last record.

4.     Click on the Set Bookmark button and note that record pointer moves to the new record

5.     Click into the id field of the new record and press a key on the keyboard

6.     Access will crash.

 

ShowProblem3:

This form demonstrates the problem using a textbox and a button to go to that record id.  It also allows you to test refresh vs. requery and to see some information about the Dirty property and record id as the edit progresses.

1.     Create a new set of test data.

2.     Open form ShowProblem3, delete record 1.

3.     Type 500 in the textbox, then click "Find It"

4.     Click the info button.  Notice that Id is 500. Also notice that the Dirty property is False, indicating that explicitly setting the Dirty property to false will NOT avoid the bookmark going to the wrong record.

5.     Edit the Num field.

6.     Before pressing enter or clicking elsewhere on the form, click the "info" button again.  Notice the record id is now 501. 

7.     Click in another record.  As in ShowProblem1, it appears as though the edit has been saved on the correct record. Again scroll the form such that records 500 and 501 are both off the screen, then scroll back so they are once again visible.  You will now see that it was record 501 that was actually changed.

8.     Click "refresh".

9.     Edit the Num field of record 502.  Click elsewhere, then scroll to make 502 and 503 not visible the visible again.  Notice record 503 has actually been edited.  This shows that a Refresh is NOT sufficient to eliminate the problem.

10.    Click "requery"

11.    You've now been moved back to the top of the recordset.  Enter 504 in the textbox and click Find It.

12.    Edit the Num field of 504.  Click "info" before committing the change - notice the id has remained 504.  Click elsewhere, scroll back and forward - indeed record 504 was edited.

 

ShowProblem4:

This form demonstrates the problem using a combo box.  It also allows you to see the "info" and test refresh/requery as in ShowProblem3.

1.     Create a new set of test data.

2.     Open form ShowProblem4, delete record 1.

3.     Test as in ShowProblem3, just use the combo to navigate rather than the "Find It" button.

4.     Notice, in the Access 2.0 sample database, that the value changes to the edited value of record 501 as you type.

 

Form "TrapError" shows a technique for catching the problem.  If you use one of the solutions provided here, you shouldn't have the problem.  However, these tests have not been exhaustive, and there may be other ways to manifest the problem that the provided solutions would not avoid.  This trap should at least alert you or your users that there is an inconsistency in the update which has occurred and allow you to further work on the problem (as well as fix the data).   Unfortunately, I've only been able to figure out how to trap using both Before and After Update, not just Before Update, so I haven't figured out how to cancel the update in case of a problem; this only provides notification that there is a problem.  This form functions similarly to ShowProblem3.

1.     Create a new set of test data.

2.     Open form TrapError, delete record 1.

3.     Enter 500 in text box, click "Find It".

4.     Edit the Num field on record 500, click in another record.

5.     You should get a message box regarding the problem.  The form is then requeried and you are returned to the error which has incorrectly been edited.

Solutions

There are three forms (Solutions 1, 2, and 4) which demonstrate solutions that my testing has shown consistently avoid the problem in any manifestation (any manifestation I've found, that is), subject to the issue described above with Delete Confirm event requeries.   Although these techniques were tested against all problem scenarios shown here, this database only includes samples which work like ShowProblem3 (with the obvious addition of the "solution" code). 

            Solution1:

This solution uses Me.Requery in the form's After Delete Confirm event.  Note that using On Delete causes an error.

1.     Create a new set of test data.

2.     Open form Solution1, delete record 4.

3.     Note that the form moves the current record to record 1, this is because requery returns you to the first record in the recordset.

4.     Type 500 in the textbox, then click "Find It"

5.     Click the info button.  Notice that Id is 500. Edit the Num field.

6.     Before pressing enter or clicking elsewhere on the form, click the "info" button again.  Notice the record id is STILL 500. 

7.     Click in another record.  Scroll the form such that records 500 and 501 are both off the screen, then scroll back so they are once again visible.  You will now see that it was indeed record 500 that was changed.

 

             Solution2: (Code courtesy of Terry Kreft)

This solution uses Me.Requery in the form’s Before Delete Confirm event.  By using Me.Requery in this event we can return to an adjacent record after the delete.

1.     Create a new set of test data.

2.     Open form Solution2, delete record 4

3.     Note how you are now positioned on record 5, this is because after the requery we have used the newly synchronized bookmarks to return to the record adjacent to the record deleted
Note that this will fail if the last record in the recordset is deleted.

4.     Type 500 in the textbox, then click "Find It"

5.     Click the info button.  Notice that Id is 500. Edit the Num field.

6.     Before pressing enter or clicking elsewhere on the form, click the "info" button again.  Notice the record id is STILL 500. 

7.     Click in another record.  Scroll the form such that records 500 and 501 are both off the screen, then scroll back so they are once again visible.  You will now see that it was indeed record 500 that was changed.

 

            (Solution 3 Eliminated)

 

             Solution4: (Code courtesy of Andy Baron)

This uses two functions, RecordDeleted and Resynch in the Delete, Current and AfterDelConfirm events of the form.  Functions GetDelimiter and GetToken are also needed.   See the "Solutions" section above for instructions on how to incorporate these functions into your own forms.  The form in this sample uses this code.

1.     Create a new set of test data.

2.     Open form Solution4, delete record 1.

3.     Type 500 in the textbox, then click "Find It"

4.     Click the info button.  Notice that Id is 500. Edit the Num field.

5.     Before pressing enter or clicking elsewhere on the form, click the "info" button again.  Notice the record id is STILL 500. 

6.     Click in another record.  Scroll the form such that records 500 and 501 are both off the screen, then scroll back so they are once again visible.  You will now see that it was indeed record 500 that was changed.

 

The End!

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


Have your say - comment on this article.

What did you think of 'Access 97 Bookmark Bug'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).