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:
Private Sub cmdChangeCaption_Click()
'Declare a CommandButton object
Dim cmdAny As CommandButton
'Point the CommandButton object at the cmdHello Command button
Set cmdAny = Me.cmdHello
'Change the Caption of the control referenced by the cmdAny variable
cmdAny.Caption = "Hello"
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:
Dim intVar1 As Integer
Dim intVar2 As Integer
intVar1 = 5
intVar2 = intVar1
intVar1 = 10
Debug.Print intVar1 'Prints 10
Debug.Print intVar2 'Prints 5
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:
Private Sub cmdObjectVariable_Click()
Dim ctlText As TextBox
Set ctlText = Forms.frmSales.txtProductID
ctlText.Text = "New Text"
Debug.Print Forms.frmSales.txtProductID.Text 'Prints New Text
This routine creates an object variable called ctlText of type TextBox. It then associates the object variable with Forms.frmSales.txtProductI
D. Next, it modifies the Text property of the object variable. Because the object variable is actually pointing to the text box on the form, the Debug.Print statement prints the new text value.
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:
Private Sub ChangeVisible_Click()
Dim ctlAny As Control
Set ctlAny = Me.txtCustomerID
ctlAny.Visible = False
In this example, ctlAny can be used to point to any control. Compare that with the following code:
Private Sub cmdChangeVisible_Click()
Dim txtAny As TextBox
Set txtAny = Me.txtCustomerID
txtAny.Visible = False
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:
Set frmNew = Nothing
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
Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "SELECT * FROM tblClients WHERE StateProvince = 'CA'"
Do Until rst.EOF
Set rst = Nothing
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:
Me.txtCustomerID.Enabled = False
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:
Me.txtCustomerID.Enabled = False
Me.txtCustomerID.SpecialEffect = 1
Me.txtCustomerID.FontSize = 16
Me.txtCustomerID.FontWeight = 700
Contrast this with the following code:
.Enabled = False
.SpecialEffect = 1
.FontSize = 16
.FontWeight = 700
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:
Dim frm As Form
For Each frm In Forms
frm.Caption = frm.Caption & " - " & CurrentUser
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:
Sub ChangeCaption(frmAny as Form)
'Change the caption property of the form received
'to what was already in the caption property,
'concatenated with a colon and the name of the current user
frmAny.Caption = frmAny.Caption & ": " & CurrentUser
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:
Private Sub cmdChangeCaption_Click()
'Call the ChangeCaption routine, passing a reference to the current form
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):
Sub FlipEnabled(frmAny As Form, ctlAny As Control)
'Declare a control object variable
Dim ctl As Control
'Loop through the Controls collection using the For..Each Construct
ctlAny.Enabled = True
For Each ctl In frmAny.Controls
'Evaluate the type of the control
If ctl.ControlType = acCommandButton Then
'Make sure that we don’t try to disable the command button _
that invoked this routine
If ctl.Name <> ctlAny.Name Then
ctl.Enabled = Not ctl.Enabled
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:
Private Sub cmdAdd_Click()
'Call the FlipEnabled routine, passing references to the current form,
'and to the cmdSave command button on the current form
Call FlipEnabled(Me, Me.cmdSave)
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:
Screen.ActiveForm.Caption = Screen.ActiveForm.Caption & _
" - " & CurrentUser()
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.