Working with Objects - Part 2

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"
                      End Sub

Select all Open in new window

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

Select all Open in new window

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
                      End Sub

Select all Open in new window

This routine creates an object variable called ctlText of type TextBox. It then associates the object variable with Forms.frmSales.txtProductID. 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
                      End Sub

Select all Open in new window

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
                      End Sub

Select all Open in new window

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

Select all Open in new window

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:

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
                          MsgBox rst("CompanyName")
                          rst.MoveNext
                      Loop
                      rst.Close
                      Set rst = Nothing

Select all Open in new window

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:

Me.txtCustomerID.Enabled = False

Select all Open in new window

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

Select all Open in new window

Contrast this with the following code:

With Me.txtCustomerID
                          .Enabled = False
                          .SpecialEffect = 1
                          .FontSize = 16
                          .FontWeight = 700
                      End With

Select all Open in new window

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:

Sub FormCaptions()
                          Dim frm As Form
                          For Each frm In Forms
                              frm.Caption = frm.Caption & " - " & CurrentUser
                          Next frm
                      End Sub

Select all Open in new window

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
                      End Sub

Select all Open in new window

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
                          Call ChangeCaption(Me)
                      End Sub

Select all Open in new window

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
                          ctlAny.SetFocus
                          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
                                  End If
                              End If
                          Next ctl
                      End Sub

Select all Open in new window

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)
                      End Sub

Select all Open in new window

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


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 following example using the Screen.ActiveForm property shows how a subroutine can change the caption of the active form:

Sub ChangeCaption()
                          Screen.ActiveForm.Caption = Screen.ActiveForm.Caption & _
                                " - " & CurrentUser()
                      End Sub

Select all Open in new window

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.
Chap9Ex.accdb