ACC2002: Pivot Toolbars Do Not Appear for Subform (282392)

RESOLUTION

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