Microsoft Access - Avoid For...Each Loops When Removing Items From A Collection (Access 2000/2002/2003)

The easiest way to iterate a collection is using a For...Each loop. However, if you plan to modify the collection as you loop through its records, you should use a conditional loop instead. As you might suspect, adding or removing in a collection while you're looping through it shuffles the collection's indexes. This, in turn, prevents the For...Each loop from functioning properly. For example, the following code doesn't allow Access to close all open forms:

  • Dim obj As Access.Form
  • For Each obj In Access.Forms
  • DoCmd.Close acForm, obj.Name
  • Next

In contrast, the following Do Loop closes all open forms:

  • Do Until Access.Forms.Count = 0
  • DoCmd.Close acForm, Access.Forms(0).Name
  • Loop

You'll notice that in the Do Loop, you only have to reference index 0 to close each object. That's because VBA shuffles the indexes up with each form it removes.

Go back