ACC2000: Transaction Rollback Causes Recordset Cursor to Be Lost (248968)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q248968
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

If you use transaction processing to validate a user's input in the BeforeUpdate event of a subform, and if you roll back the transaction when the data is not valid, the data in the subform will be lost, and the subform will not work until the user closes and then re-opens the form.

CAUSE

The Rollback action on the Workspace object closes all cursors on the recordset clone. Therefore, the current record position in the recordset clone is lost, and the subform is no longer synchronized with the main form.

RESOLUTION

To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

STATUS

Microsoft has confirmed that this is a problem in Access. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.

MORE INFORMATION

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample database Northwind.mdb.
  2. Click Forms under Objects, and then click New.
  3. In the New Form dialog box, click Form Wizard, and then click OK.
  4. In the Tables/Queries list, click Table: Orders.
  5. Move the OrderID field and the OrderDate field from the Available Fields list to the Selected Fields list.
  6. In the Tables/Queries list, click Table: Order Details.
  7. Move the OrderID field and the Product ID field from the Available Fields list to the Selected Fields list, and then click Next.
  8. Click Form with subform(s), and then click Finish.
  9. Open the new subform in Design view.
  10. Add the following code to the BeforeUpdate event for the subform:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim ws As Workspace
   Set ws = DBEngine.Workspaces(0)
   With ws
      .BeginTrans
      .Rollback
   End With
   Cancel = True
End Sub
  11. Add the following code to the OnCurrent event of the subform:
    Private Sub Form_Current()
   Dim lngCount As Long
   lngCount = Me.RecordsetClone.RecordCount
End Sub
  12. Browse through the main form, select a new product in the ProductID field in the subform, and try to save the record. Note that the data in the record is lost, and that you get the following error message:
    No Current Record.
    Click OK, and then press ESC to quit edit mode.
Modification Type:MajorLast Reviewed:11/25/2002
Keywords:kbbug KB248968
©2002 Microsoft Corporation. All rights reserved.