Transcript Document
1
Chapter 9
Writing, Testing,
and Debugging
Access Applications
9
Chapter Objectives
• Write VBA procedures that use a variety of
assignment statements
• Choose and write appropriate conditional
execution statements
• Create program loops
• Write disk- and file-related statements
9
Chapter Objectives
• Invoke error handlers and write errorhandling routines
• Test Access applications
• Debug Access applications using message
boxes and the debugging facilities
9
Operators
• Many VBA statements support
mathematical, string, and comparison
operations
• Integer division
Indicated by \
• Exponentiation
Indicated by the ^ operator
9
Operators
• Comparison operators
<, <=,>, >=, =, and <>
• And and Or
Logical operators
• Not
Negative operator
• And, Or, and Not have traditional precedence
relationships
Parentheses can be used to control the precedence
9
Operators
• String concatenation
Uniting of two or more string values
Can be performed using either the + or &
operator
+ operator produces a null result when either
operand is null
& operator treats a null operand as a zero-length
string
9
Operators
Figure 9-1
Constructing
an SQL
command by
using strings
in VBA
9
Comments
• Comments
Indicate the purpose of each section of code
• Rem statements
Used to include explanatory comments in a
program
Can be represented in code with a single quote
(‘)
9
Built-in Functions
• Built-in functions
Perform trigonometric calculations, financial
calculations, and common mathematical
calculations
Handle the conversion of data from one data
type to another
InputBox
Used to prompt a user for information
9
Assignment and With
Statements
• Assignment statements
Change the value of an object’s properties
Use them to update fields in recordsets and to
place values in VBA variables
• With statements
Allow you to abbreviate assignment statements
that reference object properties, because the
object does not need to be specified
9
Assignment and With
Statements
Table 9-1 Summary of assignment statements
9
Assignment and With
Statements
Table 9-1
Summary
of
assignment
statements
(continued)
9
Conditional Execution
Statements (If Statement)
• If statement
Can evaluate multiple conditions and thus
selectively execute multiple sections of code
Can also contain multiple ElseIf parts
When it does include such parts, conditions occur
for the initial If part and for each ElseIf part
9
Iterative (Looping)
Statements
• Iterative (or looping) statements
Cause blocks of statements to repeat
• VBA supports multiple looping statement
forms
The Do statement is a commonly used
“looping” statement
9
Table 9-2
Looping
statements
Iterative (Looping)
Statements
9
Unconditional Branching
Statements
• Unconditional branching statements
Cause the program to skip execution of code
that would have otherwise been executed
Created using line labels and numbers
• Exit Function
Exits a function
9
VBA Statements and Related
Functions that Perform Access Actions
• VBA provides many statements that may
not appear elsewhere which include
statements that:
Manipulate the Access environment
Manipulate files
Reference the Windows environment
Manipulate other Windows applications
9
Statements Related to the
Access Environment
• SendKeys
statements
are useful
when you
want to
trigger a
series of
events
Table 9-3
Statements
that perform
actions within
the Access
environment
9
Statements Related to the
Access Environment
Table 9-3 Statements that perform actions within the Access
environment (continued)
9
Windows Disk- and FileRelated Statements
• ChDrive
Changes the current drive
• ChDir
Changes the current directory
• CurDir[] function
Returns the current directory
9
Windows Disk- and FileRelated Statements
Table 9-4 Disk- and file-related statements
9
Windows Disk- and FileRelated Statements
Table 9-5
File I/O
statements
9
Determining and Setting the
Applications Operating Environment
• An application can:
Determine the path to an application by reading
the Windows Registry
Access information about the registry through
the GetSetting and GetAllSettings functions
9
Determining and Setting the
Applications Operating Environment
Table 9-6 Commonly used SysCmd specifications
9
Using SysCmd to Implement a
Progress Meter
• A Progress meter should be used whenever
an operation is expected to take a long time
acSysCmdInitMeter constant
Used to start the progress meter and length of
argument indicates the total time of the operation
acSysCmdRemoveMeter constant
Used when the operation is complete
acSysCmdUpdateMeter constant
Used to show progress at a given point in time
9
Compilation Errors
• Compilation errors
Due to syntax mistakes
• Compiling a procedure tells Access to
translate VBA code into a format that is
efficiently executed by the computer
• A procedure will not compile when one or
more lines of code violate the syntax rules
of VBA
9
Runtime Errors
• When a statement combination is invalid, errors
may occur when the procedure executes
Figure 9-2
Runtime
error
example
9
Logic Errors
• A logic error occurs when a procedure
executes successfully but produces an
incorrect result
• Common logic errors include:
Procedures associated with events that do not
execute
Procedures associated with events that execute
at an unplanned time
9
Logic Errors
• Common logic errors (continued):
Events execute in an unplanned sequence
Focus of a control, form, or report changes
earlier or later than a procedure expects
Value of a control changes earlier or later than
an executed procedure expects
Criteria string used by a method is incorrect
An expression that does not accommodate null
values o empty strings receives one
9
The On Error Statement
• On Error statement
Principal means for controlling error processing
in VBA
• When VBA encounters an On Error
statement, it enables an error handler
Error handler
A mechanism for transferring process control to
specific VBA statements when an error occurs
9
The Resume, Exit, and End
Statements
• An error-handling routine runs until it
encounters a Resume, Exit, or End
statement
Resume statement
Causes the system to continue processing
Exit statement
Causes the system to end the error-handling routine
End statement
The last statement in a procedure
9
Advanced Error Handling
• Err
Commonly used error-handling object
Belongs to VBA object library
Specializes in handling VBA runtime errors
• Err object properties and methods
including:
Description property
Contains the Access default description of the error
that just occurred
9
Advanced Error Handling
• Err object properties and methods including
(continued):
Number property
Contains the Access internal error number for the
most recent error
Clear method
Automatically sets the Number property to zero
9
Handling Logic Errors by
Using MsgBox
• One technique for understanding the
underlying logic of your code is to include
MsgBox statements in the code
Display the value of variables as the code
executes
When values are displayed, you can see what is
actually happening in the procedure and
compare that to what you think should be
happening
9
Using VBA’s Debug Facility to Find
and Solve Complex Logic Errors
• Simplest way to interact with the Debug
facility is to use the Print method in
conjunction with the Debug object
• Debug object
Allows a program to communicate with the
Debug facility
Supports Assert and Print methods
9
Using VBA’s Debug Facility to Find
and Solve Complex Logic Errors
Figure 9-3
The Immediate
window of the
Debug facility
9
Breakpoints, Step into, and
Step Over
• Breakpoint
Suspends the execution of a procedure
• Step Into and Step Over menu items
Used to execute one statement in a procedure at a time
• VBA Toggle Breakpoint
Allows programmer to examine state of the processing
at the time when the statement executed
• Clear All Breakpoints option
Clears all breakpoints set previously
9
Breakpoints, Step into, and
Step Over
• Step Into menu item
Causes execution to continue to progress one
statement at a time inside the invoked
procedure
If the statement being executed invokes a
procedure or a function, then Step Into will
enter that procedure or function, allowing you
to debug it as well
9
Breakpoints, Step into, and
Step Over
• Step Over menu item
If the statement being executed invokes a
procedure or a function, Step Over does not go
into the procedure or function
• Run to Cursor item in the Debug menu
Causes Access to continue processing until it
reaches the point where you have placed your
cursor and then procedure will then suspend
processing
9
Breakpoints, Step into, and
Step Over
• When you select the Continue item on the
Run menu, Access will continue processing the
procedure until it reaches another breakpoint
• Reset menu item
Resets the internal representation of the code so
that the execution environment returns to what
it would be before the procedure is executed
9
Breakpoints, Step into, and
Step Over
• Set Next Statement option
Causes the system to move to the statement that
currently contains the cursor and makes it the
next executable statement
• Show Next Statement option
Displays the next executable statement in the
procedure
9
Using Watch Expressions to
Find Logic Errors
• Watch window allows your code to break on
changes in the variable that you want to analyze
Figure 9-4
The Add
Watch
window
9
Using Watch Expressions to
Find Logic Errors
• You can change or delete watch expressions
by using the Edit Watch menu item
Figure 9-5
The Edit
Watch
window
9
Using Watch Expressions to
Find Logic Errors
• Locals window
Displays the current values of all expressions
encountered in the procedure currently being executed
Figure 9-6
The Locals
window
9
Call Stack Window
• Call Stack window
Shows the sequence of procedures that were
invoked, leading to the suspended procedure
Figure 9-7
The Call
Stack
window
9
Repairing and Compacting a
Database
• Corrupt application
Occurs when an error affects the Access internal
representation of an application’s tabled, forms, reports,
procedures, and macros
• Symptoms of a corrupt application
Access may recognize when the application is corrupt
and notify the user when the application is opened
Application may run in a strange and unpredictable
fashion
9
Chapter Summary
• Statements
Accomplish an objective by organizing the objects,
variables, and expressions used in a procedure
Assign values to properties, influence the execution of a
procedure, and change aspects of Access environment
• To build an Access application, a developer must
construct statements, order them inside a
procedure, and determine when the procedure
should execute
9
Chapter Summary
• VBA supports several types of statements
Assignment statement
Object variables require the Set statement
With statement
Conditional Execution statements
Provided in the form If…Then…Else and If…Else
statements
Select Case statement
9
Chapter Summary
• VBA supports several looping statements
For Next Loop, the Do While loop, and the Do
Until loop
Provide different ways to control the number of loop
executions
Do statements
Allow the condition controlling the execution of a
loop to be placed either at the beginning or the end
of the loop
9
Chapter Summary
• On Error statement
Establishes error-handling capabilities within a
procedure
• Compilation errors
Often identified as soon as statements are entered
• Runtime errors
Cause Access to display an error message and highlight
the line where processing stopped
9
Chapter Summary
• Logic errors
Can be debugged as an application is executing
• Breakpoints
Suspend execution of an application
• Repair database menu item
Used to repair a corrupt database
• Compacting a database
Can significantly reduce the amount of storage space
required by an Access application