robtrue
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.
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.
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.subformCont rol.Form
Set PTbl = Forms.MainForm.subformCont rol.Form.P ivotTable
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.
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.subformCont
Set PTbl = Forms.MainForm.subformCont
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.subformCont rol.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!!
Do you get an error on this line: Set subFormPTbl = Forms.MainForm.subformCont
I don't think you're missing the reference, otherwise you'd get a different error.
Go Gamecocks!!
ASKER
There is no error in the line:
Set subFormPTbl = Forms.MainForm.subformCont rol.Form
The error is with the PivotTable.
Set subFormPTbl = Forms.MainForm.subformCont
The error is with the PivotTable.
Can you upload the database? If so, remove any sensitive information first.
ASKER
Pleas find attached an Access 2010 .accdb file which illustrates the problem.
Database1.accdb
Database1.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You must refer to a Subform through it's host (i.e. Parent) form, so:
Set subFormPTbl = YourParentForm.YourSubform