Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 7.4 Multitask Your VBA Code

7.4.1 Problem

If your VBA
code includes a loop that runs for more than just a second or two,
Access seems to come to a halt. You can't move the
windows on the screen, and mouse-clicks inside Access are disregarded
until your code has finished running. Why is this happening? Is there
something you can do to relinquish some control?

7.4.2 Solution

You may have noticed that it's possible to tie up
Access with a simple bit of VBA code. Though 32-bit Windows is
multithreaded, this helps only if the applications running under it
are also multithreaded. It appears that the executing VBA code ties
up Access's processing, so the multithreaded nature
of Windows doesn't help. If your code contains loops
that run for a while, you should make a conscious effort to give
Windows time to catch up and do its own work. VBA includes the
DoEvents statement, which effectively yields time
to Windows so that Access can perform whatever other tasks it must.
Effective use of DoEvents can make the difference
between an Access application that hogs Access's
ability to multitask and one that allows Access to run smoothly while
your VBA code is executing.

To see the problem in action, load and run the form frmDoEvents (in

07-04.MDB ). Figure 7-5 shows
the form in use. The form includes three command buttons, each of
which causes the label with the caption "Watch Me
Grow!" to change its width from 500 to 3500 twips
(in Figure 7-5, you can see only a portion of the
label), in a loop like this:

    Me.lblGrow1.Width = 500
For intI = 0 To 3000
Me.lblGrow1.Width = Me.lblGrow1.Width + 1
' Without this call to Repaint, you'll
' never see any changes on the screen.
Me.Repaint
Next intI

Figure 7-5. The sample DoEvents Test form, frmDoEvents, in action

To test the effects of DoEvents, try these steps:

1. Press the "Run Code Without
   DoEvents" button. The code attached to this button
   will change the width of the label inside a loop without yielding
   time to Access. While the code is running, try to click on another
   button on the form or to move or size the active window. You will
   find that any of these tasks is impossible while the label is
   expanding. Once the label has finished growing, Access will display
   any actions you attempted to make during the process.

2. Try the same loop with DoEvents inserted. Click
   the second button, labeled "Run Code With DoEvents
   1". This time, as the code executes, you will be
   able to move or size the active window. In addition, you can click on
   any of the form's buttons while the code is running.
   The next step tests this capability.

3. While the label is growing, click on
   the "Run Code With DoEvents 1"
   button many times in quick succession. Every time you click the
   button, Access starts up another instance of the Click event
   procedure, and each instance continues to make the label grow. This
   is called recursion, in which multiple calls are made into the same
   routine, each starting before the last instance has completed. Each
   time you call the Click event, you use a bit of
   Access's stack space (a memory area set aside for
   each procedure's entry information and local
   variables). It's possible that, with many
   invocations, you will use up that memory. Using versions of Access
   later than Access 95, we've never made this happen.
   Using Access 2, it was easy to do. The next step offers a solution to
   this recursion problem.

4. Click the third button, labeled "Run Code with
   DoEvents 2". While the label is expanding, try
   clicking on the button again. You'll see that this
   time your clicks won't have any effect. The code
   attached to this button checks to see if it's
   already running and, if so, exits the code. This method solves the
   problem of recursive calls to DoEvents.

7.4.3 Discussion

The code attached to the first button does its work without any
concern for Windows or other running applications. When you press it,
it executes this code:

Private Sub cmdNoDoevents_Click( )
Dim intI As Integer
Me.lblGrow1.Width = 500
For intI = 0 To 3000
Me.lblGrow1.Width = Me.lblGrow1.Width + 1
' Without this call to Repaint, you'll
' never see any changes on the screen.
Me.Repaint
Next intI
End Sub

Because the code never gives Windows
time to "catch up," you must
include the call to Me.Repaint to make sure the form repaints itself
after each change. To see how this works, comment out that line and
press the first button again. You'll see that the
screen won't repaint until the entire operation is
done.

The code attached to the second button
does the same work, but it calls DoEvents within
the loop. With that statement added, you no longer need the call to
Me.Repaint, because DoEvents allows Windows to
take care of the pending repaints. It also allows you to use the
mouse and other applications while this loop is running. The code
attached to the second button looks like this:

Private Sub TestDoEvents( )
Dim intI As Integer
Me.lblGrow1.Width = 500
For intI = 0 To 3000
Me.lblGrow1.Width = Me.lblGrow1.Width + 1
DoEvents
Next intI
End Sub
Private Sub cmdDoEvents1_Click( )
TestDoEvents
End Sub

The problem with this code, as
mentioned in Step 2, is that nothing keeps you from initiating it
again while it's running; if you press the same
button while the code is in the middle of the loop, Access will start
up the same procedure again. Every time Access starts running a VBA
routine, it stores information about the routine and its local
variables in a reserved area of memory, called its
"stack". The size of this area is
fixed and limits the number of procedures that can run concurrently.
If you press that button over and over again in quick succession,
it's possible that you'll overrun
Access's stack space.

It's doubtful that you'll ever be
able to reproduce this problem with this tiny example. Though the
stack space was limited to 40 KB in Access 2, it was increased to a
much larger size in Access 95 and later versions.
You'd have to press that button very fast for a very
long time to fill up that much stack space. However, in more complex
situations, if you were passing a large amount of data to a procedure
in its parameter list, this could still be a problem.

The third button on the form demonstrates the solution to this
problem. It ensures that its code isn't already
running before it starts the loop. If it's already
in progress, the code just exits. The code attached to the third
button looks like this:

Private Sub cmdDoEvents2_Click( )
Static blnInHere As Boolean
If blnInHere Then Exit Sub
blnInHere = True
TestDoEvents
blnInHere = False
End Sub

It uses a static variable,
blnInHere, to keep track of whether the
routine is already running. If blnInHere
is currently True, it exits. If not, it sets the
variable to True and then calls cmdDoEvents1_Click
(the previous code fragment). Once cmdDoEvents1_Click returns,
cmdDoEvents2_Click sets blnInHere back to
False, clearing the way for another invocation.

DoEvents is
one of the most misunderstood elements of VBA. No matter what
programmers would

like
DoEvents to do, under versions of Access later
than Access 95 it does nothing more than yield time to Access so it
can process all the messages in its message queue. It has no effect
on the Access database engine itself and can't be
used to slow things down or help timing issues (other than those
involving Windows messages). When used in VBA code,
DoEvents releases control to the operating
environment, which doesn't return control until it
has processed the events in its queue and handled all the keys in the
SendKeys queue. Access will ignore DoEvents in:

• A user-defined procedure that calculates a field in a query, form, or
  report

• A user-defined procedure that creates a list to fill a combo or list
  box

As you can see from the second button on the sample form, recursively
calling DoEvents can lead to trouble. You should
take steps, as in the example of the third button, to make sure that
this won't occur in your applications.