When (or Should I Say, How Often) to Call DoEvents

This is part 3 of a multi-part series on the DoEvents function.

A brief recap of DoEvents

Calling DoEvents is sometimes a necessary evil.  

Microsoft Access is single-threaded.  The code and the user interface both run on the same thread.  If you want to provide form updates during a long-running process (like for a custom progress meter form), you need to explicitly give the UI a chance to update itself.

That's why you usually call DoEvents from inside of a loop.  The DoEvents call tells the code to wait its turn while the user interface is updated.  Here's what that looks like in practice:

Too Much of a Good Thing

Too many calls to the DoEvents function will slow down your code.  

A better approach is to call it only enough to stave off the "(Not Responding)" treatment from Windows.  

The exact frequency depends on how long it takes your code to execute each time through the loop.  The technique, though, is the same: use the Mod operator to call the DoEvents function every x times through a loop.

Dim i As Long
For i = 1 To 5000
    'Some slow code
    
    'Call DoEvents on every hundredth trip through the loop
    If i Mod 100 = 0 Then DoEvents
Next i

The goal is to leave no more than a 3-4 second gap between calls to DoEvents.  Anything longer than five seconds and Windows will flag a process as "Not Responding."  

Bringing an axe to a knife fight

The problem with the DoEvents function is that it's a blunt instrument.  The user interface is not the only thing that takes a turn when you call DoEvents.  Every message in the Windows Message queue gets processed, too.

The following excerpt from Chapter 13 of Advanced Visual Basic 6 by Matthew Curland is instructive:

"Visual Basic provides the DoEvents function for arbitrarily clearing pending messages in all the current thread's windows. Since pending methods masquerade as messages, calling DoEvents lets them through. The problem is that DoEvents lets everything else through as well.  You get a flood instead of a trickle."

I'll talk about the Windows Message queue and the nitty-gritty details of the DoEvents function in later articles in this series.  For now, the important point I want to make is that you usually don't want to call the DoEvents function on every single trip through a loop.  

Sometimes all you need is a knife

In certain situations, the Sleep API function can be used in place of DoEvents.

Sleep function
Suspends the execution of the current thread until the time-out interval elapses.

A value of zero causes the thread to relinquish the remainder of its time slice to any other thread  that is ready to run.

Do 
    'Some slow code
    
    'Give non-Access threads a chance to execute
    Sleep 0
Loop

Unfortunately, there's not really another simple way to cede control of the processor so that Access can handle other events and update the user interface.  So, if you want to update the Access UI during a long-running process, you're pretty much stuck with DoEvents.

External references

Article references

Photo by Hans-Jurgen Mager on Unsplash