ACC: Main Form Record Edits Saved When Subform Gets Focus

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

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you navigate to a subform control, edits you make to a main form record are automatically saved. Validation rules for the main form record are evaluated and the BeforeUpdate and AfterUpdate events run.

CAUSE

If Microsoft Access did not save the main form record, it would be possible to create detail records that do not have a corresponding parent (main form) record. Creating detail records without a corresponding main form record would violate referential integrity rules.

For example, in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0), in the Orders form containing an order detail subform (the Orders Subform form) linked on a common Order ID field, if Microsoft Access did not save the main form Orders record immediately, it would be possible to create order detail records that are saved with an Order ID number for an order record that does not yet exist in the Orders table. If a problem were to occur and the main form order record could not be saved, there would be "dangling" order detail records in your database.

STATUS

This behavior is by design.

MORE INFORMATION

Microsoft Access does not group edits made to the main form record and to subform record(s) in a single transaction. Edits to the main form record are maintained in a single transaction. Edits to each subform record are also maintain in their own separate transactions. Because Microsoft Access does not group record edits you make in a main form and in the subform in a single transaction, to maintain referential integrity, Microsoft Access must save the main form record (commit the transaction) before you can edit the subform.

For more information about transaction processing in forms, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q131535
   TITLE     : ACC: Unable to Undo or Rollback Main Form and Subform
                    Changes

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) and open the Orders form in Design view.

2. Change the Orders form's BeforeUpdate property as follows:

         =MsgBox("Orders BeforeUpdate!")
   

3. View the Orders form in Form view.

4. In the Salesperson combo box, select a different sales person.

5. Click anywhere in the Orders Subform form to give the subform the focus. Note that the message "Orders BeforeUpdate!" appears in a message box as the main form record is about to be saved.

REFERENCES

For more information about referential integrity, search the Help Index for "referential integrity," or ask the Microsoft Access 97 Office Assistant.