Collections of Objects

Collections of Objects

Most objects in Access belong to a collection. For example, the currently open forms belong to the Forms Collection, Controls on a form belong to the Controls collection for the form. Knowing this makes it easier to perform actions on all items in a collection. For example, you can loop through all the tabledefs in the database, and make changes to each tabledef, or else loop through the open forms, and close them all.

However, there is one thing to be aware of. If you are closing/deleting objects, you will need to loop backwards through the collection. Below are examples of looping forwards and backwards through a collection:

Sub sLoopForwards()
    Dim db As Database
    Dim tdf As TableDef
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        Debug.Print tdf.Name
    Next tdf
    Set db = Nothing
End Sub

Sub sLoopBackwards()
    Dim intCount As Integer, intPos As Integer
    Dim db As Database
    Set db = DBEngine(0)(0)
    intCount = db.TableDefs.Count - 1
    For intPos = intCount To 1 Step -1
        Debug.Print db.TableDefs(intPos).Name
    Next intPos
    Set db = Nothing
End Sub

This principle can be expanded, so that you can loop through all fields in all tables, for example to set the AllowZeroLength, by nesting the loops:

Sub sChangeAllowZeroLength()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Set db = DBEngine(0)(0)
    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4) <> "MSys" Then
            For Each fld In tdf.Fields
                If fld.Type = dbText Then
                    If fld.AllowZeroLength = False Then fld.AllowZeroLength = True
                End If
            Next fld
            tdf.Fields.Refresh
        End If
    Next tdf
    db.TableDefs.Refresh
    Set db = Nothing
End Sub

Another requirement might be to loop through all forms in a database, and make changes to all of them. Some code would be like:

Sub sChangeFormProperties()
    Dim db As Database
    Dim ctr As Container
    Dim doc As Document
    Dim frm As Form
    Set db = DBEngine(0)(0)
    Set ctr = db.Containers!Forms
    For Each doc In ctr.Documents
        DoCmd.OpenForm doc.Name, acDesign, , , , acHidden
        Set frm = Forms(0)
        frm.ShortcutMenu = False
        DoCmd.Close acForm, doc.Name, acSaveYes
    Next doc
End Sub

Top

Last modified at 06/06/2006 14:55:19