The Vba

Organize with Office 365 Course

Organize With Office 365

Get Instant Access

If you've written programs in Visual Basic before, the VBA integrated development environment (IDE) should feel very familiar to you. If not, don't worry—the VBA IDE is user friendly and easy to learn. For ease of use, I refer to the VBA integrated development environment as the Visual Basic Editor, or VBE, from now on.

The VBE contains a suite of windows, toolbars, and menu items that provide support for text editing, debugging, file management, and help. Two common ways for accessing the VBE is with forms and code modules.

After adding a form to your database, make sure your form is highlighted (selected) in Design view and then select View, Code. If your Code menu item is not selectable, make sure you've created and selected (highlighted) a form first.

T«*»c,t easy shortcut to opening the VBE and alternating between Access and the

VBE is by simultaneously pressing Alt+F II.

After selecting the Code menu item, the VBA IDE should open up in a separate window similar to the one shown in Figure 2.1.

VBE Menu VBE Toolbar

Project Explorer

VBE Menu VBE Toolbar

Project Explorer

Task Scheduler Microsoft Access Macro
Opening the Visual Basic Editor or VBE for the first time.

The first time you open the VBE, certain windows such as Project Explorer and Properties windows may not be visible, but can be accessed from the View menu.

There are a few VBE components you should familiarize yourself with right away. Each is described here and seen in Figure 2.1.

• Toolbars: Toolbars contain shortcuts to many common functions used throughout your VBA development, such as saving, inserting modules, and running your program code. Additional Toolbars can be added from the View menu item.

• Menus: Menus in the VBE provide you with many development features, such as file management, editing, debugging, and help.

• Project Explorer window: The Project Explorer window provides you with a bird's-eye view of all files and components that build your Access VBA programming environment. Notice in Figure 2.1 that my form's name (Form_Form1) appears under the Microsoft Office Access Class Objects heading. If I had multiple forms in my database, there would be multiple form names in this folder. Remember, Microsoft Access stores all components including forms, queries, reports, modules in single .mdb file.

• Properties window: The Properties window shows all available properties for the object selected in the list box above. Most importantly, the Properties window allows you to change the values of an object's property during design-time development.

• Code window: The Code window is where you enter your VBA code and find procedures and event procedures for objects using the two list boxes at the top of the Code window.

If you haven't done so yet, explore each of the previously mentioned components and windows so that you are comfortable navigating the VBE environment.

Introduction to Event Procedures

Procedures are simply containers for VBA code. Access VBA contains four types of procedures:

• Subprocedures

• Function procedures

• Property procedures

• Event procedures

Each type of procedure is designed to accomplish specific tasks. For example, event procedures are designed to catch and respond to user initiated events such as a mouse click on a command button or system initiated event such as a form loading. In this section I concentrate an event procedure, as they are the foundation for an event-driven language such as VBA. In subsequent chapters, you learn about other types of procedures in detail.

As mentioned, objects such as the Form object contain methods and properties. They also contain specialized events that are automatically provided after the object has been added to your database. VBA takes care of naming your object's events for you. Their naming convention follows.


For example, a form added to your Access database called Form1 has a number of events, including the following.

Private Sub Form_Load() End Sub

Private Sub Form_Unload(Cancel As Integer)

End Sub

Notice the naming convention used for each event procedure: object name followed by the event name with an underscore in between. The objects and their events in Figure 2.2 are accessed from the VBE Code window.

Select an object

Accessing an object and its associated events in the VBE.

Select an object

Accessing an object and its associated events in the VBE.

The leftmost list box in the Code window identifies available objects. The rightmost list box contains all available events for the object selected in the left list box. Each time you select an event, VBA creates the event shell for you automatically. This saves you from having to manually type each event's beginning and ending procedure statements.

Each procedure in the VBE code window is separated by a horizontal line.

Empty event procedures serve no purpose until you write code in them using VBA statements.

Introduction to VBA Statements

VBA statements are comprised of variables, keywords, operators, and expressions that build a complete instruction to the computer. Every VBA statement falls into one of three categories:

• Declaration statements: Creates variables, data types, and procedures.

• Assignment statements: Assigns data or values to variables or properties.

• Executable statements: Initiates an action such as a method or function.

Most VBA statements fit onto one line, but sometime it is appropriate to continue a VBA statement onto a second or more lines for readability. To split a single VBA statement into multiple lines, VBA programmers use the concatenation character (&) and the line continuation character (_) separated by a space. To demonstrate, the following assignment statement uses the concatenation and continuation characters to extend a statement across two lines.

Private Sub Label3_Click()

Label3.Caption = "This is a single VBA assignment " & _ " statement split onto two lines."

End Sub

T«*»c,t ^he term concatenation means to glue or put one or more items together.

One of the best ways to provide understandable VBA statements is with comments. Comments provide you and other programmers a brief description of how and why your program code does something. In Access VBA, comments are created by placing a single quote ('), sometimes called a tick mark, to the left side of a statement. Comments are also created by placing the keyword REM (short for remark) at the left side of a statement. The following statements demonstrate both ways of creating VBA comments.

' This is a VBA comment using the single quote character.

REM This is a VBA comment using the REM keyword.

When a computer encounters a comment, it is ignored and not processed as a VBA statement.

Accessing Objects and Their Properties

Besides the Properties window, Microsoft Access provides a number of ways for accessing objects and their properties. Each way provides a level of intricacy and detail while providing specific performance characteristics. In its simplest form, programmers can directly call the name of an object (such as the Form object) or the name of a control (such as a command button). This is only applicable when accessing objects and controls that belong to the current scope of a code module. For example, the next VBA assignment statement updates the form's Caption property during the form's Load event.

Private Sub Form_Load()

Form.Caption = "Chapter 1" End Sub

In addition to forms, controls belonging to the current form and scope can be referenced by simply calling their name.

Private Sub Form_Load()

IblSalary.Caption = "Enter Salary" txtSalary.Value = "50000.00" cmdIncrease.Caption = "Increase Salary" End Sub

There are times, however, when you need to go beyond the current scope and access forms and controls that do not belong to the current object. There are a number of other reasons for being more specific about what controls you are referencing, including performance considerations and advanced control access techniques such as enumerating. To accomplish these goals, I show you how to access forms and controls using common VBA techniques with the Me keyword prefix and collections such as the Forms collection.

The Forms Collection

Properties of the Form object can be accessed in the VBE code window by simply supplying the form's Access class name.

Form_Form1.Caption = "updating the form's caption property"

Notice the naming convention used in the keyword Form_Form1. When an Access form in created and saved, Microsoft Access refers to it in the VBE as a Microsoft Office Access Class Object with the name Form representing the standard object name with a trailing underscore (_) followed by the individual form's name. Moreover, you can use the form's Access class name to not only access its own properties, but controls contained on the form. For example, the following VBA assignment statement uses the Access form class name to modify a label's caption property.

Form_Form1.Label1.Caption = "update the label's caption property"

If your form name contains spaces, you must surround the Access form class name using brackets.

[Form_Light Switch].Label1.Caption = "Light Switch"

This approach is convenient when working with small VBA projects. At times, however, you want to use a more advanced feature (such as the Forms collection) when working with multiple forms or with multiple controls on a form. Access provides the Forms collection for specifying which form's Caption property you are referencing.

The Forms collection contains all open forms in your Access database. To access individual forms in the Forms collection, simply supply the Forms collection an index or form name as seen in the next statements.

' Using an index to refer to a form in the collection. Forms(0).Caption = "Chapter 1"

' Using a form name to reference a form in the collection. Forms("Form1").Caption = "Chapter 1"

Because form indexes can change, it is considered safer to use the form name when accessing forms in the Forms collection.

Notice when passing the name of the form to the Forms collection, you must surround the form name in double quotes. If the form's name contains one or more spaces, you must use brackets ([]) to surround the name. After specifying a form in the Forms collection, you can use the dot operator to reference the individual form's properties, such as Caption.

The Me Keyword

To make things more interesting, Access provides the Me keyword, which refers to the current object or form within the scope of the VBE code module. More specifically, I can use the Me keyword in place of the Access form class name to access the current form's properties, methods, and controls.

Me.Caption = "updating the form's caption property" Me.lblSalary.Caption = "updating the label's caption property"

The Me keyword provides a self-documenting feature for VBA programmers in that it explicitly tells the reader what object, property, or form you are referring to.

In addition to the dot operator (.), Microsoft VBA provides the exclamation point (!) identifier for identifying what type of item or object immediately follows.

Me!lblSalary.Caption = "updating the label's caption property"

VBA supports two operators, the dot and exclamation mark, for accessing object properties and collection items. Because the dot and exclamation mark operators can often be interchanged, it can be confusing to remember which serves what purpose and when to use what. As a general rule of thumb, use the exclamation mark operator prior to accessing an item in a collection, and use the dot operator when referencing a property of a form or control. To keep things simple however, I use the dot operator to reference both items in collections and properties of forms and controls.

Assignment Statements

You can assign data to object properties, such as the form's Caption property, using an assignment operator in a VBA assignment statement. The assignment operator is really a fancy term for the equals (=) sign. However, it's really more important, as you soon see. To demonstrate, evaluate the next lines ofVBA code, which assign the text "Ouch!" to the Caption property of the Forml control.

Form.Caption = "Chapter 1" Or

Forms("Form1").Caption = "Ouch!" Or

Forms(0).Caption = "Chapter 1" Or

Form_Form1.Caption = "Ouch!"

A core concept in most programming languages is to understand the difference between data assignment and equality testing. This is especially important in programming languages such as VBA, which use the same operator.

Specifically, the following assignment statement reads, "The Caption property takes the literal value Ouch!" or "The Caption property gets the literal value Ouch!"

Me.Caption = "Ouch!"

Either way, the equals sign in an assignment statement is not testing for equality. In other words, you never want to read the previous assignment as "the Caption property equals Ouch!"

In the next chapter, I discuss how the equals sign can be used in testing for equality.

Command and Label Objects

I now show you how to put your knowledge of event procedures, VBA statements, objects, and their properties to work by building two small programs with VBA.

Let's start by building a program that allows a user to turn off and on a light switch. Begin by adding a new form to an Access database and naming it Light Switch. Next, add one label control to the form and assign the following property values to it:

• Name:lblCaption

Now I add three image controls to the form, but only one of them is visible during the form's runtime. (You see why shortly.) Add the following property values to the image controls:

• Picture: LIGHTON.ICO (Image located on accompanying CD)

• Picture: LIGHTON.ICO (Image located on accompanying CD)

• Picture: LIGHTOFF.ICO (Image located on accompanying CD)

Now add two command buttons to the form, which allows the user to turn off and on the light switch. Do not use the wizard while adding these command buttons.

You can turn off the control wizards (command button control wizard) by clicking the Control Wizards button on the Access Toolbar seen in Figure 2.3.

Turn off and on Control Wizards

The completed

Light Switch form in Design

Turn off and on Control Wizards

Access Vba Toolbar

The completed

Light Switch form in Design

When a graphic's path and file name is assigned to an Image control's Picture property, Microsoft Access does not include the image as part of its .mdb file. To use the light switch program located in this book's accompanying CD ROM, you must first change in Design time the Picture property's value to a location on your PC. This applies to all programs on the accompanying CD-ROM that have references to images.

A depiction of my completed form in Design view is revealed in Figure 2.3. Sample code from the Light Switch form is shown next.

Private Sub cmdOff_Click()

Me.lblCaption.Caption = "Light is off" Me.imgMain.Picture = Me.imgOff.Picture End Sub

Private Sub cmdOn_Click()

Me.lblCaption.Caption = "Light is on" Me.imgMain.Picture = Me.imgOn.Picture End Sub

I use only one image control (imgMain) to display one or the other light bulb image. This is why I set the other two image control's Visible property to No. The final output of my Light Switch form in runtime mode is seen in Figure 2.4.

The completed

Light Switch form in Design view.

C Microsoft Access - [Light Swii


¡^¿j File Edit View Insert Format Records Tools Window Help - S x

Was this article helpful?

0 0

Post a comment