VBA For Queries Running

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
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:
VBA Code:
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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
678
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
VBA Code:
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

Well-known Member
Joined
May 25, 2013
Messages
711
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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,806
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

Board Regular
Joined
Jan 14, 2011
Messages
223
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,806
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,603
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,089,636
Messages
5,409,456
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top