ACC: Unable to Undo or Rollback Main Form and Subform Changes

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you make editing changes to a main form record and one or more subform records, and then choose Undo Saved Record from the Edit menu, Microsoft Access does not undo changes made to the main form and the subform together.

If focus is in the main form, Undo Saved Record will undo changes to the main form record only. If focus is in the subform, Undo Saved Record will undo the changes made to the last record you made changes to and not to other records you may have edited in the subform.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q150895
   TITLE     : ACC95: Microsoft Access Sample Forms Available on MSL

   ARTICLE-ID: Q175066
   TITLE     : ACC97: Microsoft Access 97 Sample Forms Available on MSL

CAUSE

Because Microsoft Access does not group edits made to the main form record and edits made to one or more subform record in a single transaction, Microsoft Access cannot roll back the changes as a single unit.

RESOLUTION

To work around to this behavior, you may find the method described in the sample Microsoft Access 2.0 database, UNDOFM.MDB useful. For information on how to obtain UNDOFM.MDB, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q149358
   TITLE     : ACC2: Sample to Undo Changes in Forms Available on MSL

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access maintains editing changes you make to a single record in a form in a transaction independent of changes you make to other records in the same form or in other forms, including subforms. When you choose to undo a record, Microsoft Access rolls back the transaction only for that singular record in the form.

This behavior extends to subforms. Microsoft Access maintains editing changes for a subform record in one transaction independent of record edits you make to other subform records in the same form or in another form, including the main form.

Because of the way that Microsoft Access maintains editing changes, it is not possible to group editing changes you make to a main form and subform records in a single transaction. You cannot use the BeginTrans, CommitTrans and Rollback statements or methods for this purpose. Microsoft Access controls all transaction processing for a form in a separate workspace independent of the workspace that these actions and methods could be invoked in, and Microsoft Access does not expose this separate workspace.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x or 2.0).

2. Open the Categories form in Form view.

3. In the CategoryName field, change the category from "Beverages" to "Super Beverages."

   NOTE: In versions 1.x and 2.0, there is a space in Category Name.

4. Under Product Name in the subform, change the first record's name from "Chai" to "Tasty Chai."

5. Click the CategoryName field on the main form, and from the Edit menu, choose Undo Saved Record. Note that in the main form "Super Beverages" returns to "Beverages," but that in the subform "Tasty Chai" remains "Tasty Chai."

6. Click the first record in the subform, and from the Edit menu, choose Undo Saved Record. Note that "Tasty Chai" returns to "Chai."

Keywords          : FmsSubf kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided