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 proce-
dures 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 concen-
trate 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 con-
vention follows.

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

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

Object-Based Programming

The key to programming in VBA is using objects. Objects have properties that describe the
object and methods, which perform actions. For example, say I have an object called Person.
The Person object contains properties called HairColor, Weight, Height, and Age that describe
the object. The Person object also contains methods that describe an action the object can
perform such as Run, Walk, Sleep, and Eat. As you can see, understanding the concept of
objects is really quite simple!
Many Access VBA objects also contain data structures called collections. In a nutshell, collections
are groupings of objects, which you are introduced to in this chapter.
Access VBA supports many objects such as the Form object, which is simply a window or dia-
log box. The Form object contains many properties such as Caption, Moveable, and Visible.
Each of these properties describes the Form object and allows VBA programmers to set char-
acteristics of a user’s interface. Like the object Person, the Form object contains methods such
as Move and Refresh.
Many objects share common characteristics such as properties and methods. To demon-
strate, the Label object (which implements a label control) shares many of the Form proper-
ties such as Caption and Visible.
Properties and methods of objects are accessed using the dot operator (.) as demonstrated
in the next two VBA statements.
Label1.ForeColor = vbBlue
Label1.Caption = “Hello World”
Don’t worry about the details in the previous statements for now, but do realize that prop-
erties such as ForeColor and Caption belong to the Label1 object and they are accessed using
the dot operator. I discuss this in more detail in sections to come.

The Event-Driven Paradigm

The event-driven paradigm is a powerful programming model that allows pro-
grammers to build applications that respond to actions initiated by the user or
system. Access VBA includes a number of events that are categorized by the
objects they represent. VBA programmers write code in event procedures to
respond to user actions (such as clicking a command button) or system actions
(such as a form loading).
To demonstrate the event-driven model, consider a form, which has a corresponding Form
object that contains many events such as Click, Load, and MouseUp. As seen next, both Click
and MouseUp events are triggered by the user performing an action with the mouse on the
form.
Private Sub Form_Click()
‘write code in here to respond to the user clicking the form
End Sub
Private Sub Form_MouseUp(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
‘write code in here to respond to the user releasing a mouse button
End Sub

I discuss the details of these event procedures soon enough. For now, understand that
objects have related events, which can be triggered by users. You, the VBA programmer,
write code in these event procedures to respond to user actions. Moreover, events can be trig-
gered by the system or the program itself. For example, the Load event seen next is triggered
when a form’s Form object is first loaded into memory.
Private Sub Form_Load()
‘write code in here to respond to the form loading into memory
End Sub
If you’re new to event-driven programming, this may seem a bit awkward at first. I promise
you, however, it is really not that difficult. In fact, VBA does a great job of providing much
of the detail for you. By the end of this chapter, you will be writing your first Access VBA
event-driven programs with ease.

Introduction to Access VBA

Like many professional RDBMS (Relational Database Management Systems),
Microsoft Access comes with its own programming language called VBA.
VBA, or Visual Basic for Applications, is a subset of Microsoft’s popular
enterprise programming language Visual Basic. VBA follows the Visual Basic lan-
guage syntax and comes with many of its common features such as an integrated
development environment (IDE) and many common controls for building profes-
sional event-driven and data-driven applications.
Though VBA supports the look and feel of Visual Basic, it is not Visual Basic. A
main difference being that Visual Basic allows for creation of executable programs,
whereas VBA does not. Moreover, VBA for Access is specifically designed for
Microsoft Access. Meaning, it has knowledge of and support for the Microsoft
Access object model. The concept of an object model is different for each
Microsoft Office application. For example, both Microsoft Excel and Microsoft
Word support VBA, but each has its own object model.

Introduction to Access VBA

Like many professional RDBMS (Relational Database Management Systems),
Microsoft Access comes with its own programming language called VBA.
VBA, or Visual Basic for Applications, is a subset of Microsoft’s popular
enterprise programming language Visual Basic. VBA follows the Visual Basic lan-
guage syntax and comes with many of its common features such as an integrated
development environment (IDE) and many common controls for building profes-
sional event-driven and data-driven applications.
Though VBA supports the look and feel of Visual Basic, it is not Visual Basic. A
main difference being that Visual Basic allows for creation of executable programs,
whereas VBA does not. Moreover, VBA for Access is specifically designed for
Microsoft Access. Meaning, it has knowledge of and support for the Microsoft
Access object model. The concept of an object model is different for each
Microsoft Office application. For example, both Microsoft Excel and Microsoft
Word support VBA, but each has its own object model.

Migrating to Access 2003

Migrating to Access 2003
Depending on which previous Access version you were leveraging, migration woes range
from none to minor. In fact, migrating from Access 2002 is real easy. In most cases you simply
need to open your previously created Access 2002 database in Access 2003 once installed.
Your Data Access Pages and VBA code from Access 2002 should also open in Access 2003 with
no problems.
If you’re migrating from Access 2000, it is also easy to upgrade as Access 2003 supports the
same file format. Like previous versions of Access 2002 databases, Access 2003 should import
your Access 2000 databases with no problems, including your Access 2000 VBA code. How-
ever, you need to convert any Data Access Pages built in Access 2000 by simply opening the
Data Access Page in Design view with an Access 2002 or higher database. Microsoft Access
should then prompt you to convert the Data Access Page.
For versions older than 2000, such as Access 97, you must convert the Access files first.
Microsoft provides step-by-step procedures and toolkits for converting older Access files in
this situation.
For more information on converting older Access files, simply use the key phrase “convert-
ing an Access file” in the Microsoft Office online help in Access 2003 or on the web at
http://office.microsoft.com.
Your First Access Data

Relationships

As mentioned before, Access implements a relational database, allowing database developers

to link one or more tables using keys or relationships. To demonstrate, follow the next

sequence of steps to link two tables called Students and HomeWork_Results.

1. Create and save a new Access file and database.

2. Create two new tables called Students and HomeWork_Results.

3. The Studentstable should have the following fields: Student_Id (AutoNumber),

First_Name(Text), Last_Name(Text), Middle_Initial (Text), Gender(Text), and Age (Number).

4. Set the Student_Id in the Students table as the primary key by right-clicking the

Student_Id field in Design view and right-clicking the Primary Keymenu option.

5. The HomeWork_Resultstable should have the following fields: Homework_Id(Number),

Student_Id (Number), Homework_Completed(Yes/No), and Homework_Score(Number).

6. Set two fields as the primary key in the HomeWork_Resultstable by holding down your

Shift or Ctrl key and clicking both the Homework_Idand Student_Id fields.

7. While both fields are highlighted and still holding the Shift or Ctrl key, right-click in

the gray column to the left of one of the highlighted fields and select the Primary

Keymenu option. This is known as a multifield key !

8. Ensure both tables are saved and named then select Tools, Relationships from the

Access menu. An interim window is displayed from where you will add both tables to

the relationship window. After which, the relationship window displays your two

new tables. Note the primary keys in both tables are highlighted in black as seen in

Figure 1.10.

9. Drag and drop the Student_Id field from the Students table onto the Student_Id field

in the HomeWork_Resultstable. A new window appears, as depicted in Figure 1.11.

10. Click the Create New button and you have created a one-to-many relationship, which

means for every student in the Students table, there are many occurrences of that

student in the HomeWork_Resultstable.

The primary key in the Students table, Student_Id, has also now become a foreign key

in the HomeWork_Resultstable.

You must make other considerations about a relationship:

• Enforcereferential integrity, which means values entered into the foreign key must

match values in the primary key.

• Enforcecascading updates between one or more tables, which means related updates

from one table’s fields are cascaded to the other table or tables in the relationship.

• Enforcecascading deletes between the two tables. In short, this means any relevant

deletions from one table cascade in the other table(s).

• Both cascading updates and deletes help enforce referential integrity.

Figure 1.10

Viewing

relationships

between two

tables.

Figure 1.11

Editing a

relationship

between two

tables.

To better visualize the relationship, enter a few records into the Students and

HomeWork_Resultstables by double-clicking each table, one at a time, from the main

Access window. Remember, opening a table from the main Access window allows

you to manage field values directly!

11. After you’ve entered data in both tables, open the Students table again and you

should see a plus sign (+) to the left of each Student_Id.

Click the plus sign and you should see the related HomeWork_Resultsrecord for the

student, as depicted in Figure 1.12.

Figure 1.12

Viewing related

table information

after creating a

table relationship.