Graph report running very slow, HELP!

[dlee]

Several days ago I posted this in another group and got no replies, I
hope you guys can help me.

I created a report that contains a OLE Unbound object that is a graph.
I added the code below to the Detail, Format event. When I run the
report it takes approx. 50 seconds for it to open. If I run the query
that is it's data source, the query opens quickly. Is there anything I
can do to speed it up?

Thanks,
TD

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdef As DAO.QueryDef
Dim grph As Object
Dim grphchart As Object
Dim c As Integer
Dim r As Integer

Set db = CurrentDb()
Set qdef = db.QueryDefs("qry1")

qdef.Parameters(0) = [Forms]![frmReports]![StartDatePicker]
qdef.Parameters(1) = [Forms]![frmReports]![EndDatePicker]
qdef.Parameters(2) = [Forms]![frmReports]![cboVesselNumber]
qdef.Parameters(3) = [Forms]![frmReports]![cboProductID]

Set rst = qdef.OpenRecordset()
Set grph = Me!Graph1.Object
Set grphchart = grph.Application.DataSheet
grphchart.Cells.Clear

For c = 0 To rst.Fields.Count - 1
grphchart.Cells(1, c + 1) = rst.Fields(c).Name
Next c

r = 2
Do Until rst.EOF
For c = 1 To rst.Fields.Count
grphchart.Cells(r, c) = rst(c - 1).Value
Next c
r = r + 1
rst.MoveNext
Loop

rst.Close
db.Close

grph.Axes(2).MaximumScale = 9

End Sub

 

[Duane Hookom]

Is there a reason why you don't use a query as the Row Source of the graph?

 

[Guest]

I have used the Graph control from the standard toolbox with a lot of
success, especially in this case. If the standard control is not functional
for you, try a sub-report which contains the graphic control and use the four
fields as parent/child filters.

 

[dlee]

Yes, the user can select any of seven values to plot on the graph. I
will add code later to modify the sql string that is "qry1".

 

[dlee]

I'm interested in learning all I can about the graph control. How
would this speed up the report?