Hi all,
I have a procedure which dictates the colors of a pie chart in a report. This is used to sync colors across different groups in the report; I.E., Item 1 in Group 1 should be the same color as it is in Group 7. The particular line used to set the color is:
Code:
Me.Graph.SeriesCollection(1).Points(i).Interior.Color = rs("chartColor")
Obviously the default coloring would simply assign colors in order of the Items in the group. Since different groups have different Items, they wouldn't normally be the same color. My procedure works correctly to correct this, however, it is very slow- after some testing, it takes about half a second to run just the one line in question! The report runs without this line in about 22 seconds, but due to the number of times I have to set these colors, takes 56 seconds with it enabled. I have definitively ruled out the loop as the course of the issue, as I tested by simply commenting out the listed line. Does anyone know of a different method of setting this color programatically?
Here's the full procedure for reference:
Code:
'Recordset with all Items from across the report
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Item FROM qryReport ORDER BY Item")
'Recordset with desired Chart Colors
Set rs2 = CurrentDb.OpenRecordset("SELECT chartColor FROM tblChartColors")
'Recordset with all Items in the Group
Set rs3 = CurrentDb.OpenRecordset("SELECT Item FROM qryReport WHERE Group =" & Me.txtGroup & " ORDER BY Item")
If (rs.EOF AND rs.BOF) OR (rs2.EOF AND rs2.BOF) OR (rs3.EOF AND rs3.BOF) Then GoTo PROC_ERR
rs.MoveFirst
rs2.MoveFirst
rs3.MoveFirst
'Tracks which Item we are on, which is also the position in the SeriesCollections
i = 0
Do Until rs3.EOF
Do Until rs("Item") = rs3("Item")
If rs.EOF Then
rs.MoveFirst
rs2.MoveFirst
Else
rs.MoveNext
If rs2.EOF Then
rs2.MoveFirst
Else
rs2.MoveNext
End If
End If
Loop
rs3.MoveNext
If rs2.EOF Then rs2.MoveFirst
i = i + 1
Me.Graph282.SeriesCollection(1).Points(i).Interior.Color = rs2("chartColor")
Me.Graph199.SeriesCollection(1).Points(i).Interior.Color = rs2("ChartCOlor")
Loop
Thanks in advance.