KB191883 - ACC: Data Changes Are Saved to the Incorrect Record

Article ID: 191883

Article Last Modified on 1/23/2007

APPLIES TO

Microsoft Access 2.0 Standard Edition
Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q191883

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you make a change to the data in a form record, you find that the changed data is saved to the wrong record.

CAUSE

This behavior can occur under the following two circumstances:

You are using the AbsolutePosition property as a surrogate record number to perform moves in a recordset in which you are deleting records. The following DAO example can be used to reproduce the issue on a table with more than 255 rows:

    Sub main()
        On Error GoTo ErrorHandler
        Dim db As DAO.Database, SQL As String
        Dim td As DAO.TableDef, fld As DAO.Field, qd As DAO.QueryDef
        Dim rs As DAO.Recordset
        Dim bkmk As Variant
        Dim nAbsolutePosition As Variant


        ' Open database with large table.
        Set db = DBEngine.OpenDatabase("C:\Northwind.mdb")
        Set db = CurrentDb()

        ' Open a dynaset or snapshot with hundreds of records.
        Set rs = _
        db.OpenRecordset("SELECT * FROM [Order Details]", _
        dbOpenDynaset)

        ' Populate the table.
        rs.Move (1000)
        bkmk = rs.Bookmark

        ' Delete a record.
        rs.MoveFirst
        rs.Delete

        ' Go to a bookmark way down the table.
        rs.Bookmark = bkmk

        ' Get the position so you can move back here.
        nAbsolutePosition = rs.AbsolutePosition
        Debug.Print "Absolute position = " & nAbsolutePosition _
        & ": UnitPrice = " & rs![UnitPrice]

        ' Move to position by moves.
        rs.MoveFirst
        rs.Move (nAbsolutePosition)
        Debug.Print nAbsolutePosition & " moves: UnitPrice = " _
        & rs![UnitPrice]

    Exit Sub

    ErrorHandler:
       MsgBox "An error has occurred "
       Resume Next
    End Sub

You wrote Visual Basic for Applications code or used the Combo Box or List Box Wizard to build Visual Basic for Applications code, which finds a record on your form and the following conditions occur:
1. You have more than 255 rows coming from the source from which you are getting data.
2. You delete a record on a form.
3. Without closing the form, you search for a different record in a way that makes use of the Bookmark property of the Form and RecordsetClone objects. (One way to do this is to use the Combo Box Wizard option to "Find a record on my form based on the value I selected in my combo box.")
4. You make a change to data in the found record.

RESOLUTION

To resolve this problem, do one of the following:

Obtain and install Microsoft Office 97 Service Release 2. For more information about getting this Service Release, see the following article in the Microsoft Knowledge Base:
151261 OFF97: How to Obtain and Install MS Office 97 SR-2
Requery the form prior to searching for a record using the Bookmark property. In the case of the code created by the Combo Box Wizard, you would add the line
```
       Me.Requery
```
at the beginning of the AfterUpdate event of the combo box as follows
```
      Sub ComboBoxName_AfterUpdate()

         ' Add this line to requery the form.
         Me.Requery

         ' The remaining code was generated by the wizard.
         ' Find the record that matches the control.
         Me.RecordsetClone.FindFirst "[Key] = " & Me!ComboBoxName
         Me.Bookmark = Me.RecordsetClone.Bookmark

     End Sub
```
Now when you edit data in the found record it will save properly.

To help you find suspect code, Microsoft has developed an add-in called the Find Bookmark Wizard. For more information about this wizard, see the following Microsoft Knowledge Base article:
193052 ACC: Find Bookmark Wizard Available in Download Center
Obtain Jet35sp3.exe from the Microsoft Web site; this file contains the updated version 3.51 of the Microsoft Jet database engine. For information on how to obtain Microsoft Jet 3.51, please see the following article in the Microsoft Knowledge Base:
172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Access 97, SR-2

MORE INFORMATION

This behavior is dependent upon both the amount of data in each record and the number of records between the record that is deleted and the record that is edited. You may not see this behavior if you have very few records in your table or if you do not search for a record far enough away from the deleted record.

Steps to Reproduce Problem

Creating the Search Form:

Open the sample database Northwind.mdb.
Create a new form based on the Order Details table.
Save the new form as frmOrderDetails.
Open the frmOrderDetails form in Design view.
Turn on the Control Wizards and add a combo box to the form.
On the first page of the Combo Box Wizard, click to select the option to "Find a record on my form based on the value I selected in my combo box," and then click Next.
Choose the Order Details table from the list of tables.
Move the OrderID field from the Available Fields list to the Selected Fields list, and click Next.
Click Finish, and then save the form.

Reproducing the Problem:

Open the frmOrderDetails form in Form view.
Delete the first record.
Using the combo box that you added in the "Creating the Search Form" section, select one of the records near the end of the list.
Make a change to the Quantity field on the form.
Using the navigation buttons on the bottom of the form, move to the Next record.

You should see that the data that you just entered on the form was saved to the next record.

NOTE: In Access 2.0 and 95, you may move two records ahead after clicking the navigation button. You will need to move back one record to see that the data was saved to the incorrect record.

For more information about using combo boxes to move to a record, please see the following:

100132 ACC: Four Ways to Move to a Record from a Combo Box Selection

Additional query words: pra corruption integrity

Keywords: kbbug kbpending KB191883