In Microsoft Access, when you set the focus to a subform in PivotTable or PivotChart view, the appropriate Pivot toolbars do not appear as expected.

To work around this issue, use one of the following methods:

• Right-click the subform to display the Pivot context menu for the view that is displayed in the subform.-or-
  
  
• Write custom Microsoft Visual Basic for Applications (VBA) code to handle the display of the Pivot toolbars.
  
  Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To write custom VBA code for this purpose, follow these steps:
  1. Open the main form in Design view.
  2. On the View menu, click Code to view the form's module.
  3. Add the following code to the form's module:

     Option Compare Database
     Option Explicit
     
     Private Const strToolbarPivotTable = "PivotTable"
     Private Const strToolbarPivotChart = "PivotChart"
     Private Const strToolbarPivotFormat = "Formatting (PivotTable/PivotChart)"
     Private Const strToolbarFormView = "Form View"
     Private boolSubFormFocus As Boolean
     Private boolFormActive As Boolean
     
     Private Sub HandleSubFormToolbars()
        Dim intDefaultView As Integer
        Dim strFormName As String
         
        If boolSubFormFocus And boolFormActive Then
           strFormName = "Sales Analysis Subform1"
           intDefaultView = Me.Controls(strFormName).Form.DefaultView
           Select Case intDefaultView
              Case acViewPivotTable
                 DoCmd.ShowToolbar strToolbarPivotTable, acToolbarYes
                 DoCmd.ShowToolbar strToolbarPivotFormat, acToolbarYes
                 DoCmd.ShowToolbar strToolbarFormView, acToolbarNo
              Case acViewPivotChart
                 DoCmd.ShowToolbar strToolbarPivotChart, acToolbarYes
                 DoCmd.ShowToolbar strToolbarPivotFormat, acToolbarYes
                 DoCmd.ShowToolbar strToolbarFormView, acToolbarNo
              Case Else
                 DoCmd.ShowToolbar strToolbarFormView, acToolbarWhereApprop
                 DoCmd.ShowToolbar strToolbarPivotTable, acToolbarWhereApprop
                 DoCmd.ShowToolbar strToolbarPivotFormat, acToolbarWhereApprop
                 DoCmd.ShowToolbar strToolbarPivotChart, acToolbarWhereApprop
           End Select
        Else
           DoCmd.ShowToolbar strToolbarFormView, acToolbarWhereApprop
           DoCmd.ShowToolbar strToolbarPivotTable, acToolbarWhereApprop
           DoCmd.ShowToolbar strToolbarPivotFormat, acToolbarWhereApprop
           DoCmd.ShowToolbar strToolbarPivotChart, acToolbarWhereApprop
        End If
     End Sub
     
     Private Sub Form_Activate()
        boolFormActive = True
        HandleSubFormToolbars
     End Sub
     
     Private Sub Form_Deactivate()
        boolFormActive = False
        HandleSubFormToolbars
     End Sub
     
     
     Private Sub Sales_Analysis_Subform1_Enter()
        boolSubFormFocus = True
        HandleSubFormToolbars
     End Sub
     
     Private Sub Sales_Analysis_Subform1_Exit(Cancel As Integer)
        boolSubFormFocus = False
        HandleSubFormToolbars
     End Sub
      
     						

  4. Press ALT+F11 to return to Access.
  5. On the File menu, click Save.
  6. Close the form.
  7. Open the Sales Analysis form in Form view.
  8. Click anywhere in the subform.
     
     Note that the PivotTable and Pivot Formatting toolbars appear on the form.
  9. Click the Show PivotChart button.
  10. Click anywhere in the subform.
  Note that the PivotChart and Pivot Formatting toolbars appear on the form.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce 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.

1. Open the sample database Northwind.mdb.
2. Open the Sales Analysis Subform1 form.
   
   Notice that Access displays the PivotTable and PivotTable Formatting toolbars.
3. Close the form.
4. Open the Sales Analysis Subform2 form.
   
   Notice that Access displays the PivotChart and PivotChart Formatting toolbars.
5. Close the form.
6. Open the Sales Analysis form.
7. Click anywhere in the subform.
   
   Notice that Access continues to display the default Form View toolbar, rather than the PivotTable toolbars.
8. Click the Show Pivot Chart button to change the subform into PivotChart view.
9. Click anywhere in the subform. Notice that Access continues to display the default Form View toolbar, rather than the PivotChart toolbars as expected.

For more information about using subforms, click Microsoft Access Help on the Help menu, type about subforms in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about PivotChart and PivotTable views of forms and subforms, click Microsoft Access Help on the Help menu, type about designing a pivottable or pivotchart view in the Office Assistant or the Answer Wizard, and then click Search to view the topic.