Search code examples
ms-accessvbams-access-2010statusbar

How to show progress on status bar when running code (not queries)

I have already posted a question about updating the status bar while running queries in MS Access 2010. Please see How to show progress on status bar when running a sequence of queries in MS Access if you are interested.

This is a very simple question about some code that doesn't work. I hope that if someone can answer it, it may help my understanding of why the code for the more complicated question doesn't work.

Function PutMessageInStatusBar1()

Dim RetVal As Variant
Dim i As Long

RetVal = SysCmd(4, "Before loop 1")
For i = 1 To 500000000
Next

RetVal = SysCmd(4, "Before loop 2")
For i = 1 To 500000000
Next

RetVal = SysCmd(4, "Before loop 3")
For i = 1 To 500000000
Next
RetVal = SysCmd(5)

End Function

I wrote a macro to run the code. It starts by turning warnings off, then calls the above function, displays a message box to say "Finished" and then turns warnings on.

When I run it, the status bar first shows "Ready". There is a pause, presumably while the code is running Loop 1. Then it shows "Before loop 2" and finally "Before loop 3".

Why doesn't it display "Before loop 1"?

I tried putting RetVal = syscmd(5) right at the beginning of the function to see if it made any difference. It didn't.

Solution

  • Call DoEvents after each SysCmd call. That will signal Windows to update the display before your code advances.

    In this function, each status bar message is visible before the next is displayed.

    Function PutMessageInStatusBar2()
    Const lngMilliseconds As Long = 1000

    SysCmd acSysCmdSetStatus, "Before loop 1"
    DoEvents
    Sleep lngMilliseconds
    SysCmd acSysCmdSetStatus, "Before loop 2"
    DoEvents
    Sleep lngMilliseconds
    SysCmd acSysCmdSetStatus, "Before loop 3"
    DoEvents
    Sleep lngMilliseconds
    SysCmd acSysCmdSetStatus, "Done."
    DoEvents
    Sleep lngMilliseconds

    SysCmd acSysCmdClearStatus

End Function

    Sleep is based on a Windows API method and is declared in the Declarations section of a standard module:

    Option Compare Database
Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)