Closing/Deleting Items in Collections
This always gets me for a minute or two. I am just creating some code in behind a form which will close all the other forms. The idea being that another hidden form is used to determine if the user has not done anything in a half hour. if so they get booted out. However i want to display a message indicating they got kicked out on a form. But I want to close all the other forms so there are no tables open in the backend MDB. Thus others can do backups, compacts or work on the tables or indexes.
Initially I had the following code
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm
But that was closing some forms and leaving others open. Say what? Oh yeah, I reminds myself. When you go through the open forms collection closing forms the number of objects is reduced by one as you close each form. This logic actually closed every second form.
Thus you need to start at the end of the collection and go backwards.
For i = Forms.Count - 1 To 0 Step -1
If Forms.Item(i).Name <> "zMsgBoxThenExit" Then _
DoCmd.Close acForm, Forms.Item(i).Name
Next i
Note that this also applies if you are deleting objects in a MDB, fields on a table and so forth. Anything that is a collection. You have to do the -1 to 0 logic because collections and such in VBA start at 0 not 1.