Issue with Pivot Chart

[Andrew Smith]

I created an Access db for a client a while back which they now need packaged
up with the runtime access client. One of the forms used was a pivot chart
that allowed the user to create their own ad-hoc reports. The limitations of
the runtime verison prevent me from using the standard add fields dialogue so
I have had to create a form to do this for me. The problem I have is that
some of the field names in the source table are more than 24 characters long
(and are not changeable in the source table/query). When I try and add these
fields to the chart/table I get a RTE 9. I would really appreciate any
suggestions as to how I could get around this issue.

The code I am using is below.


Select Case optDropZone

Case 1 ' Filter
frm.PivotTable.ActiveView.FilterAxis.InsertFieldSet
frm.PivotTable.ActiveView.FieldSets(lstField.Value)

Case 2 'Data
On Error GoTo aggErr
Select Case cmbAgg
Case "sum"
frm.PivotTable.ActiveView.AddTotal
txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionSum
Case "Average"
frm.PivotTable.ActiveView.AddTotal
txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionAverage
Case "Count"
frm.PivotTable.ActiveView.AddTotal
txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionCount
Case "Min"
frm.PivotTable.ActiveView.AddTotal
txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionMin
Case "Max"
frm.PivotTable.ActiveView.AddTotal
txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionMax
End Select
'frm.PivotTable.ActiveView.DataAxis.InsertTotal
frm.PivotTable.ActiveView.Totals("country")
Set chtSpace = frm.ChartSpace
chtSpace.SetData chtSpace.Constants.chDimValues,
chtSpace.Constants.chDataBound, txtName.Value


Case 3 'series
frm.PivotTable.ActiveView.ColumnAxis.InsertFieldSet
frm.PivotTable.ActiveView.FieldSets(lstField.Value)

Case 4 'Category
frm.PivotTable.ActiveView.RowAxis.InsertFieldSet
frm.PivotTable.ActiveView.FieldSets(lstField.Value)

End Select

 

[Andrew Smith]

Sorry I just re-read my post and realised I had not really explained where
the issue was properly. Its only with adding totals to the data area of my
chart that I get errors

the line below is where the issue appears:

frm.PivotTable.ActiveView.AddTotal txtName.Value,
frm.PivotTable.ActiveView.FieldSets(lstField.Value).Fields(lstField.Value),
plFunctionSum