How to show progress on status bar when running a

Question

I have a macro in MS Access 2010 in Windows 7 which runs a sequence of quite slow Make Table and Update queries. I want it to show on the status bar which query it is running, as the usual message "Run query" does not give the query name.

I have written the following VBA:

Function RunQueryAndReportStatusWithMsgBox(QueryName As String)
Dim RetVal As Variant
On Error GoTo ErrHandler

PutStatusBarBack
MsgBox "About to run query"
Application.Echo False, "Executing " & QueryName & " ..."
DoCmd.OpenQuery QueryName, acViewNormal, acEdit
On Error GoTo 0
Exit Function

ErrHandler:
Select Case Err
   Case 2501:    ' OpenQuery cancelled by the user pressing escape
      MsgBox "The OpenQuery action for query " & QueryName & " was cancelled by the user."
   Case Else:    ' Another error has occurred.
      ' Display the error number and the error text.
      MsgBox "Error # " & Err & " : " & Error(Err)
   End Select

' Put status bar back to normal.
PutStatusBarBack

End Function

Function PutStatusBarBack()

Dim RetVal As Variant

On Error GoTo ErrHandler

' Put status bar back to normal.
RetVal = SysCmd(5) ' not sure if I need this.
Application.Echo True, ""

On Error GoTo 0
Exit Function

ErrHandler:

' Display the error number and the error text.
MsgBox "Error # " & Err & " : " & Error(Err)

' Put status bar back to normal.
RetVal = SysCmd(5) ' not sure if I need this.
Application.Echo True, ""

End Function

I have written a macro to call RunQueryAndReportStatusWithMsgBox with each query in turn as an argument, and then I call PutStatusBarBack at the end of the macro. I turn warnings off at the start and on at the end. This works really well - just as I want it to.

However, I don't want to be pressing OK on a message box every time a query starts. If I comment out the MsgBox statement, it doesn't work any more. The results are variable. Sometimes it shows something in the status bar, sometimes not. When I ran it just now, I just got the "Ready" message throughout but sometimes I have got the desired message appearing for some but not all of the queries.

I have tried using RefreshDatabaseWindow instead of MsgBox, but that doesn't make any difference.

Answer 1

I'm not sure that this is what you are looking for? Maybe:

Dim statusText As String
Dim statusPercent As Integer

statusText = "Yada yada..."
statusPercent = 100 / 500 * 100

Application.StatusBar = "Progress: " & statusText & "(" & Cstr(statusPercent) & " %)" 'Progress: Yada yada... (20 %)

I.e. change the assignment in Application.StatusBar everytime you want it to change.

Answer 2

Just now stumbled upon this so it's most likely too little, too late, but be sure that during every iteration, after you change the status bar you call DoEvents. This tells your procedure to return control to the application and Windows for a second, which is how it changes that status bar text. It's also how you keep Access from looking to Windows like it's not responding.

Answer 3

Corresponding to @Zajonc's comment to Hauns TM answer.

It happens, because ot this line:

RetVal = SysCmd(5)

This means: refresh statusbar.

More about status bar in MS Access: ACC: How to Change the Status Bar Text Using SysCmd()

So, till first procedure works, do not refresh status bar ;)

For i = 1 to 10
    SysCmd(4, "Running query " i & " of " & 10)
    'your code here...
    RunQueryAndReportStatusWithMsgBox(...)
Next
'here you should refresh status bar ;)

Cheers,
Maciej

Answer 4

Thanks to the help I received from HansUp in answering a similar question (How to show progress on status bar when running code (not queries)) which I posted afterwards, I can now answer this question myself.

To make the code work without the call to MsgBox, you need to put two lines before the call to Application.Echo:

RetVal = SysCmd(4, "Executing " & QueryName & " ...")
DoEvents

This now does exactly what I want.