Chapter 10: VBA Programming Fundamentals
387
filter. You might find yourself saying, “I’m tired of typing this formula into.. .” or “Doggone it, I
typed the wrong formula in this... .”
For situations such as these, you need the horsepower of a high-level programming language such
as VBA. VBA is a modern, structured programming language offering many of the programming
structures available in most programming languages. VBA is extensible (capable of calling Windows
API routines) and can interact through ActiveX Data Objects (ADO), through Data Access Objects
(DAO), and with any Access or VBA data type.
Getting started with VBA programming in Access requires an understanding of its event-driven
environment.
Events and event procedures
In Access, unlike old-fashioned programming environments, the user controls the actions and flow
of the application. The user determines what to do and when to do it, such as changing informa-
tion in a field or clicking a command button. The user determines the flow of action and, through
events, the application determines what action to take or ignore.
In contrast, procedural programming languages require that the programmer determine the flow of
actions that the user must follow. In fact, the programmer must accommodate all possibilities of
user intervention — for example, keystrokes a user might enter in error — and must determine
what actions to take in response to the user.
Using macros and event procedures, you implement the responses to these actions. Access pro-
vides event properties for each control you place on a form. By attaching a VBA procedure to a con-
trol’s event property, you don’t have to worry about the order of actions a user might take on a
particular form.
In an event-driven environment such as Access, the objects (forms, reports, and controls) respond
to events. Basically, an event procedure is VBA code that executes when an event (such as a button
click) occurs. The code is directly attached to the form or report containing the event being pro-
cessed. An Exit command button, for example, closes the form when the user clicks the button.
Clicking the command button triggers its Click event. The event procedure is the VBA code
attached to the Click event. The event procedure automatically runs every time the user clicks
the command button.
There are two types of procedures: subprocedures (often called subs) and functions.
Sub and function procedures are grouped and stored in modules. The Modules object button in the
Navigation Pane contains the common global, or standard, modules that any form or report can
access. You could store all your procedures in a single module, but that wouldn’t be a good idea.
You’ll probably want to group related procedures into separate modules, categorizing them by the
nature of the operations they perform. For example, an Update module might include procedures
for adding and deleting records from a table.