Return to IFS
110
Notes!
Tutorial 8
Trapping Errors and Automating ActiveX
Controls with VBA
Loops
l
Control structure
- A series of VBA statements that
work together as a unit
l
Do
Loop
- A control structure that will
repeat statements in your procedure until a specified condition is true
l
For
Next loop
- A control structure that executes
statements of code a specified number of times.
l
For
Each
Next
- A construct that executes a group
of statements on each member of an array or collection
Array series of variables with a
common name and data type, arranged contiguously in memory, useful
for storing a series of values.
Do
Loop Syntax
Do [ {While | Until} condition ] test at
top of loop
[statements]
[Exit Do]
[statements]
Loop
Do
[statements]
[Exit Do]
[statements]
Loop [ {While | Until} condition ] test at
bottom of loop
Note: Exit Do used to exit
loop prematurely, not advisable to do so, considered unnatural
exit violates rules of structured programming
For
Next Loops and the For Each
Next Loops
l
These two loops differ from the
Do.. While and the Do
Until, because they are
executed a specific number of times.
A good rule of thumb if
unsure how many times you want to process a loop (i.e., you want the loop
to process based on a condition) is to use either the Do or the
While loop.
However, if you know exactly
how many times you want to process a loop, use the For
Next loop for
general looping and the For
Each for looping through a collection
of objects.
do not have to know how many times
want to go through a loop, just have to know that somewhere can get the number
that specifies the terminal value of the control variable.
For
Next Loop Example
l
Example:
process a loop for all the employees, know the number of employees and
store it in a variable, intEmployeeCount, can use that variable for the
terminal value. In this case a variable is used like this
intEmployeeCount = 50 (or value is
retrieved from some source)
For intCounter = 1 to
intEmployeeCount Step 1
Statement
Statement
Next intCounter
l
Note: step value
lets you increment loop by given amount,
if value is 1, it can be omitted from structure
For loop written as a Do While
intEmployeeCount = 50 (or value is
retrieved from some source)
intCounter = 1
Do While intCounter <= 50
Statement
Statement
intCounter = intCounter + 1
(step value in For
Next Loop)
Next intCounter
For Each
Next Loop
l
The For Each
Next loop is
used in VBA mostly for looping through a collection of objects.
Dim ctl As Control
For Each ctl In Controls
Ctl.ForeColor = 255
Next ctl
Note: in example, variable ctl is
declared as a control. This means that variable, ctl, can hold only controls,
loop is processing through the entire collection of controls and setting
their forecolor (color of the text) to red.
could also have used vbRed
in place of the 255.
constant, vbRed, is the same
as using the number code for red.
Additional color constants include;
vbBlack, vbGreen, vbYellow, vbBlue, vbMagenta,
vbCyan, and vbWhite.
Testing and Refining the Reports Switchboard
l
two options of making controls invisible or simply
disabling controls.
l
See Figure 8-4 and Figure 8-5
compare these two options.
Usually making controls invisible on a form where the user
is used to seeing them simply creates confusion. 
Dot vs. Bang Notation
l
Dot notation
- Notation that uses the dot (
. ) operator to indicate that what follows is an item
defined by Access.
l
Bang notation
- Notation that uses the bang
operator ( ! ), in place of dot notation when the collection
referenced by an object is the default collection
l
Me keyword
- A keyword that can be used to refer
to the associated form or report that the class module is in.
the Me keyword can be used to refer to the
form or other object that currently has focus. This is a shortcut
to the previous notation and it is also more versatile because it can
apply to whatever the current object is. Finally the CodeContextObject
can be used in place of the Me keyword in standard modules.
l
CodeContextObject property
- A property that determines the
object in which a macro or VBA code is executing
l
Screen object
- An object that is the particular
form, report, or controls that currently has the focus.
Controls Collection
l
can refer to a control on a form
either by implicitly or explicitly referring to the Controls
collection
Faster
to refer to a control implicitly
Example:
Me!grpEmployees or Me(grpEmployees)
Also can refer to a control by its
position in the collection index
Collection index
- A range of numbers that begin
with a zero, and in turn represent each object in the collection.
Example:
Me.Controls(0)
Object variables
l
Object variables
- Used to declare variables in
your procedures to use in place of object names
2 ways of declaring the object variables.
l
Example:
declare variable to refer to the frmReportsSwitchboard
1.
Dim frmMyForm as
Form_frmReportsSwitchboard
Must precede name of form or report
with Form_ or Report_ when defining a variable, because forms or reports
share same namespace.
2.
Dim frmMyForm as Form
Set frmMyForm = frmReportsSwitchboard
Uses set statement to
initialize the form
Can use frmMyForm
object variable to manipulate the frmRportsSwitchboard form
properties and methods
Set frmMyForm = Nothing
releases variable from memory
Dot vs. Bang Notation
l
most programmers use the
bang notation.
Forms![frmReportsSwtichboard]![grpEmployees].Enabled
l
This statement refers to the
Forms collection of the database and the ! separates the name of the
collection from the name of the particular form. The second ! refers to the
default collection, the controls collection and then specifically states the
name of the control. Finally the dot separates the control from the method.
l
Example: campus has a collection
of buildings. The buildings have a collection of classrooms, and the classrooms
have a collection of chairs. Say that the maintenance department needs to fix a
particular chair. The notation for finding the chair could be as:
Buildings![bldgMorrisHall]![rmClassroom]![chrChairNum].repair
For
Each statement Example
l
For
Each statement,
indentation.
For Each ctl In Me.Controls
If Not TypeOf ctl Is Label Then
ctl.Enabled = True
End If
Next ctl
l
loop goes through each control on
the current form (Me) and, if it is not a label, it enables the label.
3 Kinds of Errors
1.
Syntax errors
- An error that occurs when you
violate the VBA syntax rules.
·
easiest
to fix, result of a typing error, VBA will call attention to error if Auto
Syntax option is set on.
2.
Run-time errors
- An error that occurs while the
application is running.
·
an error message will be
displayed, message may not always make sense, with practice and experience,
learn to read and respond to error messages, more difficult to correct
because it is not always clear what part of the procedure is causing the error.
3.
Logic errors
- An error that occurs when your
procedures execute without failure, but their results are not what you
intended.
·
Logic errors can be the hardest
type of error to repair. Program runs without invoking an error message
but results are not correct. Stepping through a procedure can help
identify where problem is occurring.
i.
By watching values of variables,
can spot statements making wrong assignment to a variable.
ii.
can also spot when control is
passing to wrong place in the procedure.
Trapping Runtime Errors
l
Errors
in a program can be the death of the program.
If it happens frequently, users
will often abandon the use of the system altogether.
important to test a system
thoroughly for errors.
error trapping
keeps a program from fatal errors that cause the program to stop
running.
An error handling procedure
will be invoked when an error is encountered.
The error handler can be
programmed to respond in many different ways.
On Error statement
l
On Error statement
- Causes an error to be handled,
and execution of the procedure resumes either at the statement
that caused the error or at a different statement, depending on how the error
handler is enabled (Example: On Error GoTo TestError)
The On Error statement
allows the programmer to specify what should happen if an error is
encountered.
When errors are encountered, VBA
will look for an On Error statement.
If an On Error statement is
present, the statements following the
On Error statement will be executed.
If there is no On Error
statement, the program will stop.
The halting of a program can
be extremely discouraging to a user and should be avoided if at
all possible.
Resume statement
l
Resume statement
- A statement that resumes execution
after an error-handling routine is finished.
After an error has been handled by
an error handling routine, there should be a Resume statement that specifies
what action should be taken after the error has been handled.
3 options for Resume statement
1.
Resume
If error occurred in same procedure as error handler, execution resumes
with statement that caused error. If error occurred in a called
procedure, execution resumes at statement that last called out of the
procedure to error-handling routine.
2.
Resume Next
If error occurred in same procedure as
error handler, execution resumes with statement that immediately follows the
statement that caused error. If error occurred in a called procedure,
execution resumes with the statement immediately following the statement that
last called out of the procedure that contains the error-handling routine,
or execution resumes at the On Error Next statement.
3.
ResumeLine
execution resumes at the line specified in the required line argument
·
Line argument
- A line label or line
number and must be in the same procedure as the error handler.
i.
Line label
- Used to identify a single
line of code and can be any combination of characters that starts with a
letter and ends with a colon ( : ).
ii.
line number
- Used to identify a single line of
code and can be any combination of digits that is unique within the module
where it is used.
·
Line Numbers and Labels must both
begin in first column of code window
The Err Object
l
Err object
- A object that contains
information about an error that has just occurred.
When a run-time
error occurs, the properties of the Err object are filled with
information that both uniquely identifies error and that can be used to
handle it.
Properties
of Err object reset to 0 or zero-length string after the
Exit Sub or Exit Function statement executes in an error-handling routine.
Properties of Err Object
l
Err.Number
an integer value specifying the last error that occurred
Each error has unique
number
Default
error number property set to 0 (no error)
l
Err.Description
string containing a description of error
Contains Access error message
Once error trapped can
replace message with more user-friendly error message
l
Err.Source
contains name of object application that generated error
Example:
open Excel from Access, if error in Excel, Excel sets Err.Source property to
Excel Application
Properties of Err Object
l
Err.HelpFile
can be used to specify path and filename to VBA Help file
Information more user-friendly and
complete
By default, HelpFile property
displays default Help file that Access uses
l
Err.HelpContext
used to specify Help topic identified by path in HelpFile argument
Must be used in conjunction with
HelpFile property
By default, HelpContext property
displays default Help file that Access uses
l
Err.LastDLLError
contains system error code for the success or failure of
last call to a dynamic link library
Dynamic Link Library (DLL)
- A file containing a collection of Windows functions designed to
perform a specific class of operations
Functions
within DLLs are called as needed by applications to perform specific operations
VBA Errors
l
The Err Object is
extremely useful in determining what error has occurred and what to do
about it.
l
There are many error numbers that
can occur, again, it will take practice and experience to
determine the different types of errors that can occur.
l
The Help files list most of the
error numbers but, as a word of caution, it is not really that easy to find the
error number you are looking for.
Error 2103
is invoked when there is an attempt to open a report that does not exist.
Error 2497
is invoked when a method has been requested but the object name has not been
specified.
l
the name for the error, (PrintError)
is NOT indented.
As shown in figure 8-16,
the statement is flush with the left margin. However, the statements within
the error handler ARE indented.
Error and Timing Events
l
Error
occurs when an Access run-time error occurs in form or report
Does not include run-time
errors in VBA
To run an event procedure when
Error event occurs, set OnError property to name of event procedure
l
Timer
occurs when a specified time interval passes as specified by TimeInterval
property of the form.
Used to keep data synchronized
in multi-user environment to refresh or requery data at set
intervals
ComboBox Programming
l
Program the time card form so that
can bring up a particular time card record by selecting a time card
number from the combo box.
The user will select a time card
number and receive the matching record. First the code must insure that a time
card number has been selected and then it will synchronize the selection
with the proper record.
l
DoCmd
object used, use the GoToControl method and the FindRecord method.
GoToControl
method allows you to programmatically place the cursor on a
particular control.
FindRecord
method can be used to find a record that meets some criteria.
DoCmd.FindRecord arguments
l
FindWhat
required, search data
Expression that evaluates to
text, number, or date
l
Match
not required
acAnywhere
- searching for data contained in any part of field
acEntire
- (default) searching for data that fills entire field
acStart
- searching for data at beginning of field
l
MatchCase
not required, specify whether search is
case sensitive
True
(-1) for case-sensitive search
False
(0) (default) for non case-sensitive search
DoCmd.FindRecord arguments
l
Search
- not required, specify the direction of search
acUp
search starts with current record and goes back to beginning of records
acDown
search starts with current record and goes to end of records
acSearchAll
(default) to search all the records
l
SearchAsFormatted
not required, use true to search for data as it is formatted,
and false (default) to search for data as it is stored in
database
l
OnlyCurrentField
not required
acAll
search include all the fields
acCurrent
- (default) search confined to current field (faster)
l
FindFirst
not required, true (default) specifies search starts at first
record, and false specifies search should start at the record that
follows the current record
NotInList Event Procedure
l
NotInList event - can be used to trigger
code to respond to a specific error
The NotInList event applies only to controls on a form
and does not trigger the Error event
Code used in the Error event procedure in this form, to show the
default Access error message if error is not number 3058
NotInList Event Property (syntax)
Private Sub ControlName_NotInList (NewData
As String, Response as Integer)
ControlName
name of control whose NotInList event procedure is running
NewData
string Access uses to pass text user entered in text box portion
of combo box
Response
setting indicates how NotInList handled
acDataErrDisplay
(default) displays default message
Can be used to prevent user from
adding new value to combo box list
acDataErrContinue
default error message not shown
Can be used to display custom error
message
acDataErrAdded
specifies error message not shown
Can be used to add a new value to
combo box list
Line continuation character and (strings)
l
cannot put line continuation
character within the string because VBA wont recognize it as anything except
part of the string.
l
If they want to have the whole
string displayed, type it in like this:
MsgBox "The time card number you entered
does not exist. " & _
"Press Esc and enter the correct number."
l
Better to assign the 3 parts of
message box to variables
varMessage = This is Message Part of
MsgBox Function
varButtonsIcon = vbExclamation Intrinsic
Constant
varTitleBar = Sample Title Bar Text
MsgBox varMessage, varButtonsIcon ,
varTitleBar
ActiveX Controls
l
ActiveX control
- Similar to a built-in control
in that it is an object that you place on a form or report to display data
or perform an action.
ActiveX
controls are stored in separate files (.OCX), whereas built-in
controls are part of Access application
More than 100 ActiveX controls
available in Office 2002, and more available from third party vendors.
Word,
Excel, PowerPoint, VBA, Visual Basic, and Web
Pages all support ActiveX technologies
Each program might support a
different set of ActiveX controls (No Object in this Control error
message might indicate unsupported control)
If Access application is
distributed that uses ActiveX controls, must make sure that the
controls are installed on each computer that runs the
application
Registering an ActiveX Control
l
Calendar control
is automatically registered with the system when control file
installed.
Many ActiveX controls are
registered automatically, but some are not.
To add an ActiveX control to
form in Design view, it must be registered
To determine if ActiveX
control registered, open
form in Design view, click Insert on menu, click ActiveX
Control.
If control is in list it can be
added to form
To register an ActiveX
control, click Tools
on menu, click ActiveX Controls, click Register button,
in Add ActiveX dialog box navigate to ActiveX control file, click Open
button.
Using the Calendar ActiveX Control
l
The calendar control will
allow the user to pick a date on the calendar rather than having to
enter the date.
better for users
to choose something from a list or other control (like the calendar control)
rather than typing.
This cuts way down on
error due to typing errors.
l
setting the Modal property
on the calendar to Yes,
specifying that, when this form is
displayed (the calendar form), no other form can receive focus.
This way the user must finish
the selection of the date before going on to another control on the form.
Computer
Assignment 8
Return
to IFS 110 Notes!