How to Use the DoEvents Function: A Demonstration

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

Where to Use DoEvents

If your application dims out and displays "(Not Responding)" in the title bar, that is a telltale sign that you may need a call to DoEvents.  

If you are seeing that behavior due to a single query that takes a long time to execute, then adding a call to DoEvents will probably not help the situation.

On the other hand, if the "(Not Responding)" error appears when your application is inside of a loop, adding a call to the DoEvents function from inside the loop may alleviate the problem.

The DoEvents function: a metaphor for six-year olds

I broke this concept down to its most basic level when I wrote How to Teach a Six-Year Old About the DoEvents Function.   I used the concept of a school as a metaphor for an Access application.

Here's a quick recap of the article:

Little Billy had to carry 100 cans of beans from his classroom to the library one at a time.  Shannon was tasked with updating the class bulletin board with his progress, but couldn't go in the hallway while Billy had the hall pass.

Since there was no progress being posted to the bulletin board, the office called down to make sure everything was OK.  The phone rang in the hallway, but José couldn't answer it because Billy had the hall pass.  Exasperated, the principal tried to cancel the whole process by sending a note to the teacher.  Unfortunately, Kedisha couldn't go to the office to get the note because...you guessed it...Billy had the hall pass.

Once Billy delivered the last can, Shannon updated the bulletin board, then José answered the phone, and finally, Kedisha went to the office to get the note.

OK, now explain it to me like I'm fifteen

The hall pass represents the processor.  In a single-threaded application–like Access–there's only one hall pass.  Access can only execute a single instruction at a time.

Billy represents a typical loop.  Taking the cans to the library is analogous to writing data to a database RBAR-style (row by agonizing row).

A long-running  process benefits from an occasional status update.  That's Shannon.  She's the status update girl.

The operating system (i.e., Windows) also checks in from time to time.  If you stop taking the OS's calls for more than about five consecutive seconds, the application dims and the title bar gets the " (Not Responding)" treatment.  Poor José desperately wanted to answer that phone, but he couldn't because Billy was hogging all the processor time the hall pass.

Finally, Access's event-driven nature often makes it appear to be doing multiple things at the same time.  In reality, though, each of those events has to wait its turn in line.  Kedisha eventually handled the office's "KeyPress" event, but she had to wait her turn in line like everyone else.

Enough with the metaphors, how about a demo?

Let's demonstrate this phenomenon with a sample form in Access.  

Here's the form's code-behind.  The control names should be self-explanatory:

Option Compare Database
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Private mUserCanceled As Boolean

Private Sub RunLoop(IncludeDoEvents As Boolean)
    mUserCanceled = False
    DoCmd.Hourglass True
    Me.tbCount.Value = 0
    
    Dim i As Long
    For i = tbStart.Value To tbEnd.Value
        If mUserCanceled Then Exit For
        Sleep tbDelay.Value
        Me.tbCount.Value = i
        If IncludeDoEvents Then DoEvents
    Next i
    DoCmd.Hourglass False
End Sub

Private Sub btnNoDoEvents_Click()
    RunLoop False
End Sub

Private Sub btnWithDoEvents_Click()
    RunLoop True
End Sub

Private Sub btnCancel_Click()
    mUserCanceled = True
End Sub

The important parts happen inside the RunLoop procedure.  

First, I turn the Hourglass on to make it easier to see when the code is executing in the animated gif below.

Within the loop itself, I first check to see if the user clicked on the Cancel button.  If they did, then I exit the For Loop.

I'm using the Sleep API to add an artificial delay.  In a real world example, this delay typically comes from a database operation (UPDATE, SELECT, etc.).

Next, I'm updating our progress by changing the value of a textbox control.

As the last step in the loop, I'm calling the DoEvents function.  Take note that this function only gets called if I click on the [With DoEvents] button.  If I click on the [No DoEvents] button, then DoEvents never gets called.

Finally, I turn the hourglass off before exiting the RunLoop routine.

Demonstration

Here's what the form looks like in action.

Notice when I click the [No DoEvents] button, nothing happens for five seconds and then the final count of 10 iterations pops into the Current Count box.  Also, the Cancel button has no effect, even though I clearly click it before the process has a chance to finish.

When I click the [With DoEvents] button, the Current Count immediately starts updating.  After the count reaches 6, I click on the Cancel button.  This stops the process cold and the Current Count remains at 6.

Resources

Sample database with the TestDoEvents form:

• https://assets.nolongerset.com/TestDoEvents.zip

Article references

UPDATE [2021-07-19]: Updated the TestDoEvents.zip file to include default values on the Test DoEvents form.  (h/t Jack Stockton)