Microsoft KB Archive/287106

= ACC2002: Cannot Set Form Data Properties During Pivot Events =

Article ID: 287106

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287106



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you are setting a data-related property during one of the PivotTable- or PivotChart-related events in a form, you receive the following error message:

Run-time error '2101'

The setting you entered isn't valid for this property.



CAUSE
Microsoft Access prevents you from setting data-related properties from certain events in order to prevent your code from causing an endless loop.



RESOLUTION
Set data-related properties from another event, such as the Open or Load event of the form.



STATUS
This behavior is by design.



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
 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.

 Open the sample database Northwind.mdb. Open the Orders form in Design view. On the View menu, click Code to view the module of the form.  Add the following Visual Basic for Applications code to the module of the form: Private Sub Form_Query Me.RecordSource = &quot;Orders Qry&quot; End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save.</li> 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.

</li></ol>

Additional query words: prb

Keywords: kberrmsg kbprb KB287106

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.