When you make a change to the data in a form record, you find that the
changed data is saved to the wrong record.
Back to the top
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. |
|
Back to the top
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 (http://support.microsoft.com/kb/151261/EN-US/) 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
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 (http://support.microsoft.com/kb/193052/EN-US/) 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 (http://support.microsoft.com/kb/172733/EN-US/) ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
|
Back to the top
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
Back to the top
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.
Back to the top
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:
100132 (http://support.microsoft.com/kb/100132/EN-US/)
ACC: Four Ways to Move to a Record from a Combo Box
Selection
Back to the top
Help and Support
