ACC2002: Cannot Set Form Data Properties During Pivot Events (287106)

MORE INFORMATION

By setting data-related properties from certain Pivot events, you can cause your code to go into infinite recursion, or an endless loop, that would eventually cause Access to crash.

For example, the Query event of a form is triggered when the PivotTable of the form queries its data source. If Access allowed you to set the RecordSource property of the form during the Query event, it would cause the Query event to be triggered a second time, which would cause your code to execute a second time and set the RecordSource property. This would cause the Query event to be triggered a third time, and this process would continue endlessly until Microsoft Access crashed. In order to prevent this from happening, Microsoft Access does not allow you to set data-related properties from PivotTable or PivotChart events. The following data properties for a form cannot be set from any PivotTable or PivotChart events:

• RecordSource
• Recordset
• RecordsetType
• Filter
• FilterOn
• OrderBy
• OrderByOn
• RecordLocks
• DataEntry
• InputParameters

The PivotTable- and PivotChart-related events in an Access form are:

• AfterRender
• AfterFinalRender
• AfterLayout
• BeforeQuery
• BeforeRender
• BeforeScreenTip
• CommandBeforeExecute
• CommandChecked
• CommandEnabled
• CommandExecute
• Connect
• DataChange
• DatasetChange
• Disconnect
• PivotTableChange
• Query
• SelectionChange
• ViewChange

Steps to Reproduce the Behavior

1. 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.
   
   
2. Open the sample database Northwind.mdb.
3. Open the Orders form in Design view.
4. On the View menu, click Code to view the module of the form.
5. Add the following Visual Basic for Applications code to the module of the form:

   Private Sub Form_Query()
      Me.RecordSource = "Orders Qry"
   End Sub
   					

6. On the File menu, click Close and Return to Microsoft Access.
7. On the File menu, click Save.
8. On the View menu, click PivotTable View.
   
   Note that you receive the following error message:
   Run-time error '2101'
   
   The setting you entered isn't valid for this property.