Dynamic PivotChart Generation

[spongie1]

Has anyone worked with dynamically generating pivotcharts in Access 2002.

I am getting near duplicate instances of my data when I try to print my pivotchart using a print button that is generated by access.

I am just a beginner, and I am pretty sure that my lack of knowledge is hampering my abilities to develop code.

Here is what I have: I have a form that contains several combo boxes. Each combo box contains values that can be used to modify a pivotchart. For example, one combobox is used to select the period of the trendline for the pivotchart. To get a 4 week moving average select "4".

I have a query that I generated ahead of time.

I have vba code that constructs a new sql statement based on the values selected in the form. Then the vba sets the sql for the pre-generated query to my new sql statement using a commands like:

Set qryDef = db.QueryDefs(qryName)
qryDef.sql = strSQL

I have a second form linked to my query and set to display as a pivotchart. I have more vba to change the appearence of the pivotchart. For example, it adds a dynamically generated title that describes the fields in use, it adds descriptions for the x and y axis, and this is also where I add and modify my trendlines and set it to print in landscaped mode.

If I select items in the first form and hit the button that executes my vba, it will generate a new SQL statement based on the form inputs, save the sql in the query statement, open the form attached to that query, format the appearence of the pivotchart and display the pivot chart.

I can get to this point without any problems. In the debugger I only see on chart listed. However, when I close the form 2 which is attached to the query, and reopen it I get two pivot charts.

If I close the form and use the access generated print button designed to print a form, I get two charts. The first chart is my data formated with the appearence that I used vba to generate and the second chart is my data unformatted.

I think that this is all due to the fact that I am using a static query statement and when I reopen it it is generating its own pivotchart that is an near duplicate of my vba generated one.

I realize that I am probably breaking many rules assoicated with vba programming. I have been doing a lot of reading in the past 2 weeks and that is why I have been able to get as far as this, but I still have not read what I need to read in order for me to properly landscape print my pivotchart.

Here is a short snippet of my most relevant code. I realize that it is poorly documented, does not have any error handling, and is very poorly structured. Once I get it to do what I want it to do, I will rewrite it. (Any suggestions on how to improve it would be greatly appreciated.)

Private Sub Command193_Click()

  Dim strSQL As String, strGroup As String, strOrder As String, strWhere As String, strHaving As String
  Dim txtStartDate As String, txtEndDate As String
  Dim db As Database
  Dim rs As ADODB.Recordset
  Dim qryDef As QueryDef
  Dim dtWeeks As Integer
  Dim qryName As String, qryWeek As String

  Dim rs1 As Recordset
  Dim objPivotChart As OWC10.ChChart
  Dim objChartSpace As OWC10.ChartSpace
  Dim frm As Access.Form, Frm1 As Access.Form
  Dim strExpression As String
  Dim values
  Dim axCategoryAxis
  Dim axValueAxis
  Dim objSeries As OWC10.ChSeries
  
  strSQL = ""
  strGroup = ""
  strHaving = ""
  strWhere = "WHERE 1 = 1 "
  strOrder = ""
  
  Set db = CurrentDb()
  
  'qryName = "qryUT3" just numbers
  qryName = "qryUT3a" ' percentages
  
  If Me!Combo191 = "Line 1" Then
    strWhere = " WHERE qryUT2.Line=""Line 1"" "
  ElseIf (Me!Combo191 = "Line 2") Then
    strWhere = " WHERE qryUT2.Line=""Line 2"" "
  ElseIf (Me!Combo191 = "Big 10") Then
    strWhere = " WHERE qryUT2.Line=""Big 10"" "
  ElseIf (Me!Combo191 = "4 Pack") Then
    strWhere = " WHERE qryUT2.Line=""4 Pack"" "
  End If
  
  If (Not IsNull(Me!Combo196) And Not IsNull(Me!Combo198)) Then
    strHaving = strHaving & " HAVING (qryUT2.Date - Weekday(qryUT2.Date) + 1) >= #" & CDate(Me!Combo196) & "# "
    strHaving = strHaving & &quot; AND (qryUT2.Date - Weekday(qryUT2.Date) + 1) <= #&quot; & CDate(Me!Combo198) & &quot;# &quot;
  End If
  
  'CInt(Me!Combo188)[Date]-Weekday([Date])+1 AS Week
  strSQL = &quot; SELECT qryUT2.[Date]-Weekday(qryUT2.[Date])+1 AS Week, qryUT2.Line, Sum(qryUT2.MinSched) AS SumOfMinSched, Sum(qryUT2.DT) AS SumOfDT, &quot; & _
    &quot; Sum(qryUT2.Qty) AS SumOfQty, Sum([MinSched]-[DT]-[Qty]*480/(&quot; & CInt(Me!Combo188) / 870 & &quot;*4640)) AS UT FROM qryUT2 &quot;
    
  strGroup = &quot; GROUP BY (qryUT2.[Date]-Weekday(qryUT2.[Date])+1), qryUT2.Line &quot;
  
  strSQL = strSQL & strWhere & strGroup & strHaving
  
  Set qryDef = db.QueryDefs(qryName)
  qryDef.sql = strSQL
    
'  DoCmd.OpenQuery qryName, acViewPivotChart

  'Open the form in PivotChart view.
  DoCmd.OpenForm &quot;frmUT3a&quot;, acFormPivotChart
  Set frm = Forms(&quot;frmUT3a&quot;)
  Set Frm1 = Forms(&quot;Form1&quot;)
  Set rs1 = frm.Recordset
  frm.Printer.Orientation = acPRORLandscape
  
  'Loop through Recordset to obtain data for the chart and put in strings.
  rs1.MoveFirst
    Do While Not rs1.EOF
        strExpression = strExpression & rs1.Fields(0).Value & Chr(9)
        values = values & rs1.Fields(5).Value & Chr(9)
        rs1.MoveNext
    Loop
  rs1.Close
  Set rs1 = 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 objPivotChart = objChartSpace.Charts.Item(0)
  
  'Set Titles
  objPivotChart.HasTitle = True
  objPivotChart.Title.Caption = &quot;Unaccounted Time for &quot; & Frm1.Combo191
  objPivotChart.Title.Caption = objPivotChart.Title.Caption & &quot; between &quot; & Frm1.Combo196 & &quot; and &quot; & Frm1.Combo198
  objPivotChart.Title.Caption = objPivotChart.Title.Caption & &quot; using a &quot; & Frm1.Combo204 & &quot; week moving average&quot;
  objPivotChart.Title.Caption = objPivotChart.Title.Caption & &quot; and an estimated Pieces per Minute of &quot; & Frm1.Combo188
  
  'Set a variable to the Category (X) axis.
  Set axCategoryAxis = objChartSpace.Charts(0).Axes(0)
  
  'Set a variable to the Value (Y) axis.
  Set axValueAxis = objChartSpace.Charts(0).Axes(1)
  
  'The following two lines of code enable, and then set
  'the title for the category axis.
  axCategoryAxis.HasTitle = True
  axCategoryAxis.Title.Caption = &quot;Week&quot;
  objChartSpace.Charts(0).Axes(0).GroupingType = chAxisGroupingNone
  
  
  'The following two lines of code enable, and then set
  'the title for the value axis.
  axValueAxis.HasTitle = True
  axValueAxis.Title.Caption = &quot;Unaccounted Minutes&quot;
  
  'Add Series to Chart and set the caption.
  objPivotChart.SeriesCollection.Add
  
  With objPivotChart.SeriesCollection(0)
    .Caption = &quot;Orders&quot;
    
  'Add Data to the Series
    .SetData chDimCategories, chDataLiteral, strExpression
    .SetData chDimValues, chDataLiteral, values
    
    If (.Trendlines.Count = 0) Then
      .Trendlines.Add
    End If
    
      .Trendlines.Item(0).Type = chTrendlineTypeMovingAverage
      .Trendlines.Item(0).Period = CInt(Frm1.Combo204)
  End With
  
  frm.SetFocus
  Set frm = Nothing
  
End Sub

Here is my printing code:

Private Sub Command207_Click()
On Error GoTo Err_Command207_Click

    Dim stDocName As String
    Dim MyForm As Form, frm As Form

    stDocName = "frmUT3a"
    Set MyForm = Screen.ActiveForm
    
    DoCmd.OpenForm stDocName, acFormPivotChart
    Set frm = Forms(stDocName)
  
    DoCmd.SelectObject acForm, stDocName, True
    
    DoCmd.PrintOut
    DoCmd.SelectObject acForm, MyForm.Name, False

Exit_Command207_Click:
    Exit Sub

Err_Command207_Click:
    MsgBox Err.Description
    Resume Exit_Command207_Click
    
End Sub

Again to restate the problem... How to I get it to not print two relatively similar copies of the same data? I just want one copy.

Thanks,

Jason