Access Cookbook - Ken Getz [146]
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:
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.
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.
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.
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.
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