Working with Objects and Collections

Now that you're familiar with the objects available in Microsoft Access, you can begin working with them in Visual Basic. The following sections provide you with the information you need to begin working with objects.

Referring to Objects

To use an object in Visual Basic, you must specify which object it is that you intend to use. There are two types of objects with which you need to be concerned: objects that exist individually and don't belong to collections, and objects that belong to collections.

Some objects, such as the Microsoft Access Application object, are not members of a collection. Most of the time you can refer to these objects directly in your code. For example, you refer to the Application object in Visual Basic as follows:

Application

Other objects belong to collections, and you need to distinguish which object in the collection you want to work with, and which collection contains the object. For example, the DAO TableDef, QueryDef, Recordset, and Relation objects all have a Fields collection. If you refer to a Field object, you need to specify to which collection it belongs. Also, it's likely that there's more than one Field object in the Fields collection of one of these objects. To refer to a particular Field object, you must provide either its name or its position in the collection.

There are three ways to refer to an object in a collection. The fastest way is to provide the name of the collection followed by the name of the object to which you are referring, as shown in the following examples:

Forms!Employees
QueryDefs![Current Product List]

Use the ! operator to separate the name of the collection from the name of the particular object within it. Also, if the name of the object contains spaces, you must enclose it in brackets. Finally, keep in mind that the Forms collection includes only forms that are currently open. If the Employees form isn't open when you run the code in the preceding example, an error occurs. The same is true for the Reports collection.

In most cases, you'll know the name of the object to which you're referring, and you should use this syntax. Occasionally, however, you may not know the name of the object until the procedure is running. In this case, you can use a string variable to represent the name of the object. In the following examples, strFormName and strQueryDefName are string variables that contain the name of a Form object and a QueryDef object.

Forms(strFormName)
QueryDefs(strQueryDefName)

If the value of strFormName is "Employees" and the value of strQueryDefName is "Current Product Name", then the previous example is equivalent to the following lines of code:

Forms("Employees")
QueryDefs("Current Product Name")

You can also refer to an object in a collection by its index number. Like the elements of an array, each object in a collection has an index number that refers to its position in the collection. The following examples use the index number to refer to a particular object in a collection.

Forms(0)
QueryDefs(1)

Most collections are indexed beginning with zero. That is, the first object in a collection has an index of 0, the second has an index of 1, and so on. The first line of code in the previous example refers to the first open Form object in the Forms collection. In most cases, Form objects are indexed according to the order in which they were opened.

Note   Some collections, such as the Microsoft Office CommandBars collection, are indexed beginning with 1 rather than 0. To determine how a particular collection is indexed, search the Help index for the name of that collection.

The second line refers to the second QueryDef object in the QueryDefs collection. The QueryDefs collection includes all saved queries in the database, regardless of whether they are open. In most cases, QueryDef objects and other objects are indexed according to the order in which they were created in the database.

When you refer to an object in code in any of these ways, Visual Basic returns an object reference. An object reference points to the place in memory where a particular object exists. When you work with an object in Visual Basic, you're actually working with a reference to that object in memory.

Referring to Objects in a Default Collection

Many objects in Microsoft Access contain one or more collections of lower-level objects, and one of these collections is generally designated as the default collection for that object. For example, a Form object contains a Controls collection, which is the collection you're most likely to use with a Form object. Since the Controls collection is the default collection of a Form object, you can refer to the collection without explicitly specifying its name.

The following line of code returns an object reference to a control called LastName on the Employees form using the default collection.

Forms!Employees!LastName

You can also use the full reference to the control, as shown in the following line of code:

Forms!Employees.Controls!LastName

The following table lists some objects that have default collections.

Declaring and Assigning Object Variables

The preceding sections have shown how to return a reference to an object in order to work with that object in Visual Basic. It's possible to use an object reference throughout your code each time you need to refer to a particular object. However, your code runs more quickly if you declare an object variable to represent the object instead. An object variable is a variable that represents an object in Visual Basic.

To create an object variable, you first declare it as you would declare any variable, by using a Dim, ReDim, Static, Private, or Public statement. You can declare an object variable as a specific type of object or as the more generic type Object. You can also assign a variable of type Variant to an object. Whenever possible, declare an object variable as a specific type of object, because this makes your code run faster. The following line of code declares an object variable as type Form.

Dim frm As Form

See Also   For information on declaring variables, see Chapter 4, "Working with Variables, Data Types, and Constants."

Once you've declared an object variable, you assign an object reference to it. An object reference, as discussed in the previous section, refers to an object in memory. Each time you use an object reference, Visual Basic looks up the object in memory. When you assign the object reference to an object variable, it's stored in that variable so that Visual Basic doesn't have to look it up again. If you need to refer to an object more than once, it's a good idea to create an object variable.

To assign an object reference to an object variable, use the Set statement. The following line of code assigns a reference to the Employees Form object to the object variable declared in the preceding example.

Set frm = Forms!Employees

There is a key difference between using the Set statement with an object variable and assigning a value to other types of variables, such as variables of type String or Integer. Ordinary variables store a value. Even if two variables store the same value, they are stored in different locations in memory. Object variables, however, refer to actual physical objects in the database or in memory. An object variable stores a reference to an object, not the actual object itself or a copy of the object. It is this reference to the object that is assigned to the variable when you use the Set statement. You always work with the object reference in your code, never with the object itself.

In the preceding example, Forms!Employees returns a reference to the Employees Form object, and it is this reference that is assigned to the variable frm. You can also say that the variable frm points to the Employees Form object.

One advantage to this system of storing objects is that all variables assigned the same object reference refer to the same object. Therefore, even if an object is changed in some way, all variables that refer to the object reflect the change and represent the same information. You can also point the variable to a different object of the same type by using the Set statement again; you don't necessarily have to create another variable. The variable simply stores an object reference to the new object.

The Nothing Keyword

An object variable doesn't require much memory or system resources until you assign it to an object. Once it's pointing to an object, it uses much more. With the Nothing keyword, you can free the memory that's being consumed by an object variable. You use the Nothing keyword with the Set statement to disassociate an object variable from the object to which it's been pointing once you are no longer using it. For example, if you are no longer using an object variable that points to a Form object, you can free that variable as follows:

Set frm = Nothing            ' Where frm is a Form object variable.

When you set an object variable to the Nothing keyword, you are no longer storing a reference to a particular object. The variable still exists, and you can assign another object to it when you need it.

Using Objects and Collections in Code

Once you understand how to refer to objects in Visual Basic and how to create object variables to represent them, you can begin using objects in code. The following sections present concepts that may be useful to you as you begin working with objects and collections.

Navigating the Object Hierarchy

As explained earlier in this chapter, in order to work with an object that belongs to a collection, you must refer to that object in its collection. Since objects are related to one another in an object hierarchy, you must also make clear where the object and collection exist in the overall hierarchy. In other words, if the object is a member of a collection, you must qualify the object with the name of its collection. If that collection belongs to another object, you must qualify the collection with the name of that object, and so on.

When you create an object variable and assign an object to it, the information about that object's position within the object hierarchy is stored with the variable. An object variable becomes a sort of shorthand for all the objects preceding the one you want to work with in the object hierarchy.

The following example shows how you can work within the Microsoft Access object hierarchy to access individual objects. The procedure returns a reference to the Employees Form object, which is a member of the Forms collection, and assigns it to an object variable. Then it returns a reference to the LastName Control object, which is a member of the Controls collection of the Form object, and assigns it to an object variable. Finally it uses the ControlType property of the Control object to determine what type of control this is. If the control is a text box, the procedure sets its Locked property to True.

Sub LockControl()
   Dim frm As Form, ctl As Control            ' Declare object variables.
   Set frm = Forms!Employees                  ' Return reference to Form object.
   Set ctl = frm!LastName                     ' Return reference to Control object.
   If ctl.ControlType = acTextBox Then        ' Check ControlType property.
      ctl.Locked = True                       ' Lock control if it's a text box.
   End If
   Set frm = Nothing
End Sub

Although the Forms collection is a member of the Microsoft Access Application object, you don't need to refer to the Application object when you refer to the Forms collection or to other Microsoft Access objects and collections. The Application object is implicitly understood.

You work with objects and collections in the DAO object hierarchy in a similar manner. The next example navigates through the DAO object hierarchy and prints the name of each field in the Employees table.

Sub ListTableFields()
   ' Declare object variables.
   Dim dbs As Database, tdf As TableDef, fld As Field
   ' Return reference to current database.
   Set dbs = CurrentDb
   ' Return reference to Employees table.
   Set tdf = dbs.TableDefs!Employees
   ' Print out all fields in the table.
   For Each fld In tdf.Fields
      Debug.Print fld.Name
   Next fld
   Set dbs = Nothing
End Sub

Enumerating the Objects in a Collection

The previous example shows another concept that's important when working with collections. In order to print out all the fields in the table, the procedure must loop through, or enumerate, all the Field objects in the Fields collection of the TableDef object. You accomplish this by using the For Each...Next statement. You can use the For Each...Next statement to perform the same operation on each member of a collection.

To use the For Each...Next statement, you must first identify which objects you want to enumerate and in which collection they reside. Next, you declare a variable of that type of object. The previous example declares the variable fld as type Field. Within the For Each...Next statement, that variable refers to each object in the Fields collection. By using this variable, you can perform a method or set or return a property on each object in the collection, without knowing how many objects the collection contains.

See Also   For more information on the For Each...Next statement, search the Help index for "For Each...Next statement."

Adding New DAO Objects to a Collection

As stated earlier in this chapter, some DAO objects represent the structure of the database, and others provide a means for you to work with the data stored in the database. Objects that represent the structure of the database are saved with the database. Objects that you use to work with the data in the database generally are not saved, but are created each time you need them.

When you create a new DAO object to be saved with the database, you must append it to the appropriate collection of saved objects. The following example creates a new TableDef object named ArchivedInvoices with a new Field object named OrderID. It appends the new Field object to the Fields collection of the new TableDef object, and it appends the TableDef object to the TableDefs collection of the Database object representing the current database. After you run this code, the new table appears on the Tables tab of the Database window.

Sub AddTable()
   ' Declare object variables.
   Dim dbs As Database, tdf As TableDef, fld As Field
   ' Assign the current database to the database variable.
   Set dbs = CurrentDb
   ' Create new table and field, and assign to table and field variables.
   Set tdf = dbs.CreateTableDef("ArchivedInvoices")
   Set fld = tdf.CreateField("OrderID", dbLong)
   ' Add field to table's Fields collection.
   tdf.Fields.Append fld
   ' Add table to database's TableDefs collection.
   dbs.TableDefs.Append tdf
   ' Refresh TableDefs collection.
   dbs.TableDefs.Refresh
   Set dbs = Nothing
End Sub

Note   The preceding example uses the CurrentDb function to return a reference to the current database, and assigns this reference to an object variable of type Database. Anytime you're writing code to work with the database that's currently open, you should use CurrentDb to return a reference to the current database.

The Properties Collection

DAO objects and Microsoft Access Form, Report, and Control objects all contain a Properties collection. Each Property object in the Properties collection corresponds to a property of the object. You can use an object's Properties collection either to determine which properties apply to a particular object or to return their settings. For example, the following procedure loops through the properties that apply to the Database object, which represents the current database, and to the Employees Form object. The procedure displays the name of each property in the Debug window.

Sub DisplayProperties()
   ' Declare variables.
   Dim dbs As Database, frm As Form, prp As Property
   ' Return reference to current database.
   Set dbs = CurrentDb
   Debug.Print "Current Database Properties"
   ' Enumerate Properties collection.
   For Each prp In dbs.Properties
      Debug.Print prp.Name
   Next prp
   ' Print blank line.
   Debug.Print
   Debug.Print "Employees Form Properties"
   ' Open Employees form in Form view.
   DoCmd.OpenForm "Employees", acWindowNormal
   ' Return reference to Employees form.
   Set frm = Forms!Employees
   ' Enumerate Properties collection.
   For Each prp In frm.Properties
      Debug.Print prp.Name
   Next prp
   Set frm = Nothing
   Set dbs = Nothing
End Sub

Note   If you're looping through the Properties collection of a table or query, some properties aren't displayed because they're added to the collection only when they have a value.

See Also   For more information on the Properties collection, search the Help index for "Properties collection."

Working with CommandBar Objects

Creating new CommandBar objects is somewhat different from creating other new objects in Microsoft Access. To create a new CommandBar object, you use the Add method of the CommandBars collection. The following example creates a new CommandBar object and adds a button to it:

Sub CreateNewCommandBar()
   Dim cmb As CommandBar, cbc As CommandBarControl
   ' Create new CommandBar object and return reference to it.
   Set cmb = CommandBars.Add("NewCommandBar", msoBarFloating, msoBarTypeNormal)
   ' Create new CommandBarControl object and return reference to it.
   Set cbc = cmb.Controls.Add(msoControlButton)
   ' Set properties of new command bar control.
   With cbc
      .Caption = "Button1"
      .DescriptionText = "First button in NewCommandBar"
      .TooltipText = "Button1"
      .Visible = True
   End With
   ' Make command bar visible.
   cmb.Visible = True
   Set cmb = Nothing
End Sub

Note   In order to use objects in the Microsoft Office 8.0 object library from Visual Basic, you must first set a reference to the object library. When you set a reference to an object library, you notify Visual Basic that you may want to use the objects in that library. To set a reference to the Microsoft Office 8.0 object library, open a module and click References on the Tools menu. Then select the Microsoft Office 8.0 Object Library check box in the Available References box.

Creating New Objects with Class Modules

Every object that you use in Microsoft Access is derived from a unique definition for that object. The definition for an object includes its name, its inherent characteristics, and its properties, methods, and events. The definition for an object is known as a class.

To simplify the concept of a class, you can think of a class as a cookie cutter, and an object as the cookie that it makes. You can create multiple objects from a single class, just as you can make multiple cookies with a single cookie cutter. Each individual object has the same characteristics, just as each cookie has the same shape and pattern.

An individual object can also be referred to as an instance of a class. An instance of a class is like a single cookie cut from the cookie cutter. When you create an instance of a class, you create a new object and return an object reference to it. You then work with the instance by setting its properties and applying its methods.

In addition to the objects provided by Microsoft Access and its associated object libraries, you can define your own custom objects in class modules. A class module is a module that can contain the definition for a new object.

   To create a definition for a new object in a class module

1   Define the purpose for your new object. Think of the object in terms of the methods and properties it should have. For example, calling functions in a dynamic-link library (DLL) is often tricky. You can create an object that has methods that contain those function calls. Then, when you want to call a particular function, you can simply call the method that contains it, rather than calling the complex function.

2   Create a new class module by clicking Class Module on the Insert menu. Choose a name for your class and save the class module with that name.

3   Add procedures to the class module. Any Sub or Function procedures that you define in a class module become custom methods of your new object. Any Property Get, Property Let, or Property Set procedures that you define become custom properties of your new object.

4   If you want certain code to run when an instance of the class is created, add that code to the class's Initialize event procedure. If you want certain code to run when an instance of the class is removed from memory, add it to the class's Terminate event procedure.

See Also   For more information on the Initialize and Terminate events, search the Help index for "Initialize event" or "Terminate event."

5   Test the new class by creating an instance of it and applying its methods and setting its properties. To create an instance of your class, use the New keyword to declare an object variable of type classname, where classname represents the name of your class. The New keyword creates a new instance of the class.

For example, if your class is named NewClass, you can declare a new instance of it as shown in the following line of code:

Dim obj As New NewClass

If you've defined a method called ListNames within the class module, you can then apply that method as follows:

Obj.ListNames

See Also   For more information on the New keyword, search the Help index for "New keyword."

You can view the new class and its variables, methods, and properties in the Object Browser, which is available through the View menu. In the Project/Library box, click the name of your project, and then click the name of the class in the Classes box. You can determine the name of your project by checking the value in the Project Name box on the Advanced tab of the Options dialog box (Tools menu).

See Also   For more information on the Object Browser, see "Using the Object Browser" later in this chapter. For more information on programming with class modules, search the Help index for "class modules."

Creating Multiple Instances of Forms and Reports

Form modules and report modules are also class modules. They are identical to the class modules on the Modules tab of the Database window, except that they are associated with forms and reports. Since form and report modules are class modules, you can create one or more instances of a form or report class. This is useful if you want to display more than one instance of a form or report at a time.

When you create a new instance of a form or report class, the new instance has all the properties and methods of a Form or Report object, and its properties are set to the same values as those in the original Form or Report object. Additionally, any procedures that you have written in the form or report class module behave as methods and properties of the new instance.

To create a new instance of a form or report class, you declare a new object variable with the name of the form or report's class module and the New keyword. The name of the class module appears in the title bar of the module. It indicates whether the class is associated with a form or a report and includes the name of the form or report. For example, the class name for an Orders form is Form_Orders. The following line of code creates a new instance of the Orders form:

Dim frmInstance As New Form_Orders

By creating multiple instances of a Orders form class, you could show information about one order on one form instance, and information about another order on another form instance.

When you create an instance of a form class by using the New keyword, it is hidden. To show the form, set the Visible property to True.

You should declare the variable that represents the new instance of a form class at the module level. If you declare the variable at the procedure level, the variable goes out of scope when the procedure finishes running, and the new instance is removed from memory. The instance exists in memory only as long as the variable to which it is assigned remains in scope.

Note   When you create a new form or report in Microsoft Access, the form or report doesn't automatically have an associated module. Forms and reports without associated modules load more quickly. If you're working in form or report Design view, Microsoft Access automatically creates the form or report module when you click Code on the View menu. Once you enter code in the module, Microsoft Access saves the module with the form or report.

Whether or not the form or report module exists is determined by the setting of the HasModule property. When a form or report is created, the HasModule property is automatically set to False. When you create a form or report module by clicking Code on the View menu, Microsoft Access sets the HasModule property to True. If you refer to the Module property of a form or report, the HasModule property is also automatically set to True. For more information on the HasModule property, search the Help index for "HasModule property."

© 1996 Microsoft Corporation. All rights reserved.