Avoid Using DoEvents to Wait in Microsoft Access, VBA, and VB6

Code for waiting a specified amount of time, or
waiting until a specific time to continue code,
without using DoEvents

Provided by: Luke Chung, FMS President

In our Microsoft Access, VBA, and
VB6 programs, we often need to
pause processing for a certain period of time, or
until a specific time, and then continue processing.

One common way to do this is to use
the DoEvents function in a loop while waiting, like
this:

Do
DoEvents
Loop Until (Now > datTarget)

DoEvents releases time to other processes on
the computer. However, this method consumes
almost all the
CPU time and slows down the PC considerably because
there's no pause in the processing. As soon as
the DoEvents command executes, it executes again
and repeats as fast as the PC can
run. It literally consumes all the available CPU
cycles, only giving time to other processes during the tiny
period DoEvents is executing. This is
a significant problem when pausing for more than a
few seconds to wait for the user to
perform other tasks. Users will experience the
entire PC slowing down.

You can see the impact of this by looking at the
Performance tab of the Windows Task Manager:

CPU Spike During DoEvents in a DO..LOOP

The proper approach is to use the Windows Sleep
command which lets you wait but releases CPU cycles
to the computer. To use the Windows Sleep command,
you must first add the following Declare statement
to the Declarations section of your module:

Now you can can call the Sleep
command to pause, while keeping the system
resources free. For instance, this code uses the
Sleep command to wait the specified number of
seconds before continuing processing: