Graph on an access form

Hi,

I have some code at the moment to draw a graph based on the values of a series of text boxes on an access form.

I’m happy with the code and how it works but I’m not convinced that it is the most efficient way of doing this. The graph takes about 1.2 seconds to redraw each time. The form is unbound so it is just getting the values from the text boxes. Just to check I got it to loop through and dump the text boxes values to debug.print and that did it instantly so it cant be that.

I suspect that it is trying to redraw the graph after each value is added. Is there a quicker way of doing this in VBA or am I stuck with it?

'**************************
        '** Draw the Call Deviation graph **
        '**************************
        .cells(1, 1) = "Start Time"
        .cells(1, 2) = "Deviation"
        lRT_actual = 0
        lRT_forecast = 0
        For i = 1 To 48
            lRT_actual = lRT_actual + Me.Controls("txtActual_" & i)
            lRT_forecast = lRT_forecast + Me.Controls("txtForecast_" & i)
            .cells(i + 1, 1) = Format(DateAdd("n", (i - 1) * 15, "08:00:00"), "HHMM")

            .cells(i + 1, 2) = lRT_actual - lRT_forecast

            If Me.acxProgress_bar.Value + 2 < 100 Then
                Me.acxProgress_bar.Value = Me.acxProgress_bar.Value + 2
            Else
                Me.acxProgress_bar.Value = 90
            End If
        Next i

Thanks for your help

I will do a quick refactor of the code to do that as a test. It would howver mean making a temp table which does seem a bit over the top but if it is quicker overall then so be it!

Kevin Ross 2009-11-26 15:50:21

The temp table can be emptied and refilled, making it a, um, permanent temp table.

Remou 2009-11-26 15:52:07

Why not use a stored QueryDef that pulls the values from the controls?

David-W-Fenton 2009-11-26 23:25:20

I redid the code to save it to a temp table then bind the graph to that. It went from taking 1200ms to taking 80ms (with some other tweaks). Who would have thought that saving something to disk then recalling it would be quicker than doing it all in memory!

Kevin Ross 2009-11-27 14:16:09

ansaurus

tags:

views:

answers:

Graph on an access form

related questions