ACC: Macro on Subform Control Executed by Main Form

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

SYMPTOMS

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

If a macro is attached to the OnEnter or OnExit property of the first control in the tab order of a subform, the macro will run each time you move from one record to another in the main form.

In addition, a macro attached to the OnEnter property of the first control in the tab order of the subform will run when the main form is opened, and a macro attached to the OnExit property of the first control in the tab order of the subform will run when the main form is closed.

NOTE: This behavior is also true for Visual Basic for Applications code (Access Basic code in Microsoft Access 2.0 or earlier) attached to the OnEnter or OnExit property.

CAUSE

To cause a macro to run only when you are actually moving in and out of the subform, use a test condition with a flag. A macro can set a flag on the main form to indicate whether the subform was entered by moving into the subform from the main form. The macro attached to the subform's OnEnter and OnExit properties can check this flag to see if you really did move into the subform and continue running if appropriate. The following steps demonstrate how to set up and test for this condition.

RESOLUTION

1. Create the following macro called Subform Flag:

         Macro Name     Action
         -----------------------
         Set            SetValue
         UnSet          SetValue
   
         Subform Flag Actions
         -----------------------
         SetValue
            Item: [Subform Flag]
            Expression: True
         SetValue
            Item: [Subform Flag]
            Expression: False
   
   

2. For a form named MyForm, add an unbound text box to the main form with the following properties:

         Form: MyForm
         Text Box: Subform Flag
            Name: Subform Flag (in version 1.x, ControlName: Subform Flag)
            Visible: No
            Default Value: False
   
   

3. Select the subform control on the main form and change the following properties:

         Form: MyForm Subform
         On Enter: Subform Flag.Set
         On Exit: Subform Flag.UnSet
   

4. Add the following macro action to the top of the macro that is attached to the OnEnter or OnExit property of the first control in the tab order of the subform. The macro will not perform any other actions if the subform flag is not set.

         Condition                       Action
         -----------------------------------------
         Parent![Subform Flag] = False   StopMacro
   
   

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access versions 1.x and 2.0) and create the following macro called Test:

         MacroName     Action
         --------------------
         Test          MsgBox
   
         Test Actions
         --------------------
         MsgBox
            Message: Testing!
   
   

2. Open the Orders Subform form and change the OnEnter and OnExit properties of the PropertyID box ( or Property ID box in versions 1.x and 2.0) box to Test, as follows:

         Form: Orders Subform
         OnEnter: Test
         OnExit: Test
   

3. Save the Orders Subform form, open the Orders form, and move among the records. Note that the message "Testing!" appears twice for each record.