Ok, I have not seen much information on this forum about pivotcharts and I have only been programming in vba since a the end of November, so what I ask of you is feedback as you learn more vba. I want to know how to improve my code.
Here is what you can do, and it will work because I have written and tested it. However, I have only tested it using Access 2002.
First, create your table.
Next, use the form wizard to create a form... make the selections necessary to select view as pivot chart and also select the table.
Now, create a second form in the design view. Add a button and set the on click event to procedure. Then add code similar to this to the vba procedure linked to the button.
Code:
Private Sub Command0_Click()
Dim db As Database
Dim rs As Recordset
Dim objPivotChart As OWC10.ChChart
Dim objChartSpace As OWC10.ChartSpace
Dim frm As Access.Form
Dim strExpression As String
Dim objSeries As OWC10.ChSeries
Dim frmName As String
Dim values
Set db = CurrentDb()
frmName = "frmpSalary"
'Open the form in PivotChart view.
DoCmd.OpenForm frmName, acFormPivotChart
Set frm = Forms(frmName)
'use this if you want it to format your chart for landscape printing (not necessary for viewing)
frm.Printer.Orientation = acPRORLandscape
Set rs = frm.Recordset
'Loop through Recordset to obtain data for the chart and put in strings.
rs.MoveFirst
'rs.Fields start at 0 and continue to the number of fields you have in your table -1
'Just make the values field the number corresponding to the column in the table you want to use as your data.
Do While Not rs.EOF
strExpression = strExpression & rs.Fields(1).Value & Chr(9)
values = values & rs.Fields(2).Value & Chr(9)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
'Trim any extra tabs from string
strExpression = Left(strExpression, Len(strExpression) - 1)
values = Left(values, Len(values) - 1)
'Clear existing Charts on Form if present and add a new chart to the form.
'Set object variable equal to the new chart.
Set objChartSpace = frm.ChartSpace
objChartSpace.Clear
objChartSpace.Charts.Add
'Set Titles
Set objPivotChart = objChartSpace.Charts.Item(0)
objPivotChart.HasTitle = True
objPivotChart.Title.Caption = "Wages"
'Add Series to Chart and set the caption.
objPivotChart.SeriesCollection.Add
With objPivotChart.SeriesCollection(0)
.Caption = "Wages"
.Type = chChartTypePie
'Add Data to the Series
.SetData chDimCategories, chDataLiteral, strExpression
.SetData chDimValues, chDataLiteral, values
'I don't know anything about setting colors
'Once you get this working tell me where you found the color information
.Points.Item(0).Interior.Color = 1
.Points.Item(1).Interior.Color = 111112
.Points.Item(2).Interior.Color = 55555533
End With
frm.SetFocus
Set frm = Nothing
Finally, add some references. In the vba editor... Tools... References...
I added Micorsoft Graph 10.0 Object Library and
Microsoft DAO 3.6 Object Library. Also, you should probably have stuff like Microsoft ActiveX Data Objects 2.5 Library.
Like I said I have only been at this for a couple of weeks so every day I am learning a lot more than I knew the day before. I would sincerely appreciate constructive feed back on code structure and references and colors.
I am not sure what the other references are that you might need as I emailed myself code I developed on another computer but forgot to include a list of reference.
Hope this helps. Like I said. I know the code works.
The next time I read this forum, I will probably know if it is possible to do the same thing without imbedding it in a form first.
