Working with Objects - Part 2

Published on
16,283 Points
8 Endorsements
Last Modified:
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:

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

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

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

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

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

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

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")
Set rst = Nothing

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

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

Open in new window

Contrast this with the following code:

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

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

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

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

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

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

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

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:

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

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.

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Join & Write a Comment

With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month