Access Cookbook

Multitask Your Access Basic Code

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?

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 Basic 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 frmMoveTest (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, in increments of 1, from 500 to 2,000 twips (in Figure 7-5, you can see only a portion of the label), ...