VBA to Create / Add Multiple Charts to Report

[Catch Wrestler]

I would like to create a report that has multiple charts. Preferably two per page. I would like line charts. I can create one of these charts using the Chart Wizard, but I can't figure out how to add additional charts. There are going to be 221 charts in total, so automating it is a must.

The source query I am using to create the chart with the Wizard is:

 SELECT KWB_Raw.Well, (Year([Date])) AS SampleDate, KWB_Raw.Observed, KWB_Raw.Simulated, KWB_Raw.ChartOrder
FROM KWB_Raw;
WHERE ChartOrder = 1

I want the next chart to contain the same data only 'ChartOrder' will be equal to 3, etc etc. The Wizard is also summarizing 'Simulated' and 'Observed' as the Max of each. I'm guessing the SQL statement will need to have a variable that increments +1 each cycle to get the ChartOrder to continue to increase.

 

[TimW]

Hi Catch
How I achieve this was to

1. Put the graph into a selection head and to link the master and child fields
2. On the Group header print section I put the following to update the graph

Private Sub GroupHeader1_Print(Cancel As Integer, PrintCount As Integer)
    On Error Resume Next
    Dim objGraph As Object
    Set objGraph = Me!Graph21.Object
    objGraph.Refresh
    DoEvents
    Set objGraph = Nothing
End Sub

I guess you would have chart order to be one of your groupings

HTH

TimW

 

[Catch Wrestler]

I'm only using one table, would that still be a master/child situation?

 

[TimW]

Yes, I think so.

you need to be grouping on your ChartOrder

Link Master fields ChartOrder
Link Child Fields ChartOrder

The graph needs to be in a ChartOrder Header

The graphs can only be viewed in Print Preview mode

Tim