Return to IFS 110 Notes!

Tutorial 7
Using Visual Basic for Applications

Modules

3 different types of modules

1. Standard module - is not tied to any other object.  It will contain procedures and functions that can be used by more than one object.

2. Form class module  - are tied specifically to a particular form.  The functions and procedures are intended to be used by that form only. 

l       These functions and procedures become methods of that form.

l       The controls on a form are part of the form’s class and so the code that goes with any control is also part of the form class module.

3. Report class module  - has the same distinction except that it is tied to a particular report instead of a form.

l       Note : ALL of the code for a form or report is contained in a single module. 

Sub Procedure and Functions

l     Module - An object that contains VBA code

l     Procedures - Perform actions and tasks in a module.

l     Function - returns a single value

l     Scope - The visibility and accessibility of one procedure from another procedure.

l     Public - A keyword that indicates that the procedure can be called from outside the module in which it is contained.

l     Private - A keyword that indicates that the procedure can be called only from within the module in which it is contained.

l     Comment - Text included in a procedure for the purpose of describing what the procedure does.

Procedure Scope Concepts

l     A procedure as a form class for example, doesn’t even exist as far as other forms modules are concerned. 

l      Therefore another form cannot access any of the sub procedures or functions in that module. 

l    The Public keyword can be used to “expose” a procedure and the Private keyword can be used to “hide” a procedure.

l     stack model of memory (Public vrs Private)

l      more efficient to use a private function or procedure where possible because it is released from memory as soon as the procedure has completed its work

OpenReport method arguments

l     ReportName – string expression valid report name

l     View – intrinsic constant (word that has a particular meaning in VBA)

l    acViewDesign – (Design view)

l    acViewNormal – (prints report immediately [default])

l   leave this argument out, the report will automatically print to the printer

l    acViewPreview – (Print Preview)

l     FilterName – string expression valid query name

l     WhereCondition – string expression valid SQL where clause without the word “where”

 OpenReport method arguments

l      WindowMode – intrinsic constant

l    acDialog – (Modal and Pop Up properties set to Yes)

l    acHidden – (Report open but hidden from view)

l    acIcon – (minimized as small title bar at bottom)

l    acWindowNormal – (opens in mode set by its properties [default])

l      OpenArgs – used to set OpenArgs of form

l    Read-only in all views, can only be set by using OpenArgs parameter with the OpenReport method of DoCmd object

l   Argument only available in VB, cannot be set using a macro

l     Note: Can leave out any of the arguments but still need to include the comma that would separate the arguments if they were there.  

Creating the reports switchboard

l      It is useful to illustrate how the commands are prompted as you type.  Open the VB window and type DoCmd, observe the list of methods associated with the DoCmd.

l      select OpenReport from the list and then hit the space bar, the prompt is telling which argument is currently requested (i.e., the one that is bold in the list of arguments).  Once this argument typed, prompted for the next one.

l      learn to use this assistance because it helps to know where to leave a comma when planning to leave an argument out.

l      program will determine whether the user has checked the Terminate box or not. 

l      The check box should be either Yes or No.

l      The third option (Null) for the checkbox not allowed because report printed for either terminated or current employees.

l      By setting the default value for the checkbox, it will always be set to the false or no unless the user clicks the checkbox.  

Variables and Constants

l     Variables - Named locations in memory that are used to store data of a particular type.

l     Implicit variable – defined by using it in a procedure

l     Explicit variable - Requires that you declare a variable before you use it

l     bottom line to the discussion of implicit vs. explicit declaration of variables is that variables should ALWAYS be declared explicitly.  There are many difficulties associated with allowing implicit declarations. (placing the Option Explicit statement in programs)

l     Constant - A meaningful name that takes the place of a number or string

Variables Examples

l    Variables are named locations in memory that are reserved for the duration of the program.

l     when the program terminates, so do all the variables. 

l     Example: Implicit Variables

Sale = 150

SalesTax = Sale * .075

TotalSale = Sale + SalesTax

Variables Examples

l    Example: Use strSQL string variable to hold the WhereCondition argument for the DoCmd.OpenReport

l     The string they put into the variable must be exactly what would have been entered into the statement.

strSQL = “Terminated = “ & chkTerm  &  _

     “AND HourlySalaried = “ & “ ’ ” & cboHS & “ ’ ” 

DoCmd.OpenReport “rptEmployeesList”, acViewPreview, ,strSQL

Variable Scope

l         Variable scope – similar to procedure scope

l        Lifetime – time during which a variable retains its value

l       When variable loses scope it no longer has a value

l       Scope can be thought of as variable’s lifetime or life span

l        Variable scope is determined by its declaration and its location in a module

4 different scopes of variables for declaration:

1.  Public - can be accessed by other modules

2. Private - can be accessed ONLY within the module

3. Static - used to create a variable that will retain its value

4. Dim – normally used to declare variables

Declaring Variables

l      Procedure-level variable - A variable that exists only when the procedure is running

l      declaring a variable inside a procedure makes that variable known ONLY within the procedure

l      Module-level variable - A variable that can be referenced by another function or Sub procedure within the same module

l      (declare a variable at the top of a module, in the declarations section of the module, the variable can be accessed by any procedure in the module

l      Normally when a procedure begins running, all variables (except static) are initialized: (number variable to 0, string variable to null string “”)

l      Normally, a variable loses its value when a procedure has completed its work.  If the value needs to be retained after the procedure is finished, the Static keyword can be used to create a variable that will retain its value.

Private and Public keywords

l    Private keyword can be used only in the General Declarations section of a module

l     Has same effect as using Dim keyword in the General Declarations section (variable available to any function or Sub procedure in the module)

l    Public keyword also can be used only in the General Declarations section of a module

l     Variable can then be referenced from any module anywhere within the database application

Variable Data types

l      If no date type defined for variable, the Variant data type is assigned by default, VBA automatically performs any necessary conversions

l      Implicit variable Access will assign as Variant data type unless Deftype statement used

•      Deftype statement allows certain data types assigned when variables begin with certain letters (DefStr L-Z : variables beginning with letters L-Z would be of string data type)

•       variant data type should be avoided by new programmers and left only for those who are very experienced.

§      variant data type offers a lot of flexibility but in turn wastes a lot of memory space and sacrifices speed (time to convert from one data type to another) 

l      Naming conventions used for variables. Most developers adhere to these conventions so that every object is easily identifiable by its name.

Declaring Constants

l     Constants are very much like variables in that they are temporary storage locations that hold values. 

l      Their distinction from variables is that they are assigned a value (and a type) and that value never changes. 

l      All other rules pertaining to scope apply to constants.

l      declare a constant by assigning it a value, the value assigned actually determines the data type.

l     Example: if you declare a constant as:

    Const conPi = 3.14159

l      This constant’s data type will be single. 

    Const conAppName = “MovieCam Technologies”

l      This constant’s data type will string.

Creating the Where Condition for Employee Reports

l     If the number of reports an organization needs is minimal then queries are the best approach. 

l      Create a query for the particular report then build a report around it. 

l    Then create another query and use either the same report or another one. 

l      However, in many settings, the number of variations for the report makes building queries for these variations much too cumbersome. 

l      In that regard, building Where conditions is very helpful because the user can, in effect, create their own query by making selection off the screen.

Creating combo boxes

l      combo box is another option available from the toolbox.  Using a list box or a combo box is very beneficial when the user must enter their choice of several options.

l      It cuts down on typing errors that produce undesirable results.

l     any time they can avoid typing in values they can save themselves a lot of trouble.

l      In the tutorial example, the user will make a selection and then the VBA code will store their selection in a variable.  

l      Since the list is set to have 2 columns, the Column Widths property has two values. 

l      The first one is for column 1 and the second for column 2.  So when they set the property to 0”; 1” they are saying that they want the first column to have NO size meaning that it won’t be visible.

Building the Where Condition

l      SQL underlies all queries, can look at the SQL to see how to write the statement that would produce the same dynaset as the query.

l      example on pages AC 310-311 (simply coming up with a string that will contain the Where condition for the SQL statement.)

l      The hardest part for any programmer to get right is concatenating the apostrophes at the right place.

l      the department name will be in a control cboDept (the combo box choice is text). (must use apostrophes the SQL statement to enclose the text or string data since set of double quotes already open in the expression) 

l    The department name must be enclosed in apostrophes so have to concatenate them on either side of cboDept. 

l    It is always easier to read if you store a string for the Where condition in a variable rather than typing it into the statement.  Then the statement need only include the variable that contains the string.  

SQL view of Query

SELECT tblEmployees.DeptNo, tblEmployees.HourlySalaried, tblEmployees.Terminated

FROM tblEmployees

WHERE (((tblEmployees.DeptNo)="20") AND ((tblEmployees.HourlySalaried)="H") AND ((tblEmployees.Terminated)=No));

l      Remove SELECT and FROM clauses, and WHERE word

(((tblEmployees.DeptNo)="20") AND ((tblEmployees.HourlySalaried)="H") AND ((tblEmployees.Terminated)=No));

l      Remove parentheses

tblEmployees.DeptNo="20" AND tblEmployees.HourlySalaried="H" AND tblEmployees.Terminated=No  

SQL view of Query Revisions

l     Because query has only one table, table references removed in WhereCondition of DoCmd.OpenReport

DeptNo="20" AND HourlySalaried="H" AND Terminated=No

l     Switchboard form contains cboDept, and cboHS combo boxes and the chkTerm check box, substitute names of controls for actual data (expression becomes)

DeptNo=" cboDept " AND HourlySalaried=" cboHS " AND Terminated= chkTerm  

Building WhereCondition Code
(Stages) (click to progress)

1. “DeptNo = "

2.  “DeptNo = "  & “ ’ ”

3.  “DeptNo = "  & “ ’ ”  & cboDept

4.  “DeptNo = "  & “ ’ ”  & cboDept & “ ’ ”

5.  “DeptNo = "  & “ ’ ”  & cboDept & “ ’ ” & “ AND HourlySalaried = "

6.  “DeptNo = "  & “ ’ ”  & cboDept & “ ’ ” & “ AND HourlySalaried = " & “ ‘ “

7.  “DeptNo = "  & “ ’ ”  & cboDept & “ ’ ” & “ AND HourlySalaried = " & “ ‘ “ & cboHS & “ ‘ “ &  “ AND Terminated = “ & chkTerm

l           Note: chkTerm does not need to be enclosed in apostrophes since its data is not text or string data (True or False, Yes or No Boolean)

IsNull and Mid$ functions

l     IsNull function – tests expression to see whether it is null (empty) or not

l      Syntax: IsNull(expression)

l      Example: If Not IsNull(cboDept) then

l     Mid$ function – returns variant or string with specified number of characters from a string

l      Syntax: Mid$(string, start [, length])

l    String – variable or string from which taking new string

l    Start – character position at which new string starts

l    Length – total number of characters to return (optional)

Building the Where Condition

l      The three sets of If statements that are added to the code on pages AC 313-314 will concatenate the user’s choice of conditions into a single string, strWhere.

l      The strWhere variable is built in the code the way it is, because the user can use the controls in any order, code needs to reflect this.

l      Example:  user has chosen all non-terminate employees, in the Accounting department who are Hourly. ( resulting string )

     AND DeptNo= '10' AND HourlySalaried = 'H' AND Terminated = 0

l      need to strip off the “AND “ at the beginning of the string

l      The MID$ function will allow them to obtain the string minus the first 6 characters (remember that AND has a blank on either side of it making it 6 characters in length).

l      After using the string, the grpEmployees, cboDept, cboHS, and chkTerm are all set back to either Null or false.  This prepares the switchboard for a new set of conditions.

Using the Immediate Window

l     Immediate window - A window that shows information that result from debugging statements in your code or from commands typed directly into the window.

l      Debug.Print Mid$(strWhere, 6)

l      ? Mid$(strWhere, 6)

l      ?ProperCase(“TEST”)

l     Breakpoint - A selected line of your program at which execution automatically stops.

l     Locals window - A window where Access automatically displays the name, current value, and type of all the variables and objects in the current procedure.

Reviewing Code Line By Line

l     By watching the execution of the program line-by-line, can watch the flow of control.

l      good way to further grasp the concept of an If statement, also be able to observe how the flow of control goes through a Select statement.

l     use these debugging tools anytime getting undesirable results. 

l      Watching the variables in the Immediate Window is very useful to observe the values those variables are taking on as the program progresses.

Modifying the Code for Employee Reports

l    start programming by going directly to the VB window and picking the object off the object list

l     Note: Introduced to programming the button by selecting the event in the properties window.

l    all objects on the form are available in the objects list (on the left) and the procedures for those objects are listed in the procedures list (on the right).

Code for Employee Reports
(order of operations on form)

1.  User clicks a button in grpEmployees option group to choose a report.

l          Name of report stored in variable strReport

2. User selects Terminated, Hourly or Salaried, and/or a department

l          Values need to remain in controls until report previewed

3. User clicks Preview button.

l            Note: because of the order of these steps, the code to build the WhereCondition must be run after the user selects Terminated, Hourly or Salaried, and/or a department from the controls on the form. If portion of code remains in AfterUpdate procedure, WhereCondition will be built before choices made, because AfterUpdate event fires as option is selected in option group, so code needs to be placed in Sub cmdPreview_Click() procedure.  

Adding the Where Condition for Job Reports

l     Run reports for specific job numbers by typing in the first few numbers of the job

l      WHERE JobID Like “92*”

l     Run job reports by a single date or range of dates

l      WHERE TimeCardDate = #11/9/2002#

l      WHERE TimeCardDate Between  #1/1/2002# And #12/31/2002#

l     Line continuation character - The underscore serves as a character that you can use when your code is too long to fit on a single line.

strWhere = strWhere & “ AND TimeCardDate Between “ & “#” & _
& txtStartDate & “#” & “AND “ “#” & txtEndDate & “#”  

Handling Query Dates

l      User enters beginning date in txtStartDate text box and nothing in txtEndDate text box, report shows records for only one date

l      WHERE TimeCardDate = #11/9/2002#

l      If both start and end dates entered, display range of dates in report

l      WHERE TimeCardDate Between #1/1/2002# And # 12/31/2002#

l      Problems: If start date blank and end date specified, provide error message to enter both dates in range, then clear controls on form and exit Sub procedure without previewing a report.

l      Use nested If…Then…ElseIf statement inside an If…Then…ElseIf statement. The first If statement tests to see if txtStartDate has entry. If it does, the nested If statement tests to see if txtEndDate field has a entry. If it does not, add a string to WhereCondition to query for only one date. If txtEndDate field has an entry, add a different string to WhereCondition that queries for a range of dates.  

IFS 110 Computer Assignment 7

Return to IFS 110 Notes!