Transcript Chapter 4
1
Chapter 4
The Fundamentals of
VBA, Macros, and
Command Bars
4
Chapter Objectives
• Specify the fundamental structure of a VBA
procedure
• Open forms and reports with VBA, macros,
and command bars
• Create statements that declare variables and
constants
• String together WHERE conditions used in
the process of opening forms and reports
4
Chapter Objectives
• Write code that uses assignment and
If…Then…Else statements
• Use the VBA debugging facility
• Create custom menus and toolbars
4
Introduction to VBA
• Visual Basic for Applications (VBA)
Programming language used in Access to
develop complex applications
Create procedures that open forms and reports
in response to a user’s actions
Calculate complex equations and validate field
values before storing records
4
Introduction to VBA
• Most programming languages utilize
procedures that are similar to recipes
Figure 4-1
Recipe for
strawberry banana
cooler
4
Introduction to VBA
• Dim statements (or dimension statements)
Declare variables
• Variables
Named locations in memory that are used to
store data of the types declared in the Dim
statement
4
Functions and Sub
Procedures
• Event procedure
Code inside an event procedure executes when
the user or system performs an action
Figure 4-2
cmdFilter_C
lick() event
procedure
4
Functions and Sub
Procedures
• Other types of procedures include functions
and general sub procedures
Not tied to one particular event; programmer
specifies when the procedure executes
Primary distinction between them is that a
function usually returns a value and a sub
procedure usually does not return a value
• Built-in functions
Functions provided by Access
4
OpenForm and OpenReport
• OpenForm and OpenReport
Instructions that are included in a procedure and that
cause Access to open forms and reports, respectively
• In VBA, OpenForm, OpenReport, and almost all other
instructions are called methods
• Named arguments
Advantage of this approach is that the string of commas
is not necessary and a specific order is not required
4
OpenForm and OpenReport
• Continuation character
Space followed by an underscore (_)
Used to indicate that the instruction continues
on the next line
Commonly used when an entire line cannot be
seen in the VBA window
• Intrinsic constant
Keyword that represents some particular value
(usually an integer)
4
Controls
• Value
Usually a number or a string
Current value of a text box is displayed on a
form, report, or data access page
Current value of a combo box refers to the
value of the bound column
• Bang notation
Commonly used notation
4
Variables
• Variables
Named locations in the computer’s memory
Key ingredients in a VBA procedure because
they store values that are needed to complete
the steps in the procedure
Variables are usually declared before they are
used
Variable declarations can become complex
4
Assignment Statements
• Variables are useful only when they contain values
• Assignment statements
Instructions that tell Access to place a value in a
particular location
• Once a variable has a value, it can be used in any
expression or placed in any argument that requires
a value that is consistent with the variable’s data
type
4
Basic wherecondition
Arguments
• wherecondition arguments
Used to add additional criteria to a record
source before the data are displayed or returned
Figure 4-3
Selecting
Chemical
Processing
from the
combo box
4
Basic wherecondition
Arguments
Figure 4-4
Query
equivalent to
adding a
wherecondition
to the record
source of
frmCompanies
4
whereconditions
That Do More
• If data in a form must obey more than one new
criterion, the AND operator is strung into the
wherecondition
• If data must obey at least one criterion, the OR
operator is used
• Domain aggregate functions
Return values or statistical information from the
records in a table or query
4
Conditional Execution
• Often blocks of code need to be executed
under some some conditions but not
executed under others
• IF…Then…Else statement in VBA
Supports conditional execution
• Comments
Used to document the code
Not executed as a part of the steps necessary to
produce the procedure’s goals
4
The Visual Basic Editor
• Visual Basic Editor (VBE)
Allows you to quickly access modules, procedures,
possible intrinsic constants, and other objects that could
be used in a VBA procedure
Has many tools that help the programmer create
syntactically correct statements
Contains debugging tools that help find and fix the
errors that will inevitably occur in the process of
developing the application
4
Figure 4-6
Visual
Basic Editor
The Visual Basic Editor
4
The Visual Basic Editor
• Object Browser
Additional source of help
Displays all objects recognized by VBA,
Access, the database engine, and any other
application with which you are working
4
Figure 4-7
Object
Browser
The Visual Basic Editor
4
Debugging Tools
• Compilation errors
Occur before the code is run
• Runtime errors
Cause a running procedure to abort
• Logic errors
Do not abort execution, but rather produce
incorrect results
4
Immediate Window
• Immediate window
Holds commands that will be executed as soon
as the programmer presses the Enter key
Can also display values that are in a form, but
that are not referenced in the aborted procedure
4
Locals Window
• Locals window
Once a procedure aborts, used to display
information about all variables, forms, reports,
or controls used in the aborted procedure
Can also be displayed through the Debug
toolbar
4
Breakpoint
• The line of code that Access believes to have
caused the error is not always the line of code that
actually caused the error
• Frequently, it is useful to step through code, lineby-line, until the error occurs
Beneficial when certain lines of code are executing (or
not executing) unexpectedly
• Setting a breakpoint suspends the execution of a
program
4
Debug Toolbar
• Buttons on the Debug toolbar:
Step Into causes the highlighted line to execute
and moves the insertion point to the next line
that should execute, even if it is in another
procedure
Step Over causes the highlighted line to execute
If this line invokes another procedure, that
procedure executes in its entirety, and then
processing is suspended at the next executable line
4
Debug Toolbar
• Buttons on the Debug toolbar (continued):
Step Out causes the entire procedure to finish
executing
Control is suspended again in the procedure that
invoked the currently highlighted procedure
Continue causes the entire procedure to run
until it is finished, aborts, or encounters another
breakpoint
4
Debug Toolbar
• Buttons on the Debug toolbar (continued):
Break causes a running procedure to stop
Reset causes the program, even in its suspended
state, to stop running
4
Macros
• Macros are a sequence of actions that automates a
task
Figure 4-9
Macro
equivalent to
cmdFilter_C
lick()
4
Macro Groups
• A macro groups refers to a macro that contains
other macros
Figure 4-10
mcrExits
4
Condition Column
• Condition column
Similar to a VBA If…Then…Else statement
• When the expression in the Condition
column evaluates to True:
The action in the Action column executes
• The expression placed in the Condition
column is identical to the If…Then…Else
condition used in VBA
4
Action Column
• The action selected from the combo box
located in the Action column tells Access to
perform some task
• As with VBA methods, most actions require
the use of an action argument
4
Command Bars
• Command Bars
Collective name given to menu bars, toolbars,
and shortcut menus
Common in many Windows-based applications
Controls can open forms, print reports, move
the cursor to a new record, delete records, and
perform many other operations
4
Strategies and Tools for Developing
Menu Bars and Toolbars
• Rules of good menu bar and toolbar design:
Adhere to industry norms
Develop consistent command bars
Determine common features for all menu bars
Use standard Access menu controls whenever
possible
4
Table 4-1
Properties
of menu
bar and
toolbar
controls
Strategies and Tools for Developing
Menu Bars and Toolbars
4
Wizards and the
Switchboard Manager
• Access wizards
Can help develop very simple procedures
automatically
• Switchboard Manager Wizard
Found under the Tools, Database Utilities menu
Creates a form with buttons that open forms,
reports, tables, queries, and macros
4
Chapter Summary
• Access supports both macros and VBA
• Although macros are easier to use,
experienced Access developers prefer to
work with VBA because it is a more capable
language
4
Chapter Summary
• VBA procedures and macros require a name,
objects to work with, and a set of instructions that
cause actions to occur
• The name of a VBA procedure is specified in a
sub or function statement
• Both macros and procedures can refer to values of
controls on a form or report
• VBA procedures can also declare variables that
can be used within the context of the procedure
4
Chapter Summary
• Many macros and VBA procedures use
OpenForm and OpenReport actions or
methods
• These two instructions use a number of
arguments
The wherecondition argument restricts data
displayed in the form or report to those records
that meet the specified criteria
4
Chapter Summary
• The Visual Basic Editor (VBE)
Facilitates the development of VBA procedures
Debugger allows the developer to create
breakpoints
Can watch execution of VBA code on line-byline basis
• An access developer must know how to
create custom command bars