The Concept of Objects

Even someone who had only a peripheral relationship with computers would probably hear one word over and over again—that word is “objects.” There are many definitions for “object.” In my definition, I like to say that an object is a prebuilt component that can be plugged into an existing application.

Whenever I teach OOP courses, I always use the analogy of a small object that can format numbers to the currency format. Now, for example, let’s assume that it takes 100 lines of code for that object to achieve the currency format. Wouldn’t it be time consuming to have to reenter the 100 lines of code each time you want to format a number to currency? However, you can just plug in the object, feed the number into one end, and get the reformatted number out the other end. This makes the whole job easier because you don’t need to program and debug.

Access is built on the concept of objects. As discussed in Chapter 1, components like tables, reports, and forms are all called objects in Access. As a matter of fact, these objects are part of a larger hierarchy that includes the Application object. The objects associated with your application are part of the CurrentProject object. That is why, when using VBA, you might call a report as follows:

Application.CurrentProject.Report("myReport")

You might want to read this from right to left. You are calling the report object “myReport.” That report object is part of the CurrentProject object which, in turn, is part of the Application object. In this light, it is easy to see the hierarchy. You use a dot (period) to separate members of the hierarchy.

All objects can have two basic components: attributes and methods.The attributes of an object are, essentially, its fields. For instance, the object may have an attribute to hold the last name and another to hold the first name. If the value of any of those attributes changes (such as a different last name), we say the state of the object has changed.

Note

As we progress through the book, the words attribute and properties are interchangeable.

Using the example of an Access form object, some of the attributes might be the sort order, the background color of the form, or the tab stop order. As you will see, VBA has code to set an object’s attributes on-the-fly.

The methods are the actions the object can perform. For example, if you had an object called BankAccount, two possible methods, or behaviors, could be debit and credit. Again using the form object as an example, some of the methods might be to move to a record, save a record, delete a record, and filter records.

Many objects contain collections of other objects. As an example, a command bar object will usually have button objects on it. It could also contain combo box objects and pop-up objects.

VBA has the ability to interact with Access to help create and edit objects. As an example:

Sub createMenu()   Dim myBar As CommandBar   Set myBar = CommandBars.Add("My Command Bar") End Sub

Note

This code may not run in your version of Access without some setup that we have not discussed yet.

In the preceding code, we created a sub procedure to create a command bar object called myBar. It will be listed in the View | Toolbars menu of VBA as My Command Bar. If you were to run this sub, and then go to View | Toolbars in the Database window, you would see something like this:

Let’s take a brief look at the three main libraries of objects that we will be working with: ADO, VBA, and Access. For the time being, we will only look at one or two essential objects in each so that we can move forward in this book. Of course, as we progress, you will be learning many more.

ADO Objects

Recall that I defined objects earlier as prebuilt programs that can be plugged in as needed. VBA, by itself, has limited interaction with Access. Remember, VBA is not really part of the Access program. Instead, it is a separate program bundled with the Microsoft Office environment to help extend the capabilities of Office.

In order for VBA to interact with the data, a lot of code is needed to make the connection. This could result in extraordinarily long and complicated programs. To address this issue, versions of Access before Access 2000 used a library of objects called Data Access Objects (DAO) to help VBA make a connection with a database’s data. This saved a lot of code writing.

It was decided that DAO did not meet the increasing demands made upon databases, and to address that issue, Access 2000 introduced a new library of objects called ActiveX Data Objects (ADO). We will be discussing this in greater detail in Chapter 15, but since we need to reference it from time to time before then, we’ll turn it on now. If you do not have it open already, open the VBA Editor by selecting Tools | Macro | Visual Basic Editor.

Once in it, select Tools | References. You should see the dialog box shown in Figure 5-1.

Figure 5-1: References dialog box

Scroll down and click on the object libraries as shown in Figure 5-1. If your version of ADO does not go up to version 2.7, as shown in the figure, don’t worry about it. Just click on the highest number version that you have. Do the same for the Microsoft Office 11.0 Object Library.

Select OK, and those libraries are now available to VBA to help save a lot of coding. Using the command bar example in the previous section, VBA simply had to say, “create a command bar, call it myBar, and give it the title My Command Bar.” The objects in the libraries knew what to do from there.

Don’t worry if you don’t understand all the details for now. This is just an overview, and we will cover them as we go along.

VBA Objects

So far, we have taken a brief look at ADO, but VBA has a number of objects built into it. As we progress through this book, you will see many of these objects, but again, we are just going to take a cursory look for the time being.

The VBA library contains an object called Interaction. Interaction has two of the most popular methods used: MsgBox and Inputbox. The following code shows a simple example using the MsgBox method.

Note

For the time being, I will use the words "method" and "function" interchangeably. As we progress, we will refine our use of these terms.

Sub message()   MsgBox "Welcome to VBA programming!" End Sub

This results in the following Message Box:

Access Objects

Another important object is the DoCmd object. This is not part of the VBA library, but it is connected with the Access library. We will use this object with many of the objects in other libraries. For example:

DoCmd.GoToRecord , "", acNext 

The DoCmd object carries out actions in Access. What the code says is that the DoCmd object has a method called GoToRecord. You distinguish between the object and its method with a dot. The subsequent commas separate the arguments needed for the method to do its job.