Code works...but not in the right order!

[Big Pat]

Hi,

I have code that mainly consists of Do.Cmd.RunSQL "INSERT INTO..."

There are 28 almost identical lines, differing only in table names, but split into three groups for the last three financial years.

On the form that contains the command button, I have several hidden labels, so I inserted some lines to make these messages visible at the appropriate stage, (see red lines below) so the user knows that the various statements are running.

'Turn warnings off
DoCmd.SetWarnings False

'Delete the existing data
DoCmd.RunSQL "DELETE [tbl All CLRN Summary].* FROM [tbl All CLRN Summary];"

'Message 1
[COLOR="Red"]Me.lbl2008_09.Visible = True[/COLOR]               [COLOR="DarkGreen"]'Displays a label that says "Appending 2008-09 data"[/COLOR]

'Append the 08-09 data
DoCmd.RunSQL "INSERT INTO ….
DoCmd.RunSQL "INSERT INTO ….
etc.
etc.

'Message 2
[COLOR="red"]Me.lbl2009_10.Visible = True[/COLOR]

'Append the 09-10 data
DoCmd.RunSQL "INSERT INTO ….
DoCmd.RunSQL "INSERT INTO ….
etc.
etc.


'Message 3
[COLOR="red"]Me.lbl2010_11.Visible = True[/COLOR]

'Append the 10-11 data
DoCmd.RunSQL "INSERT INTO ….
DoCmd.RunSQL "INSERT INTO ….
etc.
etc.


'Message 4
[COLOR="Red"]Me.lblCompleted.Visible = True[/COLOR]

But when it runs, none of the labels are made visible until all the DoCmd lines have run, they the all become visible together. That's what it *looks* like, anyway. I don't suppose this is what is *actually* happening and there must be something stopping the screen from being updated.

Can I force it somehow?

Thanks,

 

[CBrighton]

Try adding me.repaint after each label is made visible.

 

[Big Pat]

Thanks so much - that works perfectly!

I'm curious as to why it's needed though. Why doesn't the ".visible=True" line work by itself? I've dome something similar in another database and it works OK there without the me.repaint line.

Pat

 

[CBrighton]

The same reason you cannot continue clicking things on the form, using other forms, etc.

The VBA code being run freezes access. Repaint basically tells it to apply any pending changes to the form.

 

[Big Pat]

You live and learn...which is what I love about this forum.

Thanks again!