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)

     Loop

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 property. 

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 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

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 DLL’s 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 won’t 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.

IFS 110 Computer Assignment 8

Return to IFS 110 Notes!