Working with Objects and Collections
CHAPTER 4
Working with Objects and Collections
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
A Microsoft® Access database is made up of different kinds of objects. Some kinds of objects are used to display the data in your database, while others are used to store and manage the data itself, or to assist you in programming in Visual Basic® for Applications (VBA). You can create a database using tables, queries, forms, reports, and macros without writing any VBA code. If your needs are more sophisticated, however, you can use VBA to create, control, and manage all of the different types of objects in an Access database. This chapter explains how to program with objects in VBA.
Chapter Contents
Understanding Objects and Collections
Working with Objects and Collections
Working with Properties and Methods
Using the Object Browser
Understanding Objects and Collections
As you’ve seen in earlier chapters of this book, you can produce powerful applications in Access without programming. However, when you need a more sophisticated application, writing VBA code gives you a greater degree of control over what your application does. When you program in VBA, you work with objects that correspond to different aspects of your database. Collections are sets of objects of the same type. Programming with objects and collections gives you added flexibility in that you can design your application to respond to user actions and input in a customized way.
Objects available to you in Access come from four different sources:
Access provides objects that you use to display your data, such as Form, Report, Control, and Page objects.
Microsoft DAO provides Data Access Objects (DAO), such as the TableDef and QueryDef objects, which determine the structure of your database and which you can use to manipulate data in VBA.
VBA provides objects that give you more flexibility in programming, such as the Debug object and the Err object.
Microsoft Office provides objects that you can use to customize the interface of your application, such as the CommandBar and FileSearch objects.
Many of the objects that you work with in VBA correspond to specific parts of your Access database. For example, the Microsoft Access Form and Report objects correspond to your forms and reports, while the DAO TableDef and QueryDef objects correspond to your tables and queries. The Microsoft Office CommandBar object corresponds to the toolbars, menu bars, menus, and shortcut menus you see in Access.
Other objects you may work with in VBA are more abstract. For example, the VBA Err object contains information about errors that occur while VBA code is running, rather than corresponding to a specific part of your database.
Even though the objects available to you in Access come from several different sources and perform different functions, you can work with them in similar ways. You’ll find that the concepts you need to understand in order to program with one object apply to most objects.
For example, every object has properties and methods associated with it. Once you understand how to work with one object’s properties and methods, you can use those same concepts to work with any object’s properties and methods. You use properties to determine or change some characteristic of an object. For example, you use a form’s Visible property to determine whether or not the form is visible to the user. You use methods to perform a particular operation on an object. For example, the Repaint method completes any pending screen updates on a specified form.
****See Also **** For more information about how to work with objects and collections, see Chapter 4, “Understanding Office Objects and Object Models,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online.
Organization of Objects and Collections
Each application that supplies objects to Access provides an object library. The object library contains information about an application’s objects and their properties and methods. Access includes four built-in object libraries, which are described in the following sections.
Microsoft Access objects and DAO objects are organized in object hierarchies. In an object hierarchy, certain objects contain other objects and collections. A collection is a special type of object that is actually a set of all objects of a given type. When you refer to a collection in code, you are referring to all the objects in the set. For example, the Microsoft Access Application object contains a Forms collection, which contains individual Form objects. A Form object contains a Controls collection, which in turn contains individual Control objects. The following illustration shows this relationship.
.jpg)
You can think of a collection as an array of objects that’s already declared by Access. Collections, like arrays, have elements, and you refer to the objects in a collection as elements of the collection. You can refer to an element of a collection by its name or by its position within the collection.
****See Also **** For more information see Chapter 7, “Getting the Most Out of Visual Basic for Applications,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
The following sections describe the four object libraries included in Access.
Microsoft Access Objects
You’re probably already familiar with some of the objects in the Microsoft Access 9.0 object library. Microsoft Access Form, Report, and Control objects correspond to your forms, reports, and controls. You use these objects to control the way you display the data in your database. The Application object and the Reference object make it easier to work with objects in other applications. The Module object gives you control over the VBA modules in your database. You use the DoCmd object to include macro actions in your code and the Screen object to refer to the active object on the screen.
The following table describes some of the objects provided by the Microsoft Access 9.0 object library.
| Object | Description |
| Application | Active Access application |
| Control | Control on a form or report |
| DoCmd | Macro actions used in VBA code |
| Form | Open form, including subforms |
| Module | Open standard module or class module |
| Reference | Reference to an application’s object library |
| Report | Open report, including subreports |
| Screen | Screen that currently has the focus |
****See Also **** For information about an individual object, type the name of the object in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Of the objects listed in the preceding table, the Control, Form, Module, Reference, and Report objects belong to collections. The Application object is the top-level object in the hierarchy, and all other objects exist beneath it. The following illustration shows part of the hierarchical organization of Microsoft Access objects and collections.
.jpg)
****See Also **** For more information about Access objects, see Chapter 5, “Working With Office Applications,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
DAO Objects
The Microsoft DAO 3.6 object library provides DAO objects, which you can use to work with the Microsoft Jet database engine. Some DAO objects represent the structure of your database and the relationships between the data in it. These objects include the Database, TableDef, QueryDef, Field, Index, Parameter, Property, and Relation objects. Other objects are responsible for the security of your database, including the Container, Document, User, Group, and Workspace objects. The Recordset object provides you with direct access to the data in the database. The DBEngine object gives you control over the Microsoft Jet database engine itself. The Connection object represents a network connection to an Open Database Connectivity (ODBC) database and is available only when you’re working with an ODBCDirect Workspace object.
****See Also **** For more information about data access methods, see Chapter 14, “Working with the Data Access Components of an Office Solution” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
The following table describes the DAO objects provided by the Microsoft DAO 3.6 object library.
| Object | Description |
| Connection | Network connection to an ODBC database |
| Container | Security and other information for various types of objects in the database |
| Database | Open database |
| DBEngine | Microsoft Jet database engine itself |
| Document | Security and other information for individual objects in the database |
| Error | Data access error information |
| Field | Field in a table, query, recordset, index, or relation |
| Group | Group account in Microsoft Jet’s current workgroup |
| Index | Table index |
| Parameter | Query parameter |
| Property | Property of an object |
| QueryDef | Saved query in a database |
| Recordset | Set of records defined by a table or query |
| Relation | Relationship between two table or query fields |
| TableDef | Saved table in a database |
| User | User account in Microsoft Jet’s current workgroup |
| Workspace | Active Microsoft Jet session |
Every object in the Microsoft DAO 3.6 object library belongs to an associated collection, except the DBEngine object. The DBEngine object is the top-level object that gives you access to all other objects in the collection, like the Microsoft Access Application object in the Microsoft Access object hierarchy. The following illustration shows the hierarchical organization of the DAO objects. For simplicity, it shows only the DBEngine object and the collections for all other objects in the object hierarchy.
.jpg)
Each DAO object also contains a Properties collection. The Properties collection contains Property objects that represent the properties available for each object in the DAO object hierarchy.
****See Also **** For more information about the Properties collection, see “The Properties Collection” later in this chapter.
VBA Objects
The Visual Basic for Applications object library provides three objects to Access, but these objects aren’t organized in an object hierarchy. None of them belong to a collection of other objects. The following table describes the objects provided by the VBA object library.
| Object | Description |
| Collection | User-defined collection |
| Debug | Immediate window |
| Err | Information about VBA errors |
****See Also **** For information about debugging methods, see Chapter 8, “Error Handling and Debugging” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Microsoft Office Objects
The Microsoft Office 9.0 object library provides objects you can use to customize the appearance of your application or to implement some features common to Microsoft Office applications. For example, you can create customized toolbars and menu bars in code by using the CommandBar object. You can perform custom file searches by using the FileSearch object. You can also customize the Office Assistant to respond to the user’s actions.
****Note **** In order to use objects in the Microsoft Office 9.0 object library from VBA, you must first set a reference to the object library. When you set a reference to an object library, you notify VBA that you may want to use the objects in that library. To set a reference to the Microsoft Office 9.0 object library, open a module and click References on the Tools menu. Then select the Microsoft Office 9.0 Object Library check box in the Available References box.
Not all of the objects in the Microsoft Office 9.0 object library are useful in Access. The following table describes some of the objects in the Microsoft Office 9.0 object library which Access developers may find useful.
| Object | Description |
| Assistant | The Office Assistant |
| Balloon | Balloon associated with the Office Assistant |
| BalloonCheckBox | Check box control for the balloon |
| BalloonLabel | Label control for the balloon |
| CommandBar | Toolbar, menu bar, menu, or shortcut menu |
| CommandBarButton | Button on a CommandBar object |
| CommandBarComboBox | Combo box control on a CommandBar object |
| CommandBarControl | Any control on a CommandBar object |
| CommandBarPopup | Pop-up control on a CommandBar object |
| FileSearch | Microsoft Office file searching |
| FoundFiles | Files found through file search operation |
****Note **** The Office Assistant is not available in Access run-time applications.
****See Also **** For more information about Office objects, see Chapter 6, “Working with Shared Office Components,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Working with Objects and Collections
Now that you’re familiar with the objects available in Access, you can begin working with them in VBA. The following sections provide you with the information you need to begin working with objects.
Referring to Objects
To use an object in VBA, 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 VBA 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. However, if you use the new AllForms collection, you can access any form in the database, even if it is not open or loaded.
****See Also **** For more information about the AllForms collection, type AllForms in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
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, type the name of the collection in the Office Assistant or the Answer Wizard tab in the Help window, and then click Search.
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, VBA 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 VBA, you’re actually working with a reference to that object in memory.
Referring to Objects in a Default Collection
Many objects in 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.
| Object library | Object | Default collection |
| Microsoft Access | Form | Controls |
| Report | Controls | |
| DAO | Container | Documents |
| Database | TableDefs | |
| DBEngine | Workspaces | |
| Group | Users | |
| Index | Fields | |
| QueryDef | Parameters | |
| Recordset | Fields | |
| Relation | Fields | |
| TableDef | Fields | |
| User | Groups | |
| Workspace | Databases |
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 VBA. 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 VBA.
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 about declaring variables, see Chapter 2, “Introducing Visual Basic for Applications.”
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, VBA looks up the object in memory. When you assign the object reference to an object variable, it’s stored in that variable so that VBA 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 frmpoints 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 VBA 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 frmEmployee As Form
Dim ctlText As Control
On Error GoTo ErrorHandler
Set frmEmployee = Forms!Employees
Set ctlText = frmEmployee!LastName
'Check ControlType property.
If ctlText.ControlType = acTextBox Then
ctlText.Locked = True 'Lock control if it's a text box.
End If
Set frmEmployee = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
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()
Dim dbsOrders As DAO.Database
Dim tdfEmployees As DAO.TableDef
Dim fldField As DAO.Field
On Error GoTo ErrorHandler
'Return reference to current database.
Set dbsOrders = CurrentDb
'Return reference to Employees table.
Set tdfEmployees = dbsOrders.TableDefs!Employees
'Print out all fields in the table.
For Each fldField In tdfEmployees.Fields
Debug.Print fldField.Name
Next fldField
dbsOrders.Close
Set fldField = Nothing
Set tdfEmployees = Nothing
Set dbsOrders = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
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 fldField 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 about the For Each…Next statement, type For Each…Next in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
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()
Dim dbsOrders As DAO.Database
Dim tdfTable As DAO.TableDef
Dim fldField As DAO.Field
On Error GoTo ErrorHandler
'Assign the current database to the database variable.
Set dbsOrders = CurrentDb
'Create new table and field, and assign to table and field variables.
Set tdfTable = dbsOrders.CreateTableDef("ArchivedInvoices")
Set fldField = tdfTable.CreateField("OrderID", dbLong)
'Add field to table's Fields collection.
tdfTable.Fields.Append fldField
'Add table to database's TableDefs collection.
dbsOrders.TableDefs.Append tdfTable
'Refresh TableDefs collection.
dbsOrders.TableDefs.Refresh
dbsOrders.Close
Set fldField = Nothing
Set tdfTable = Nothing
Set dbsOrders = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
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 using DAO to write code to work with the database that’s currently open, you should use CurrentDb to return a reference to the current database.
****See Also **** For information about using ADO to reference the current database, see Chapter 14, “Working with the Data Access Components of an Office Solution,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
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 Immediate window.
Sub DisplayProperties()
Dim dbsOrders As DAO.Database
Dim prpCurrent As DAO.Property
Dim frmEmployees As Form
On Error GoTo ErrorHandler
'Return reference to current database.
Set dbsOrders = CurrentDb
Debug.Print "Current Database Properties"
'Enumerate Properties collection.
For Each prpCurrent In dbsOrders.Properties
Debug.Print prpCurrent.Name
Next prpCurrent
'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 frmEmployees = Forms!Employees
'Enumerate Properties collection.
For Each prpCurrent In frmEmployees.Properties
Debug.Print prpCurrent.Name
Next prpCurrent
dbsOrders.Close
Set prpCurrent = Nothing
Set frmEmployees = Nothing
Set dbsOrders = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
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 about the Properties collection, type Properties Collection in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Working with CommandBar Objects
Creating new CommandBar objects is somewhat different from creating other new objects in 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 cmbBar As CommandBar
Dim cbcControl As CommandBarControl
On Error GoTo ErrorHandler
'Create new CommandBar object and return reference to it.
Set cmbBar = CommandBars.Add("NewCommandBar", msoBarFloating)
'Create new CommandBarControl object and return reference to it.
Set cbcControl = cmbBar.Controls.Add(msoControlButton)
'Set properties of new command bar control.
With cbcControl
.Caption = "Button1"
.DescriptionText = "First button in NewCommandBar"
'Run this function when button is pressed.
.OnAction = "Button1Function()"
.Visible = True
End With
'Make command bar visible.
cmbBar.Visible = True
Set cbcControl = Nothing
Set cmbBar = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
****Note **** In order to use objects in the Microsoft Office 9.0 object library from VBA, you must first set a reference to the object library. When you set a reference to an object library, you notify VBA that you may want to use the objects in that library. To set a reference to the Microsoft Office 9.0 object library, open a module and click References on the Tools menu. Then select the Microsoft Office 9.0 Object Library check box in the Available References box.
****See Also **** For more information about using command bars, see Chapter 6, “Working with Shared Office Components” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
Creating New Objects with Class Modules
Every object that you use in 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 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
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.
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.
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.
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 about the Initialize and Terminate events , type Initialize Event or Terminate Event in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
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 NewClassIf you’ve defined a method called ListNames within the class module, you can then apply that method as follows:
obj.ListNames
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 Code window. 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 about the Object Browser, see “Using the Object Browser” later in this chapter, or see Chapter 9, “Custom Classes and Objects,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.
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 object list in 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 by using the New keyword and the name of the form or report’s class module. 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 an Orders form class, you could show information about one order on one form instance, and information about another order on another form instance.
Tip 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 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, Access automatically creates the form or report module when you click Code on the View menu. Once you enter code in the module, 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, 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.
****See Also **** For more information about the HasModule property , type HasModule Property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
Working with Properties and Methods
To describe an object’s characteristics, you use its properties. You can set properties to change their values, or read properties to get information about the object. To control how an object behaves, you use its methods. An object’s methods determine what operations you can perform on that object.
Because a collection is also an object, each collection in Access has its own properties and methods. You can set or read the properties of a collection, or apply its methods, in the same manner that you would for any object.
See Also **** For more information about the properties and methods an object supports, type the name of the objectin the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. You can also type in the name of a property or method.
Setting and Reading Properties
VBA provides a standard syntax for setting and reading properties in code. When you set a property, you give it a new value. You can use the following syntax to set a property for any type of object:
object**.property=**setting
The following line of code sets the Caption property of the Employees form:
Forms!Employees.Caption = "Employees Form"
When you read the value of a property, you determine its current value. In order to read the property, you can assign its value to a variable or to another property, or you can display it in the Immediate window, in a dialog box, or in a control on a form or report. The following example assigns the value of the Caption property to a variable and then displays the value of that property in a dialog box.
Dim strCaption As String
strCaption = Forms!Employees.Caption
MsgBox strCaption
Properties That Return Objects
Sometimes you may want your code to refer to whatever object happens to be in a particular state at the time a procedure is running, rather than to a specific object. Writing code in this way can make your application more flexible. For instance, you may want to change the caption of the active form, without knowing the form’s name. Or you may want to hide the control that has just lost the focus.
Rather than determining an object’s characteristics, some properties of an object represent another object that is related in some way. These properties return an object reference that you can work with directly or assign to an object variable, just as you would any object reference. The following table lists several properties that return objects.
| Property | Applies to | Returns a reference to |
| ActiveControl | Screen, Form, or Report object | The Control object that has the focus. |
| ActiveForm | Screen object | The Form object that has the focus or that contains the control with the focus. |
| ActiveReport | Screen object | The Report object that has the focus or that contains the control with the focus. |
| Application | Numerous objects | The active Microsoft Access Application object. |
| DBEngine | Application object | The current DBEngine object. |
| Form | Subform Control object | The Form object associated with the subform control. |
| Me | Form or Report object | The Form or Report object in which code is currently running. |
| Module | Form or Report object | The Module object associated with a Form or Report object. |
| Parent | Numerous objects | The object or collection that contains an object. |
| PreviousControl | Screen object | The Control object that had the focus immediately before the currently active control. |
| RecordsetClone | Form object | A clone of the form’s underlying recordset. |
| Report | Subreport Control object | The Report object associated with the subreport control. |
| Section | Form, Report, or Control object | A section on a form or report. |
****See Also **** For more information about properties that return objects, type the name of the specific property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
The Section Property
The Section property returns a reference to a section of a form or report. For example, you can use the Section property to return a reference to the detail section of a form. Once you’ve returned a reference to a section, you can set the section’s properties. The following example uses the Section property to set a property of the detail section on an Employees form.
Forms!Employees.Section(acDetail).Visible = False
****See Also **** For more information about the setting properties of sections, type Section Property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.
The Me Property
The Me property returns an object reference to the Form or Report object in which the code is currently running. You can use the Me property to refer to a Form or Report object from within that object, without needing to know the name of the form or report. You can also use it to pass a Form or Report object to a procedure that takes an argument of type Form or Report.
For example, the following code uses the Me property to return a reference to the Employees form, the form in which the code is running. It then passes this reference to the ChangeDetailColor procedure which it calls when the form’s Current event occurs. It also uses the Me property to return references to the Employees form in order to set a property and to return the values of the FirstName and LastName controls on the form. Note that the . (dot) operator is used to set the property, and the ! operator is used to refer to the controls on the form.
'Place this procedure in a standard module.
Sub ChangeDetailColor(frm As Form)
frm.Section(acDetail).BackColor = RGB(Rnd * 256, Rnd * 256, Rnd * 256)
End Sub
'Place this procedure in the form module associated with the Employees
'form.
Private Sub Form_Current()
ChangeDetailColor Me
Me.Caption = Me!FirstName.Value & " " & Me!LastName.Value
End Sub
In most cases, the form or report represented by the Me property is the same form or report represented by the ActiveForm or ActiveReport property of the Screen object. However, the ActiveForm and ActiveReport properties represent the active form or report, whereas the Me property represents the form or report in which the code is running. For example, a Timer event can occur on a form called Customers, even if the Customers form isn’t active. In a Timer event procedure for the Customers form, Screen.ActiveForm represents the active form, whatever it is, and Me always represents the Customers form.
Using Methods
Methods are built-in operations that you can perform on an object. There are two kinds of methods: those that return a value or an object, as a function does, and those that perform a specific operation, as a statement does. To apply a method to an object, you use the following syntax:
object**.**method [[(] arg1, arg2...[)]]
Many methods take one or more arguments. An argument provides the method with additional information for its operation. If the method returns a value or an object, you must enclose its argument list in parentheses; otherwise you should omit the parentheses.
The following example shows the syntax for several different methods. The OpenRecordset method creates a new Recordset object and returns a reference to the new object. You can assign this object reference to an object variable by using the Set statement. Because the OpenRecordset method returns a value, you must enclose its arguments in parentheses. The FindFirst method, on the other hand, doesn’t return a value. It simply sets the current record pointer to the first record that matches the criteria given in the FindFirst argument. Since this method doesn’t return a value, you don’t need to enclose its arguments in parentheses. The same is true for the Print method of the Debug object. Finally, the Close method of the Recordset object doesn’t take any arguments.
Sub FindEmployee()
Dim dbsOrders As DAO.Database
Dim rstEmployees As DAO.Recordset
On Error GoTo ErrorHandler
Set dbsOrders = CurrentDb
'Requires parentheses.
Set rstEmployees = dbsOrders.OpenRecordset("Employees", dbOpenDynaset)
rstEmployees.FindFirst "[HireDate] >= #1-1-93#"
Debug.Print rstEmployees!LastName
rstEmployees.Close
dbsOrders.Close
Set rstEmployees = Nothing
Set dbsOrders = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
Performing Multiple Actions on an Object
You’ll often need to perform several different actions on the same object. For example, you may need to set several properties for the same object within a single procedure. Instead of using many separate statements to do this, you can use the With...End With statement. The following example uses the With...End With statement to set several properties for a command button named HelpButton in a form’s Load event.
Private Sub Form_Load()
With Me!HelpButton
.Caption = "Help"
.Visible = True
.Top = 200
.Left = 5000
.Enabled = True
End With
End Sub
Using the Object Browser
The Object Browser is a tool that provides information about objects and their methods, properties, events, and constants. The Object Browser displays all objects available to Access, including Microsoft Access objects, DAO objects, VBA objects, and objects you’ve defined within your application.
The object information that you see in the Object Browser comes from an application’s object library. Each application that supplies objects to Access has an object library that contains information about the application’s objects, methods, properties, events, and constants.
****Note **** Some objects show up in the Object Browser automatically when you start Access. Others, such as the Microsoft Office objects, show up only after you have set a reference to the object library that contains them.
To use the Object Browser
Open a module.
On the View menu, click Object Browser.
In the Project/Library box, click the object library whose objects you want to see, or click <All Libraries> to view the objects of all referenced libraries together.
The Classes box lists all the objects in the object library you selected.
In the Classes box, click an object.
The Members Of box lists the methods, properties, events, and constants associated with the object you selected.
For example, if you click Access in the Project/Library box, all the objects in the Microsoft Access object library are displayed in the Classes box. If you then click an object in the Classes box, you can view the object’s members—the methods, properties, events, and constants associated with that object—in the Members Of box. A class definition includes all of an object’s members. For example, if you click Control in the Classes box, you see the methods and properties of that Control object displayed in the Members Of box.
.jpg)
From the Object Browser, you can get help on a particular object, method, property, or event. Just click the item you’re interested in and then click the Help button on the Object Browser’s toolbar. You can also copy a particular item to the Clipboard so that you can paste it into your code. Click the item you want to copy and click the Copy button on the Object Browser’s toolbar.
You can also view procedures that you’ve created yourself in Access in the Object Browser. The Project/Library box displays the name of the current database and any other referenced databases, in addition to the other referenced object libraries. If you click a referenced database in the Project/Library box, the Classes box displays all standard modules in the database and their public procedures. If you select the current database, it also displays any class modules and their methods and properties.
****See Also **** For more information about the Object Browser, type Object Browser in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.