ACC2000: Transaction Rollback Causes Recordset Cursor to Be Lost

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.

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.

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:

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

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.