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