ACC: Data Changes Are Saved to the Incorrect Record

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

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:

1. 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 

2. 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:




Q151261 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:




Q193052 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:




Q172733 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

1. Open the sample database Northwind.mdb.



2. Create a new form based on the Order Details table.



3. Save the new form as frmOrderDetails.



4. Open the frmOrderDetails form in Design view.



5. Turn on the Control Wizards and add a combo box to the form.



6. 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.



7. Choose the Order Details table from the list of tables.



8. Move the OrderID field from the Available Fields list to the Selected Fields list, and click Next.



9. Click Finish, and then save the form.

1. Open the frmOrderDetails form in Form view.



2. Delete the first record.



3. 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.



4. Make a change to the Quantity field on the form.



5. Using the navigation buttons on the bottom of the form, move to the Next record.

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

Additional query words: pra corruption integrity

Keywords : kbdta FmrProp FmrHowto FmrProb
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbbug