Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

In Memoriam

Terms of Use


VB Petition

Forms: Close all open forms and "For Each" syntax

Author(s)
Terry Kreft

(Q) I'm trying to close all open forms by looping through the Forms collection using the "For Each frm" syntax. But this does not seem to work correctly. What am I doing wrong and how can I close all open forms?

(A) The For... Each... Next goes through the Forms collection in the same sequence in which the forms were opened (i.e. the index number sequence) so this code

   dim frm as form   
   for each frm in forms      
     debug.print frm.name   
   next

will produce the same result as

   dim intX as integer
   for intX = 0 to Forms.Count-1      
     debug.print Forms(intX).name   
   next

The problem is that as you remove items from the collection the items above that removed item will slip down one place in the collection, so when the for each next removes the item indexed as 0, the items indexed as 1 and 2 slip down and become index items 0 and 1, the next time you go round the loop it will remove the item indexed at 1, at this point there are no further numbers to go through so it exits leaving an item behind.

If we break the second version of the loops above, down into individual procedural steps and change the debug.print line into a docmd.close, we can see what is happening, we'll assume there are three forms (index numbers are 0 to 2), the code actually does this

   dim intX as integer
  'First pass through the loop 
  intX=0
  docmd.close acform, forms(intx).name
  
  'The first form in the collection is removed.
  'at this point the second form drops down in the 
  'index of forms to become 0
  'and the third form drops down to become 1
  intX=1 

  '(this is the forms.count - 1 figure i.e. the '
  'termination of the loop) 
  docmd.close acform, forms(intx).name

  'The second form in the collection is removed, 
  'this was our original third
  'form and the 'loop' now stops leaving our original 
  'second form open

So the answer is to either continue to remove the first item in the collection until there are no items left or start at the top end of the collection and shave them off at the top until you've gone through all of them. So either

   do while forms.count>0      
    docmd.close acform,forms(0).name   
   loop

or

   dim intx as integer   
   dim intCount as integer   
   intCount = Forms.count-1
   for intX= intCount to 0 step -1     
    docmd.close acform,forms(intX).name
   next

There is a difference between the two, with the second method you can conditionally leave forms open, you can't with the first version, so you can do something like this in the loop

   for intX= intCount to 0 step -1
        if forms(intX).Name <> "MyFormToKeepOpen" then
            docmd.close acform,forms(intX).name        
        end if    
   next

© 1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer