Repaint doesn't work until end of code

[Big Pat]

Hi,

I have a process which runs lots of slow append queries, so I want to reassure the user of progress, by changing the font colour of labels to green
when the relevant queries have finished. This is the code I'm using

'Update the older data if that option is selected
If Me.Menu_YearOption.Value = 1 Then
    DoCmd.OpenQuery "2-10 Append FY1112"
    Me.lbl1112.ForeColor = 32768
    Me.Repaint

    DoCmd.OpenQuery "2-12 Append FY1213"
    Me.lbl1213.ForeColor = 32768
    Me.Repaint

    DoCmd.OpenQuery "2-14 Append FY1314"
    Me.lbl1314.ForeColor = 32768
    Me.Repaint
Else
    'Do nothing
End If

'Regardless of the option, update last year and this year
DoCmd.OpenQuery "2-16 Append FY1415"
Me.lbl1415.ForeColor = 32768
Me.Repaint

DoCmd.OpenQuery "2-18 Append FY1516"
Me.lbl1516.ForeColor = 32768
Me.Repaint

Each query takes about a minute to run, as it gets data from a sharepoint server, but the labels don't turn green one at a time as expected. Instead, I get the spinning wheel until all the queries have run, then all the labels turn green together.

Is there something else I should be doing rather than Me.Repaint ?

Thanks

 

[Ranman256]

i never used repaint, just use on print.

 

[Big Pat]

Thanks. I'm afraid I need more explanation though, as I don't do this kind of stuff very often.

I know there is an On Print event but I don't know how that could work in this case. I probably didn't explain my problem clearly.

I have a form with one button and series of labels lbl1112, lbl1213 etc. When the form first opens, these are all in a grey font.

I click the button which runs the code.
The first append query runs,
The first label should change from grey to green - but it doesn't.
The second append query runs,
The second label should change from grey to green - but it doesn't.
etc.

When the final query has finished, THEN all the labels turn green. It's as if the code is being executed out of sequence.

I don't understand how to use On Print to accomplish this.

 

[Ranman256]

these events happen on EVERY record of the report. There is no 1st and 2nd query.

so youd have report code like:
lbl1.visible= [field] = "green"
if [field] = true then lbl32.backcolor = vbBlue

 

[spikepl]

Insert

msgbox "some text"

after a couple of the DoCmd just for fun and see if that changes anything

 

[Big Pat]

these events happen on EVERY record of the report. There is no 1st and 2nd query.

so youd have report code like:
lbl1.visible= [field] = "green"
if [field] = true then lbl32.backcolor = vbBlue

Hi Ranman256

There is no report, there are no records and I'm not trying to print anything. It's just a form with a button that runs several append queries. The queries run fine, I'm just trying to get labels on the form to change colour to show progress.

 

[Big Pat]

Insert

msgbox "some text"

after a couple of the DoCmd just for fun and see if that changes anything

Hi Spikepl,

I inserted a message box as suggested. Up to that point, the code ran as before i.e. several queries ran and THEN the labels up to that point turned green together. But after I OK'd the message box, the rest of the labels turned green one by one as the queries ran.

(In fact there are quite a few more of them than I showed in the code, but i chopped it down for brevity. It goes back to FY2008-09 but ni exactly the same format.)

Of course I don't want the user to have to press OK after every year's data is appended and I know this msgbox is only a test, but what's your thinking? Something connected to focus maybe?

Thanks for your help.

 

[spikepl]

I'm really not sure what I was thinking - just wanted to see how the system reacts. However . you did mention focus....

What happens when you have one msgbox, and you move it earlier and earlier in the sub? What happens when it is prior to all queries?

 

[Big Pat]

Moving it up and down through the code means whatever section is before the msgbox changes colour all together, after the DoCmd's up to that point have run. After that point, they change colour one at at time, as desired. This holds no matter where I put th msgbox. Except...

When I put it right at the start, the code reverts to the earlier behaviour. All the queries run, then all the labels update.

 

[spikepl]

If your queries do not have parameters, try

Currentdb.Execute "2-10 Append FY1112"

etc..

 

[spikepl]

Gotta run now, but anyone else is welcome to chime in. back tomorrow

 

[Big Pat]

Aw heck I found it!! I also need to fess up and admit I didn't show you all my code as I was convinced parts of it were irrelevant.

After the queries that I showed, I have 3 others that run instantly so do not require screen-updating. It turns out one of those had an error in it: "Too few parameters". But I also had a line of code at the beginning that said On Error GoTo ExitHere and at the end
ExitHere:
Msgbox " "
End Sub

So I wasn't even picking up the error! I have now commented out the offending query until I work out what my error is and all the label updating works just fine.

Lesson learned: "Show all your workings"

Groan...I should really be kept away from Access

Thanks so much for your help