ACC: Data Changes Are Saved to the Incorrect Record

ID: Q191883


The information in this article applies to:


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:


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


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


Reproducing the Problem:
  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.


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

Last Reviewed: July 1, 1999