Objects, properties, methods, and events are at the heart of all programming that you do within Microsoft Access. Without a strong foundation in objects, properties, methods, and events, and how you should use them, your efforts at Access and Visual Basic for Applications (VBA) programming will fail. This article introduces you to Access’s object model. You will not only become familiar with Access’s objects, properties, methods, and events, and how to manipulate them, but you will also learn concepts that will carry throughout your Access and VBA programming career.
Understanding Objects, Properties, Events, and Methods
Many people, especially those accustomed to a procedural language, don’t understand the concept of objects, properties, methods, and events. As mentioned earlier, you need a thorough knowledge of Access’s objects, their properties, the methods associated with them, and the events that each object can respond to if you want to be a productive and successful Access programmer.
What Exactly Are Objects?
Objects are all the things that make up your database. They include tables, queries, forms, reports, macros, and modules, as well as the components of those objects. For example, a Table object contains Field and Index objects. A Form object contains various controls (text boxes, combo boxes, list boxes, and so on). Each object in the database has specific properties that determine its appearance or behavior. Each object also has specific methods, which are actions that it can take.
What Exactly Are Properties?
A property is an attribute of an object, and each object has many properties. Often, different types of objects share the same properties; at other times, an object’s properties are specific to that particular object. Forms, combo boxes, and text boxes all have Width properties, for example, but a form has a RecordSource property that the combo box and text box don’t have.
You can set most properties at design time and modify them at runtime; however, you can’t modify some properties at runtime, and you can’t access others at design time (you can only modify them at runtime). Access’s built-in Help for each property tells you one of the following:
You can set this property in the object’s property sheet, a macro, or Visual Basic.
You can set this property only in Design view.
You can access this property by using Visual Basic or a macro.
Each of these descriptions indicates when you can modify the property.
As a developer, you set the values of many objects’ properties at design time; the ones you set at design time are the starting values at runtime. Much of the VBA code you write modifies the values of these properties at runtime in response to different situations. For example, suppose that a text box has a Visible property. Let’s take a look at an example. If a client is paying for something by cash, you might not want the text box for the credit card number to be visible. If he’s paying by credit card, you might want to set the Visible property of the text box with the credit card number to True. This is just one of the many things you can do to modify the value of an object’s property at runtime in response to an event or action that has occurred.
You might wonder how you can determine all the properties associated with a particular object (both those that can be modified at design time and those that can be modified at runtime). Of course, to view the properties that can be set at design time, you can select the object and then view its property sheet. Viewing all the properties available in Access 2010 is actually quite easy to do; you can use the Object Browser to quickly and easily view all properties associated with an object. The Object Browser is covered later in this article.
What Exactly Are Events?
Windows is an event-driven operating system; in other words, the operating system responds to many events that are triggered by actions that the user takes and by the operating system itself. Access exposes many of these events through its Object Model. An event in an Access application is something your application can respond to. Events include mouse movements, changes to data, a form opening, a record being added, and much more. Users initiate events, as does your application code. It’s up to you to determine what happens in response to the events that are occurring. You respond to events by using macros or VBA code. Each Access object responds to different events. If you want to find out all the events associated with a particular object, take the following steps:
1. Select the object (for example, a text box).
2. Open the Property Sheet.
3. Click the Event tab, as shown in Figure 1.
4. Scroll through the available list of events.
What Exactly Are Methods?
Methods are actions that an object takes on itself. As with properties and events, different objects have different methods associated with them. A method is like a function or subroutine, except that it’s specific to the object it applies to. For example, a form has a GoToPage method that doesn’t apply to a text box or most other objects.
Using the Object Browser to Learn About Access’s Objects
The Object Browser is a powerful tool that can help you learn about and work with the objects that are part of both Access 2010 and the Microsoft Windows environment. The Object Browser displays information about Microsoft Access and other objects and can help you with coding by showing you all the properties and methods associated with a particular object.
Access objects are complex; they have many properties and methods. The Object Browser helps you to understand and use objects, properties, and methods by doing the following:
Displaying the types of objects available
Allowing you to quickly navigate between application procedures
Displaying the properties and methods associated with a particular object
Finding and pasting code into your application
How to Use the Object Browser
You can easily invoke the Object Browser from the Visual Basic Editor. You can click the Object Browser button on the toolbar, press F2, or choose View, Object Browser. The window shown in Figure 2 appears.
The Object Browser displays two levels of information about the selected library or database. With a database open, select the database name from the Project/Library drop-down (the top drop-down), and your screen will look similar to Figure 3. The Classes list box displays all modules, including Form and Report modules, in the database. The Members Of list box displays any procedures that have been defined in the selected module. Notice the basUtils module, which is part of the open database. Looking at the list box on the right, you can see the procedures (subroutines and functions) included in the basUtils module. You can click to select each Form and Report module in the list box on the left and view the associated methods and properties in the list box on the right.
You can use the Project/Library drop-down list to select a different object library (provided you have set a reference to it). The Classes list box displays the types of objects available in the selected library or database. Just as with the Access object library, the Members Of list box displays the methods, properties, and data elements defined for the selected object (see Figure 4). You can even add other libraries to the Library drop-down list by referring to other type libraries, such as Microsoft Excel.
Pasting Code Templates into a Procedure
After you have located the method or property you’re interested in, you have the option of pasting it into your application. With the method or property selected, simply click the Copy to Clipboard button in the Object Browser; then paste it in the appropriate module. If you want to get more information about a particular method or property, click the Help button in the Object Browser or press F1.
Referring to Objects
Access objects are categorized into collections, which are groupings of objects of the same type. The Forms collection, for example, is a grouping of all the open forms in a database. Each form has a Controls collection that includes all the controls on that form. Each control is an object, and you must refer to an object through the collection to which it belongs. For example, you refer to a form through the Forms collection. VBA offers three ways to refer to an object; if you want to refer to the frmProjects form, for example, you can choose from the following options:
Referring to the form as Forms(0) assumes that frmProjects was the first form opened. However, you need to understand that although Access assigns an element number as it loads each form, this element number changes as Access loads and unloads forms at runtime. For example, the third form that’s loaded can initially be referred to as element two, but if the second form is unloaded, that third form becomes element one. In other words, you can’t rely on the element number assigned to a form; that number is a moving target.
You must refer to a control on a form first through the Forms collection and then through the specific form. The reference looks like this:
In this example, Forms is the name of the collection, frmProjects is the name of the specific form, and txtClientID is the name of a control on the frmProjects form. If this code is found in the Code module of frmProjects, it could be rewritten like this:
Me refers to the current form or report. It’s generic because the code could be copied to any form having a txtClientID control, and it would still run properly. Referring to a control on a report is similar to referring to a control on a form. Here’s an example:
This example refers to the txtHoursBilled text box on the rptTimeSheet report, part of the Reports collection. After you know how to refer to an object, you’re ready to write code that modifies its properties and executes its methods.
Working with Properties and Methods
To modify an object’s properties and execute its methods, you must refer to the object and then supply an appropriate property or method, as shown in this example:
Forms.frmHello.cmdHello.Visible = False
This line of code refers to the Visible property of cmdHello, found in the frmHello form, which is in the Forms collection. Notice that you must identify the object name frmHello as being associated with the Forms collection. If you want to change the Caption property of frmHello to say “Hello World”, you would use the following code:
Forms.frmHello.Caption = “Hello World”
Telling the Difference Between Properties and Methods
You might be confused about whether you’re looking at an object’s property or method, but there are a couple of quick ways to tell. You will always use a property in some type of an expression. For example, you might be setting a property equal to some value:
Forms.frmClients.txtAddress.Visible = False
Here, you’re changing the Visible property of the txtAddress text box on the frmClients form from True to False. You also might retrieve the value of a property and place it in a variable:
strFirstName = Forms.frmClients.txtFirstName.Value
You also might use the value of a property in an expression, as in the following example:
The pattern here is that you will always use a property somewhere in an expression. You can set it equal to something, or something can be set equal to its value, or it’s otherwise used in an expression.
A method, however, is an action an object takes on itself. The syntax for a method is Object.Method. A method isn’t set equal to something; however, you frequently create an object variable and then set it by invoking a method. A method looks like this:
In this example, the text box called txtHelloWorld executes its SetFocus method.
A method that returns an object variable looks like this:
Dim cbr As CommandBar
Set cbr = CommandBars.Add(“MyNewCommandBar”)
In this example, the CommandBars collection’s Add method is used to set the value of the CommandBar object variable named cbr.
Using a Bang Versus a Period
Many people are confused about when to use a bang (!) and when to use a period. You can use a bang whenever you’re separating an object from its collection, as shown in these two examples:
In the first example, frmClients is part of the Forms collection. In the second example, txtClientID is part of the Controls collection of the frmClients form.
In most cases, you can also use a period to separate an object from its collection. The reason is that the expression Me!txtClientID is actually a shortcut to the complete reference Me.Controls!txtClientID. Because Controls is the default collection for a form, you can omit Controls from the statement. You can abbreviate the expression to Me.txtClientID. The advantage of using the dot over the bang is that the dot provides you with IntelliSense. To test this, create a form and add a control called txtFirstName. Go to the code behind the form and try typing Me!. Notice that IntelliSense is not invoked. Next type Me. and watch as IntelliSense is invoked. IntelliSense facilitates the development process by providing a list box containing valid properties, methods, constants, and so on, as appropriate.
IntelliSense is a tool that helps you when writing programming code. It provides you with auto-completion when writing your programming code.
In addition to separating an object from its collection, the period is also used to separate an object from a property or method. The code looks like this:
Forms.frmClients.RecordSource = “tblClients”
Forms.frmClients.txtClientID.Visible = False
The first example sets the RecordSource property of frmClients to tblClients, and the second example sets the Visible property of the txtClientID on the frmClients form to False.
Each object has a default property, and if you’re working with an object’s default property, you don’t have to explicitly refer to it in code. Take a look at the following two code samples:
Forms.frmHello.txtHello.Value = “Hello World”
Forms.frmHello.txtHello = “Hello World”
The Value property is the default property of a text box, so you don’t need to explicitly refer to it in code. However, I prefer to explicitly state the property; it is a practice that contributes to the code’s readability and keeps novice Access programmers who work with my code from having to guess which property I’m changing.
You just learned all about objects, properties, methods, and events. You will use the techniques that you learned throughout your VBA programming career. This article is Part 1 or a three-part series. The second article will show you how to work with object variables, how to work with collections of objects, and how to pass objects to subroutines and functions. In the final article you will learn how to take advantage of Access’s object model. The third article will also provide you with several practical examples.
This article was excerpted from “Alison Balter’s Mastering Access 2007 Development” published by SAMS.