Access Cookbook - Ken Getz [147]
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.
7.5. Programmatically Add Items to a List or Combo Box
Problem
Getting items into a list or combo box from a data source is elementary in Access. Sometimes, though, you need to put things into a list box that you don't have stored in a table. In Visual Basic and other implementations of VBA-hosted environments, and in Access 2002 and later, this is simple: you just use the AddItem method. But Access list boxes in