Pie Chart Coloring

[darkwind]

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:

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:

'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.

[June7]

Not that I know of. I experimented with this once and it was the only method I could find. https://www.accessforums.net/showthread.php?t=29178