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