Link to home
Start Free TrialLog in
Avatar of robtrue
robtrueFlag for United States of America

asked on

How to Use VBA Reference Pivot Table on Access 2010 Subform

I’m getting a type miss match (error 13) on the indicated line show below.

Dim subFormPTbl As Form    ' the subform with pivot table
Dim PTbl As PivotTable     ' refers to pivot table
Dim PTblFld As PivotField  ' a column field in the pivot table
Set subFormPTbl = Me.SuformWithPivot.Form
Set PTbl = subFormPTbl.PivotTable  ' < === ERROR HAPPENS HERE
For Each PTblFld In PTbl.ColumnFields
   If PTblFld.Selected Then
   Debug.Print "Selected column field name is " & PTblFld.Name
   End If
Next

I have a main form with a subform. The subform refers to a form with a default view set to pivot table. All I need to do is to list the column fields that are selected by the user.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

What library are you using to reference a PivotTable?

You must refer to a Subform through it's host (i.e. Parent) form, so:

Set subFormPTbl = YourParentForm.YourSubformCONTROL.Form.ThePivotTable
Avatar of robtrue

ASKER

What library are you using to reference a PivotTable?

Below is the list of References:
Visual Basic For Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Access database engine Object Library
Microsoft Office 14.0 Object Library
Microsoft Excel 14.0 Object Library

I also tried the following changes:
   
    Set subFormPTbl = Forms.MainForm.subformControl.Form
   
    Set PTbl = Forms.MainForm.subformControl.Form.PivotTable

Still get the same runtime error at the same location. I’m wondering if I’m missing the right reference.

P.S. I was at USC back in the 80s.
Is "subformControl" the actual name of the Subform Control on your parent form?

Do you get an error on this line: Set subFormPTbl = Forms.MainForm.subformControl.Form? If not, then you're referencing the Subform correctly, which would indicate you've got troubles with the PivotTable call. I'm not sure, since I don't use it.

I don't think you're missing the reference, otherwise you'd get a different error.

Go Gamecocks!!
Avatar of robtrue

ASKER

There is no error in the line:
Set subFormPTbl = Forms.MainForm.subformControl.Form

The error is with the PivotTable.
Can you upload the database? If so, remove any sensitive information first.
Avatar of robtrue

ASKER

Pleas find attached an Access 2010 .accdb file which illustrates the problem.
Database1.accdb
ASKER CERTIFIED SOLUTION
Avatar of robtrue
robtrue
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial