VBA For Queries Running

[Littlemalky]

Hi, I have multiple sets of append queries that I'm executing via VB triggered by an OnClick Event button. Because there are so many, I'm wondering if there is a way for a text box to pop up while these are running to highlight which set of queries are running throughout the process to inform the user where the process is at.

Currently my code is as such:

With CurrentDb
            'REPS
            .Execute "QRY_REP_1", dbFailOnError
            .Execute "QRY_REP_2", dbFailOnError
            .Execute "QRY_REP_3", dbFailOnError
            .Execute "QRY_REP_4", dbFailOnError
            .Execute "QRY_REP_5", dbFailOnError
            
            'CFS
            .Execute "QRY_CFS_1", dbFailOnError
            .Execute "QRY_CFS_2", dbFailOnError
            .Execute "QRY_CFS_3", dbFailOnError
            .Execute "QRY_CFS_4", dbFailOnError
End With
Msgbox "Append Complete!"

So if I wanted it to say "Rep queries now running..." and "CFS queries now running...". How would I implement that?

 

[CSmith]

Dim varReturn As Variant

With CurrentDb
            'REPS
            varReturn = SysCmd(acSysCmdSetStatus, "Rep queries now running...") ' This will display in the status bar at bottom of window
            .Execute "QRY_REP_1", dbFailOnError
            .Execute "QRY_REP_2", dbFailOnError
            .Execute "QRY_REP_3", dbFailOnError
            .Execute "QRY_REP_4", dbFailOnError
            .Execute "QRY_REP_5", dbFailOnError
           
            'CFS
            varReturn = SysCmd(acSysCmdSetStatus, "CFS queries now running...") ' This will display in the status bar at bottom of window
            .Execute "QRY_CFS_1", dbFailOnError
            .Execute "QRY_CFS_2", dbFailOnError
            .Execute "QRY_CFS_3", dbFailOnError
            .Execute "QRY_CFS_4", dbFailOnError
End With
Msgbox "Append Complete!"

 

[welshgasman]

You will probably also want to clear the status bar when they have all finished, so either use the above with a string of a space or
VarReturn = SysCmd(acSysCmdClearStatus)

HTH

 

[Littlemalky]

Thank you!

 

[Micron]

You can also just open a small form that looks like a message box and alter a label caption with what ever message you want. Each time you alter the message, you Repaint the form. A lot more obvious to users than the status bar message area I think, but to each their own.

 

[Littlemalky]

Hmm so the struggle with the current technique is that I'm running 20+ append queries. Because there are so many, the screen just freezes while they are executing and I can't see any of the messages. Is there a Application.ScreenUpdating equivalent in Access or another way to avoid the freezing?

 

[Micron]

so the struggle with the current technique

which technique is that? If what I suggested then I'd say the form label doesn't have time to repaint (assuming that is what you did). I would add a short pause between query calls. One second would probably be enough. You can use an API or a simpler (IMO) pause function. Something like
Pause(1)
call query here
Pause(1)
etc.

where Pause is a Public function in a standard module

Public Function Pause(interval As Single)
Dim Start As Double
Start = Timer
Do While Timer < Start + interval
'Debug.Print Timer
Loop
End Function

Probably the single and double should be one or the other. I just have As Single because the Timer function returns a Single.

 

[xenou]

What is your code now? How long do your queries take? I haven't had problems with freezing with either status bar updates or editing a form to show progress.