Trapping Errors and Automating ActiveX
Controls with VBA
Loops
lControl structure
- A series of VBA statements that
work together as a unit
lDo Loop
- A control structure that will
repeat statements in your procedure until a specified condition is true
lFor Next loop
- A control structure that executes
statements of code a specified number of times.
lFor 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
lThese 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
lExample:
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
lNote: step valuelets 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)
Loop
For Each Next Loop
lThe 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
ltwo options of making controls invisible or simply
disabling controls.
lSee 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
lDot notation
- Notation that uses the dot (
. ) operator to indicate that what follows is an item
defined by Access.
lBang notation
- Notation that uses the bang
operator ( ! ), in place of dot notation when the collection
referenced by an object is the default collection
lMe 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.
lCodeContextObject property
- A property that determines the
object in which a macro or VBA code is executing
lScreen object
- An object that is the particular
form, report, or controls that currently has the focus.
Controls Collection
lcan 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
lObject variables
- Used to declare variables in
your procedures to use in place of object names
2 ways of declaring the object variables.
lExample:
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 = Nothingreleases variable from memory
lThis 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
property.
lExample: 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:
lloop goes through each control on
the current form (Me) and, if it is not a label, it enables the control.
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
lErrors
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
lOn 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
lResume 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
lErr 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
lErr.Number
an integer value specifying the last error that occurred
Each error has unique
number
Default
error number property set to 0 (no error)
lErr.Description
string containing a description of error
Contains Access error message
Once error trapped can
replace message with more user-friendly error message
lErr.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
lErr.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
lErr.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
lErr.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
lThe Err Object is
extremely useful in determining what error has occurred and what to do
about it.
lThere are many error numbers that
can occur, again, it will take practice and experience to
determine the different types of errors that can occur.
lThe 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.
lthe 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
lError
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
lTimer
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
lProgram 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.
lDoCmd
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
lFindWhat
required, search data
Expression that evaluates to
text, number, or date
lMatch
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
lMatchCase
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
lSearch
- 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
lSearchAsFormatted
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
lOnlyCurrentField
not required
acAll
search include all the fields
acCurrent
- (default) search confined to current field (faster)
lFindFirst
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
lNotInList 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
lActiveX 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
lCalendar control
is automaticallyregistered with the system when control file
installed.
Many ActiveX controls are
registeredautomatically, 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, clickInsert on menu, clickActiveX
Control.
If control is in list it can be
added to form
To register an ActiveX
control, clickTools
on menu, clickActiveX Controls, clickRegister button,
in Add ActiveX dialog box navigate to ActiveX control file, clickOpen
button.
Using the Calendar ActiveX Control
lThe 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.
lsetting 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.
