Awards
- Experts Exchange Approved
- Editor's Choice
In the article Objects – Part 1, you learned the basics of working with objects, properties, methods, and events. In this article we step it up a level. You learn how to work with object variables and how to work with collections of objects. You also learn how to pass objects to subroutines and functions, and we explore special properties that refer to objects. As with the techniques covered in Part 1, the techniques that you learn in this article make you much more powerful as a VBA programmer.
Declaring and Assigning Object Variables
Object variables are variables that reference an object of a specific type, such as databases, recordsets, forms, controls, and even objects created in other applications. They allow you to create shortcut references to objects and pass objects to subroutines and functions. You can use them to streamline code by using short names to refer to objects with long names and to optimize code by supplying a direct pointer to a particular object.
First, you must declare an object variable; then you assign, or point, the object variable to a particular object, as shown in the following code:
Declaring and Assigning Object Variables
Object variables are variables that reference an object of a specific type, such as databases, recordsets, forms, controls, and even objects created in other applications. They allow you to create shortcut references to objects and pass objects to subroutines and functions. You can use them to streamline code by using short names to refer to objects with long names and to optimize code by supplying a direct pointer to a particular object.
First, you must declare an object variable; then you assign, or point, the object variable to a particular object, as shown in the following code:
This code creates an object variable called cmdAny of the type CommandButton. You then use the Set statement to point your CommandButton object variable toward the cmdHello object on the current form, using the Me keyword. Finally, you modify the caption of the cmdAny object variable. Because an object variable is a reference to the original object, you’re actually changing the caption of the cmdHello command button.
Object Variables Versus Regular Variables
The difference between object variables and regular variables is illustrated by the following code:
This code uses ordinary variables. When you dimension these variables, each one is assigned a separate memory location. Although intVar2 is initially assigned the value of intVar1, changing the value of intVar1 has no effect on intVar2. This differs from the following code, which uses an object variable:
This routine creates an object variable called ctlText of type TextBox. It then associates the object variable with Forms.frmSales.txtProductI
Generic Versus Specific Object Variables
Access supports the use of generic object variables, including Application, Control, Form, and Report. Generic object variables can be used to refer to any object of that generic type:
In this example, ctlAny can be used to point to any control. Compare that with the following code:
Here, your object variable can be used only to point to a text box.
Cleaning Up After Yourself
When you’re finished working with an object variable, you should set its value to Nothing. As used in the following example, this statement frees up all memory and system resources associated with the object:
Note: When working with certain objects, such as the RecordSet object, you should first use the Close method of the object, and then set it equal to Nothing. The code that follows provides an example:
Understanding the Differences Between Objects and Collections
Many people get confused about the differences between an object and a collection. Think of an object as a member of a collection. For example, frmHello is a form that’s a member of the Forms collection; cmdHello, a command button on frmHello, is a member of the Controls collection of frmHello. Sometimes you want to manipulate a specific object, but other times you want to manipulate a collection of objects.
Manipulating a Single Object
You have already learned quite a bit about manipulating a single object, such as setting the Enabled property of a text box:
This line of code affects only one text box and only one of its properties. However, when you’re manipulating a single object, you might want to affect several properties at the same time. In that case, it’s most efficient to use the With...End With construct, explained in the following section.
One method you can use to modify several properties of an object is to modify the value of each property, one at a time:
Contrast this with the following code:
This code uses the With...End With statement to assign multiple properties to an object. In addition to improving the readability of your code, the With...End With construct results in a slight increase in performance.
Manipulating a Collection of Objects
A collection is like an array of objects. What makes the array special is that it’s defined and maintained by Access. Every collection in Microsoft Access is an object, each with its own properties and methods. The VBA language makes it easy for you to manipulate Access’s collections of objects; you simply use the For Each…Next construct, which performs the same command on multiple objects.
In the "Determining the Type of a Control" section later in this article, you learn how to loop through the collection of controls on a form, performing actions on all the command buttons. This illustrates a practical use of a collection. In the following example, you loop through all the open forms, changing the caption of each form:
This routine uses the For Each…Next construct to loop through each form in the Forms collection, setting the caption of each form to the form’s caption concatenated with the current username. As you travel through the loop, the code frm.Caption refers to each member of the Forms collection.
Passing Objects to Subroutines and Functions
Just as you can pass a string or a number to a subroutine or function, you can also pass an object to a subroutine or function. The code, found in the basExamples module in the Chap9Ex database, looks like this:
The ChangeCaption routine receives a reference to a form as a parameter. The caption of the form referenced by the procedure is modified to include the name of the current user. The ChangeCaption routine is called like this:
In this example, the click event of the cmdChangeCaption command button calls the ChangeCaption routine, sending a reference to the form that the command button is contained within. You will find this code in the frmChangeCaption form.
Determining the Type of a Control
When writing generic code, you might need to determine the type of a control. For example, you might want to loop through all the controls on a form and flip the Enabled property of all the command buttons. To do this, use the ControlType property of a control. Here’s an example of how it’s used (you can find this in Chap9Ex.accdb in the module called basExamples):
The FlipEnabled procedure is called from the frmTypeOf form. Each command button on the form (Add, Edit, Delete, and so on) sends the form and the name of a control to the FlipEnabled routine. The control that it sends is the one that you want to receive the focus after the routine executes. In the example that follows, the code sends the cmdSave command button to the FlipEnabled routine. The FlipEnabled routine sets focus to the Save button:
The FlipEnabled routine receives the form and control as parameters. It begins by enabling the command button that was passed to it and setting focus to it. The FlipEnabled routine then uses the VBA construct For...Each to loop through all the controls on a form. The For...Each construct repeats a group of statements for each object in an array or collection[md]in this case, the Controls collection. The code evaluates each control on the form to determine whether it’s a command button. If it is, and it isn’t the command button that was passed to the routine, the routine flips the control’s Enabled property. The following VBA intrinsic controls are used when evaluating the ControlType property of a control:
Intrinsic Constant - Type of Control
- acLabel - Label
- acRectangle - Rectangle
- acLine - Line
- acImage - Image
- acCommandButton - Command button
- acOptionButton - Option button
- acCheckBox - Check box
- acOptionGroup - Option group
- acBoundObjectFrame - Bound object frame
- acTextBox - Text box
- acListBox - List box
- acComboBox - Combo box
- acSubform - Subform/subreport
- acObjectFrame - Unbound object frame or chart
- acPageBreak - Page break
- acPage - Page
- acCustomControl - ActiveX (custom) control
- acToggleButton - Toggle button
- acTabCtl - Tab
Using Special Properties That Refer to Objects
VBA offers the convenience of performing actions on the active control, the active form, and other specially recognized objects. The following is a list of special properties that refer to objects in the Access Object Model:
- The ActiveControl property refers to the control that has focus on a screen object, form, or report.
- The ActiveForm property refers to the form that has focus.
- The ActiveReport property refers to the report that has focus.
- The Form property refers to the form that a subform is contained in or to the form itself.
- Me refers to the form or report where code is currently executing.
- Module refers to the module of a form or report.
- The Parent property refers to the form, report, or control that contains a control.
- PreviousControl refers to the control that had focus immediately before the ActiveControl.
- RecordsetClone refers to a clone of the form’s underlying recordset.
- The Report property refers to the report that a subform is contained in or to the report itself.
- The Section property refers to the section in a form or report where a particular control is located.
The following example using the Screen.ActiveForm property shows how a subroutine can change the caption of the active form:
This subroutine modifies the caption of the active form, appending the value of the CurrentUser property onto the end of the existing caption.
You just learned numerous techniques that help you to work with objects in your applications. In the final article, Objects – Part 3, you will learn how to work with Access’s rich object model. You will also learn how to take advantage of some special properties.
This article was excerpted from Alison Balter's Mastering Microsoft Office Access 2007 Development, published by SAMS Publishing.