Home

Modules, Procedures, and Functions

 

Modules

 

Introduction

A module is a file that holds code or pieces of code that belong either to a form, a report, or is simply considered as an independent unit of code. This independence means that a unit may also not belong to a particular form or report. Each form or report has a (separate) module. To access the module of a form or report, you can open the object in Design View and click the Code button Code. If you initiate the coding of an event of a form (or report) or of a control that is positioned on a form (or report), this would open the Code Editor window and display the module that belongs to the form (or report). If no code has been written for the form or report, its module would be empty:

 

If you have written events for a form (or report) or for the controls that belong to the form (or report), all these events would be part of the form's (or report's) module.

Creating a Module

Besides the modules that belong to forms (or reports), you can create your own module(s) that is(are) not related to a particular form (or report). There are three main ways you can create an independent module:

  • In Microsoft Access, on the Database Window, you can click Modules and click New. This would open the Code Editor with an empty file
  • In the Code Editor (or Microsoft Visual Basic) window, on the main menu, you can click Insert -> Module
  • In the Code Editor (or Microsoft Visual Basic) toolbar, you can click the Insert Module button or click the arrow of the Insert Module button and select Module

The names of modules are cumulative. This means that the first module would be called Module1; the second would be Module2, etc. It is a good idea to have names that are explicit especially if your application ends up with various modules. To use a custom name for a module, you must save it. This would prompt you to name the module. You can accept the suggested name or type your own and press Enter.

Practical Learning: Creating a Module

  1. From the resources that accompany this ebook, open the Exercise2 application. On the Database Window, if necessary, click Forms
  2. Double-click the frmProcedures form to open it in Form View. After viewing the form, switch it to Design View
  3. The accompanying resources include pictures of geometric figures. To enhance the form, you can add other pictures. To do that, on the Toolbox, click Image and click the left area of the labels. On the Insert Picture dialog box, locate the picture and add it
  4. To start a form module, with the form opened in Design View, on the Form Design toolbar, click the Code button

Sub Procedures

 

Introduction

A procedure is an assignment you ask Microsoft Visual Basic to perform besides, or to complete, the normal flow of the program. A procedure is created to work in conjunction with the controls' events of a database. Structurally, a procedure appears similar to an event. The main difference is that, while an event belongs to a control, a procedure doesn't. While an event is specific to the user's intervention or interaction with a control, a procedure can be general and applied anyway you like.

Creating a Procedure Manually

There are two kinds of procedures in Microsoft Visual Basic: A sub procedure and a function. The difference lies on their behaviors but their coding (programming) depends of your goal.

A sub procedure is a section of code that carries an assignment but doesn't give back a result. To create a sub procedure, start the section of code with the Sub keyword followed by a name for the sub procedure. To differentiate the name of the sub procedure with any other regular name, it must be followed by an opening and a closing parentheses. The Sub keyword and the name of the procedure (including its parentheses) are written on one line (by default). The section of the sub procedure code closes with End Sub as follows:

Sub ShowMe()

End Sub

The name of a sub procedure should follow the same rules we learned to name the variables, omitting the prefix:

  • If the sub procedure performs an action that can be represented with a verb, you can use that verb to name it. Here are examples: show, display
  • To make the name of a sub procedure stand, you should start it in uppercase. Examples are Show, Play, Dispose, Close
  • You should use explicit names that identify the purpose of the sub procedure. If a procedure would be used as a result of another procedure or a control's event, reflect it on the name of the sub procedure. Examples would be: afterupdate, longbefore.
  • If the name of a procedure is a combination of words, start each word in uppercase. Examples are: AfterUpdate, SayItLoud

The section between the first Sub line and the End Sub line is called the body of the sub routine. In the body of a procedure, you define what the procedure is supposed to do. If you need to use a variable, you can declare it and specify what kind of variable you need. There is no restriction on the type of variables that can be declared in a procedure. Here is an example in which a string variable is declared in the body of a sub procedure:

Sub CreateName()
    Dim strFullName As String
End Sub

In the same way, you can declare as many variables as you need inside of a sub procedure. The actions you perform inside of a procedure depend on what you are trying to accomplish. For example, a sub procedure can be simply be used to create a string. The above procedure can be changed as follows:

Sub CreateName()
    Dim strFullName As String
    strFullName = "Jacques Fame Ndongo"
End Sub

Similarly, a procedure can be used to perform a simple calculation such as adding two numbers. Here is an example:

Sub CalculateTotalStudents()
    Dim StudentsInClass1 As Integer
    Dim StudentsInClass2 As Integer
    Dim TotalNumberOfStudents As Integer
    
    StudentsInClass1 = 32
    StudentsInClass2 = 36
    TotalNumberOfStudents = StudentsInClass1 + StudentsInClass2
End Sub

There are two main ways a sub procedure receives values. To start, a procedure that is written in the module of a form (or report) has direct access to the controls that belong to the form (or report). This means that the procedure can call them and manipulate any of their available properties. Here is an example of a procedure implemented in a form that has a text box called txtCountry:

Sub ChangeColor()
    txtCountry.BackColor = 16763293
End Sub

In the same way, you can declare variables and perform operations inside of a procedure and hand the result to a control that is part of a form or report.

Practical Learning: Manually Creating a Procedure

  1. Click the first empty line in the Code Editor and type Sub SquareSolution and press Enter
  2. Notice that Visual Basic added the End Sub line and positioned the cursor inside the procedure
  3. Complete the sub procedure as follows:
     
    Sub SquareSolution()
        ' Declare the necessary variables for the square
        Dim dblSide As Double
        Dim dblPerimeter, dblArea As Double
        
        ' Retrieve the value of the side
        dblSide = txtSqSide
        ' Calculate the perimeter and the are of the square
        dblPerimeter = dblSide * 4
        dblArea = dblSide * dblSide
        ' Prepare to display the result in the appropriate text boxes
        txtSqPerimeter = dblPerimeter
        txtSqArea = dblArea
    End Sub
  4. Return to Microsoft Access and switch the form to Form View. Enter a value in the Side text box of the square and click the Calculate button. Notice that nothing happens.

Inserting a Procedure

Microsoft Visual Basic simplifies the creation of a procedure through the use of the Insert Procedure dialog box:

To display the Insert Procedure, you can click Insert -> Procedure on the main menu, or click the arrow of the Insert Procedure button on the toolbar. If you are creating a sub procedure, click the Sub radio button. If you want the procedure to be accessed only by the objects, events and procedure of the same module, click the Private radio button. If you want to access the procedure from outside of the current module, click the Public radio button.

Practical Learning: Inserting a Procedure

  1. Switch the form back to Design View and access the Code Editor.
    On the main menu, click Insert -> Procedure...
  2. On the Insert Procedure dialog box, click the Name text box and type SolveRectangle
  3. In the Type section, click the Sub radio button
  4. In the Scope section, click the Private radio button
     
  5. Click OK
  6. Implement the procedure as follows:
     
    Private Sub SolveRectangle()
        ' Declare the necessary variables for the rectangle
        Dim dblLength, dblHeight As Double
        Dim dblPerimeter, dblArea As Double
        
        ' Retrieve the values of the length and height
        dblLength = txtRLength
        dblHeight = txtRHeight
        ' Calculate the perimeter and the area of the rectangle
        dblPerimeter = (dblLength + dblHeight) * 2
        dblArea = dblLength * dblHeight
        ' Prepare to display the result in the appropriate text boxes
        txtRPerimeter = dblPerimeter
        txtRArea = dblArea
    End Sub

Calling a Procedure

After creating a procedure, you can call it from another procedure, function, or control's event. To call a simple procedure such as the above ChangeColor, you can just write the name of the sub procedure. Here is an example where a sub procedure is called when a form is clicked.

Private Sub Detail_Click()
    ChangeColor
End Sub

Practical Learning: Calling a Procedure

  1. In the Object combo box, select cmdSqCalculate
  2. Call the SolveSquare procedure as follows:
     
    Private Sub cmdSqCalculate_Click()
        SquareSolution
    End Sub
  3. In the Object combo box, select cmdRCalculate and implement its Click event follows:
     
    Private Sub cmdRCalculate_Click()
        SolveRectangle
    End Sub
  4. Return to Microsoft Access and switch the form to Form View
  5. In the Side text box of the square, type 14.55 and click the right Calculate button
  6. Also text the rectangle with 18.25 for the length and 15.75 for the height and click its Calculate button:
     
  7. Save the form and return to the Code Editor

Functions

 

Introduction

A function is a procedure that takes care of an assignment and returns a result. A function resembles a sub procedure in all respects except that a function returns a value.

Creating a Function

A function is created like a sub procedure with a few more rules. The creation of a function starts with the keyword Function and closes with End Function. Here is an example:

Function GetFullName()

End Function

The name of the function follows the same rules and suggestions we reviewed for the sub procedures. Because a function should return a value, after the parentheses, you can type the As keyword followed by the type of data the function must return. Here is an example:

Function GetFullName() As String

End Function

When we studied variables, we saw that, instead of using the As DataType expression, you could use a particular character. This theory also applies to functions. To use it, on the right side of the name of the function, type the special character that represents the data type, followed by the parentheses of the function, and then omit the the As DataType expression. An example would be GetFullname$(). As with the variables, you must use the appropriate character for the function:

Character The function must return
$ a String type
% an Integer
! a Single type
# a Double
@ a Long

Here is an example:

Function GetFullName$()
        
End Function

The implementation of a function is done the same way that of a sub procedure is. Because a function is supposed to return a value, after performing whatever assignment you need in a function, you can assign the desired result to the name of the function before the closing of the function. Here is an example:

Function GetFullName() As String
    Dim strFirstName, strLastName As String
    strFirstName = txtFirstName
    strLastName = txtLastName
    GetFullName = strFirstName & " " & strLastName
End Function

Practical Learning: Creating a Function

  1. In the Code Editor, scroll down, click the first empty line, type Function CircleCircumference As Double and press Enter
  2. Notice that Visual Basic completed the code with the End Function line and positioned the cursor in the body of the function. Implement the function as follows:
     
    Function CircleCircumference() As Double
        Dim dblRadius As Double
        
        dblRadius = txtCircleRadius
        CircleCircumference = dblRadius * 2 * 3.14159
    End Function
  3. On the main menu, click Insert -> Procedure...
  4. Type CircleArea in the Name text box
  5. In the Type section, make sure the Function radio is selected (the default). In the Scope section, click the Private radio button and click OK.
  6. Implement the function as follows:
     
    Private Function CircleArea#()
        Dim dblRadius As Double
        dblRadius = txtCircleRadius
        CircleArea = dblRadius * dblRadius * 3.14159
    End Function

Calling a Function

To call a function, you have two main alternatives. If the function was implemented as simple as a sub procedure, you can just write its name in the event or the function that is calling it. If you want to use the return value of a function in an event or another function, assign the name of the function to the appropriate local variable. Here is an example:

Private Sub Detail_DblClick(Cancel As Integer)
    txtFullName = GetFullName
End Sub

Practical Learning: Calling a Function

  1. In the Object combo box, select cmdCCalculate and implement its Click event as follows:
     
    Private Sub cmdCCalculate_Click()
        txtCircleCircumference = CircleCircumference
        txtCircleArea = CircleArea
    End Sub
  2. Switch to Microsoft Access and switch the form to Form View
  3. Click the Circular tab and, in the top Radius text box, type 25.55
  4. Click the right calculate button (for the Circle)
  5. Switch the form back to Design View

Accessories for Programming

 

Introduction

When using a database, you are in fact using two applications to create a final product. Microsoft Access is used to design the necessary objects for your product. This means that Microsoft Access is used for its visual display of objects. On the other hand, Microsoft Visual Basic is used to handle code that enhance the functionality of your application.

The Compiler

The code you write is made of small instructions written in Visual Basic. These instructions are written in English, a language that the computer, that is the operating system, doesn't understand. Visual Basic, as its own language among other computer languages, is internally equipped with a low level program called a compiler. This program takes your English language instructions and translates them in a language the computer can understand. The language the computer speaks is known as the machine language. You usually don't need to know anything about this language.

After writing your code, at one time it is transmitted to the compiler. The compiler analyzes it first, checks its syntax, the words used in the program, the variables are checked for their declaration and use. The events and procedures are checked for their behavior. The expressions are checked for their accuracy. If something is wrong with the code, that is, if the compiler does not understand something in your code, it would display an error and stop. You must correct the mistake or else... As long as the compiler cannot figure out a piece of code in a module, it would not validate it. If the code is "admissible", the compiler would perform the assignments that are part of the code and give you a result based on its interpretation of the code. This means that the code can be accurate but produce an unreliable or false result. This is because the compiler is just another program: it does not think and does not correct mistakes although it can sometimes point them out. For this reason, you should know what you are doing.

Writing Procedures With Arguments

To carry out an assignment, sometimes a procedure needs one or more values to work on. If a procedure needs a value, such a value is called an argument. While a certain procedure might need one argument, another procedure might need many arguments. The number and types of arguments of a procedure depend on your goal. If you are writing your own procedure, then you will decide how many arguments your procedure would need. You also decide on the type of the argument(s). For a procedure that is taking one argument, inside of the parentheses of the procedure, write the name of the argument followed by the As keyword followed by the type of data of the argument. Here is an example:

Sub CalculateArea(Radius As Double)
   
End Sub

A procedure can take more than one argument. If you are creating such a procedure, between the parentheses of the procedure, write the name of the first argument followed by As, followed by the data type, followed by a comma. Add the second argument and subsequent arguments and close the parentheses. There is no implied relationship between the arguments; for example, they can be of the same type:

Sub CalculatePerimeter(Length As Double, Height As Double)
  
End Sub

The arguments of your procedure can also be as varied as you need them to be. Here is an example:

Sub DisplayGreetings(strFullName As String, intAge As Integer, dblDistance As Double)
    
End Sub

Practical Learning: Writing Procedures With Arguments

  1. Switch to the Code Editor. Click an empty area at the end of the existing code and create the following procedure:
     
    Sub SolveEllipse(SmallRadius As Double, LargeRadius As Double)
        Dim dblCircum As Double
        Dim dblArea As Double
        
        dblCircum = (SmallRadius + LargeRadius) * 2
        dblArea = SmallRadius * LargeRadius * 3.14159
        
        txtEllipseCircumference = dblCircum
        txtEllipseArea = dblArea
    End Sub
  2. To create an example of function that takes an argument, add the following function at the end of the existing code:
     
    Function CubeArea(Side As Double) As Double
        CubeArea = Side * Side * 6
    End Function
  3. To use different examples of functions that take one or two arguments, type the following functions:
     
    Function CubeVolume(Side As Double) As Double
        CubeVolume = Side * Side * Side
    End Function
    Function BoxArea(dblLength As Double, _
                     dblHeight As Double, _
                     dblWidth As Double) As Double
        Dim Area As Double
        
        Area = 2 * ((dblLength * dblHeight) + _
                    (dblHeight * dblWidth) + _
                    (dblLength * dblWidth) _
                   )
        BoxArea = Area
    End Function
    Function BoxVolume(dblLength As Double, _
                     dblHeight As Double, _
                     dblWidth As Double) As Double
        Dim Volume As Double
        Volume = dblLength * dblHeight * dblHeight
        BoxVolume = Volume
    End Function
 

Calling Procedures That Have Arguments

There are various ways you can call a sub procedure. As we saw already, if a sub procedure does not take an argument, to call it, you can just write its name. If a sub procedure is taking an argument, to call it, type the name of the sub procedure followed by the name of the argument. If the sub procedure is taking more than one argument, to call it, type the name of the procedure followed by the name of the arguments, in the exact order they are passed to the sub procedure, separated by a comma. Here is an example:

Private Sub txtResult_GotFocus()
    Dim dblHours As Double
    Dim dblSalary As Double
    
    dblHours = txtHours
    dblSalary = txtSalary
    
    CalcAndShowSalary dblHours, dblSalary
End Sub

Sub CalcAndShowSalary(Hours As Double, Salary As Double)
    Dim dblResult As Double
    
    dblResult = Hours * Salary
    txtResult = dblResult
End Sub

Alternatively, you can use the Call keyword to call a sub procedure. In this case, when calling a procedure using Call, you must include the argument(s) between the parentheses. using Call, the above GotFocus event could call the CalcAndShowSalary as follows:

Private Sub txtResult_GotFocus()
    Dim dblHours As Double
    Dim dblSalary As Double
    
    dblHours = txtHours
    dblSalary = txtSalary
    
    Call CalcAndShowSalary(dblHours, dblSalary)
End Sub

Practical Learning: Calling Procedures With Arguments

  1. To call the above procedures that take arguments, on the Object combo box, select cmdECalculate and implement its OnClick event as follows:
     
    Private Sub cmdECalculate_Click()
        Dim Radius1 As Double
        Dim Radius2 As Double
        Radius1 = txtEllipseRadius1
        Radius2 = txtEllipseRadius2
        SolveEllipse Radius1, Radius2
    End Sub
  2. On the Object combo box, select cmdCubeCalculate and implement its Click event as follows:
     
    Private Sub cmdCubeCalculate_Click()
        Dim dblSide As Double
        Dim dblArea As Double
        Dim dblVolume As Double
        
        dblSide = txtCubeSide
        dblArea = CubeArea(dblSide)
        dblVolume = CubeVolume(dblSide)
        
        txtCubeArea = dblArea
        txtCubeVolume = dblVolume
    End Sub
  3. On the Object combo box, select cmdBoxCalculate and implement its Click event as follows:
     
    Private Sub cmdBoxCalculate_Click()
        Dim dLen As Double
        Dim dHgt As Double
        Dim dWdt As Double
        Dim Area, Vol As Double
        
        dLen = txtBoxLength
        dHgt = txtBoxHeight
        dWdt = txtBoxWidth
        
        Area = BoxArea(dLen, dHgt, dWdt)
        Vol = BoxVolume(dLen, dHgt, dWdt)
        
        txtBoxArea = Area
        txtBoxVolume = Vol
    End Sub
  4. Close the Code Editor or Microsoft Visual Basic and return to Microsoft Access
  5. Switch the form to Form View and test the ellipse in the Circular tab
  6. Also test the cube and the box in the 3-Dimensions tab
     
  7. Save and close the form
  8. Close Microsoft Access
 

Techniques of Passing Arguments

 

Optional Arguments

If you create a procedure that takes an argument, whenever you call that procedure, you must provide a value for that argument. If you fail to provide a value for the argument, when the application runs, you would receive an error. Imagine you create a function that will be used to calculate the final price of an item after discount. The function would need the discount rate in order to perform the calculation. Such a function would look like this:

Function CalculateNetPrice(DiscountRate As Double) As Currency
    Dim OrigPrice As Double
    
    OrigPrice = CCur(txtMarkedPrice)
    CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
End Function

Since this function expects an argument, if you don't supply it, the following program would not compile:

Function CalculateNetPrice(DiscountRate As Double) As Currency
    Dim OrigPrice As Double
    
    OrigPrice = CCur(txtMarkedPrice)
    CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
End Function

Private Sub cmdCalculate_Click()
    Dim dblDiscount#
    
    dblDiscount = CDbl(txtDiscountRate)
    txtNetPrice = CalculateNetPrice(dblDiscount)
End Sub

If a procedure such as this CalculateNetPrice() function uses the same discount rate over and over again, instead of supplying an argument all the time, you can provide a default value for the argument. If you do this, you would not need to provide a value for the argument when you call the procedure. Such an argument is referred to as optional.

To make an argument optional, in the parentheses of its procedure, start it with the Optional keyword. On the right side of the data type of the argument, type the assignment operator, followed by the desired default value that would be used for the argument if you fail to provide one or decide not to provide one. Based on this, the above CalculateNetPrice() function could be defined as:

Function CalculateNetPrice(Optional DiscountRate As Double = 0.2) As Currency
    Dim OrigPrice As Double
    
    OrigPrice = CCur(txtMarkedPrice)
    CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
End Function

Private Sub cmdCalculate_Click()   
    txtNetPrice = CalculateNetPrice()
End Sub

Notice that, this time, you don't have to provide a value for the argument when calling the function: if you omit the value of the argument, the default value would be used. At another time, when calling the function, if you want to use a value that is different from the default value, you should make sure you provide the desired value. Consider the following call:

Function CalculateNetPrice(Optional DiscountRate As Double = 0.2) As Currency
    Dim OrigPrice As Double
    
    OrigPrice = CCur(txtMarkedPrice)
    CalculateNetPrice = OrigPrice - CLng(OrigPrice * DiscountRate * 100) / 100
End Function

Private Sub cmdCalculate_Click()
    Dim dblDiscount#
    
    dblDiscount = CDbl(txtDiscountRate)
    txtNetPrice = CalculateNetPrice(dblDiscount)
End Sub

Instead of one, you can also create a procedure with more than one argument. You may want all, one, or more than one of these arguments to be optional. To do this, declare each optional argument with the Optional keyword and assign it the desired value.

Consider the following example where two arguments are optional:

Function CalculateNetPrice(OrigPrice As Currency, _
                           Optional TaxRate As Double = 0.0575, _
                           Optional DiscountRate As Double = 0.25) As Currency
    Dim curDiscountValue As Currency
    Dim curPriceAfterDiscount As Currency
    Dim curTaxValue As Currency
    Dim curNetPrice As Currency
    
    curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
    curPriceAfterDiscount = OrigPrice - curDiscountValue
    curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
    
    txtDiscountValue = CStr(curDiscountValue)
    txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
    txtTaxValue = CStr(curTaxValue)
    CalculateNetPrice = curPriceAfterDiscount + curTaxValue
End Function

Private Sub cmdCalculate_Click()
    Dim curMarkedPrice As Currency
    Dim dblDiscountRate#
    Dim dblTaxRate#
    
    curMarkedPrice = CCur(txtMarkedPrice)
    dblDiscountRate = CDbl(txtDiscountRate)
    dblTaxRate = CDbl(txtTaxRate)
    txtNetPrice = CalculateNetPrice(txtMarkedPrice, txtTaxRate, dblDiscountRate)
End Sub

If you create a procedure that takes more than one argument, when calling the procedure, make sure you know what argument is optional and which one is required. When calling a procedure that has more than one argument but only one argument is optional, you can provide a value for the required argument and omit the others. Here is an example:

Function CalculateNetPrice(OrigPrice As Currency, _
                           Optional TaxRate As Double = 0.0575, _
                           Optional DiscountRate As Double = 0.25) As Currency
    Dim curDiscountValue As Currency
    Dim curPriceAfterDiscount As Currency
    Dim curTaxValue As Currency
    Dim curNetPrice As Currency
    
    curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
    curPriceAfterDiscount = OrigPrice - curDiscountValue
    curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
    
    txtDiscountValue = CStr(curDiscountValue)
    txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
    txtTaxValue = CStr(curTaxValue)
    CalculateNetPrice = curPriceAfterDiscount + curTaxValue
End Function

Private Sub cmdCalculate_Click()
    Dim curMarkedPrice As Currency
    
    curMarkedPrice = CCur(txtMarkedPrice)
    txtNetPrice = CalculateNetPrice(txtMarkedPrice)
End Sub

In reality, the Microsoft Visual Basic language allows you to create the procedure with the list of arguments as you see fit, as long as you make sure you clearly specify which argument is optional and which one is required. If you create a procedure that has more than one argument and at least one argument with a default value, if the optional argument is positioned to the left of a required argument, when calling the procedure, if you don't want to provide a value for the optional argument, enter a comma in its placeholder to indicate that there would have been a value for the argument but you prefer to use the default value. Remember that you must provide a value for any required argument. Consider the following example:

Function CalculateNetPrice(OrigPrice As Currency, _
                           Optional TaxRate As Double = 0.0575, _
                           Optional DiscountRate As Double = 0.25) As Currency
    Dim curDiscountValue As Currency
    Dim curPriceAfterDiscount As Currency
    Dim curTaxValue As Currency
    Dim curNetPrice As Currency
    
    curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
    curPriceAfterDiscount = OrigPrice - curDiscountValue
    curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
    
    txtDiscountValue = CStr(curDiscountValue)
    txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
    txtTaxValue = CStr(curTaxValue)
    CalculateNetPrice = curPriceAfterDiscount + curTaxValue
End Function

Private Sub cmdCalculate_Click()
    Dim curMarkedPrice As Currency
    Dim dblDiscountRate#
    Dim dblTaxRate#
    
    curMarkedPrice = CCur(txtMarkedPrice)
    dblDiscountRate = CDbl(txtDiscountRate)
    txtNetPrice = CalculateNetPrice(curMarkedPrice, , dblDiscountRate)
End Sub

Practical Learning: Using Default Arguments

  1. In the Forms section of the Database window, double-click the ItemPrice form to open it
  2. After viewing it, switch it to Design View
  3. On the form, click the Calculate button
  4. In the Properties window, click Events and double-click the On Click field
  5. Change the file as follows:
     
    Function CalculateNetPrice(OrigPrice As Currency, _
                               Optional TaxRate As Double = 0.0575, _
                               Optional DiscountRate As Double = 0.25) As Currency
        Dim curDiscountValue As Currency
        Dim curPriceAfterDiscount As Currency
        Dim curTaxValue As Currency
        Dim curNetPrice As Currency
        
        curDiscountValue = CLng(OrigPrice * DiscountRate * 100) / 100
        curPriceAfterDiscount = OrigPrice - curDiscountValue
        curTaxValue = CLng(curPriceAfterDiscount * TaxRate * 100) / 100
        
        txtDiscountValue = CStr(curDiscountValue)
        txtPriceAfterDiscount = CStr(curPriceAfterDiscount)
        txtTaxValue = CStr(curTaxValue)
        CalculateNetPrice = curPriceAfterDiscount + curTaxValue
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim curMarkedPrice As Currency
        Dim dblDiscountRate#
        Dim dblTaxRate#
        
        curMarkedPrice = CCur(Nz(txtMarkedPrice))
        dblDiscountRate = CDbl(Nz(txtDiscountRate))
        dblTaxRate = CDbl(Nz((txtTaxRate))
        txtNetPrice = CalculateNetPrice(curMarkedPrice, dblTaxRate, dblDiscountRate)
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Test it
  8. Close the form
  9. When asked whether you want to save it, click Yes

Random Call of Arguments

When you call a procedure that takes more than one argument, you must pass the arguments in the right order. Consider the following function:

Function ResumeEmployee$(salary As Currency, name As String, dHired As Date)
    Dim strResult$
    
    strResult = name & ", " & CStr(dHired) & ", " & CStr(salary)
    ResumeEmployee = strResult
End Function

When calling this function, you must pass the first argument as a currency value, the second as a string, and the third as a date value. If you pass a value in the wrong position, the compiler would throw an error and the program would not work. This is what would happen if you call it as follows:

Private Sub cmdResume_Click()
    Dim strFullName As String
    Dim dteHired As Date
    Dim curHourlySalary As Currency
    Dim strResume$
    
    strFullName = [txtFullName]
    dteHired = CDate([txtDateHired])
    curHourlySalary = CCur(txtHourlySalary)
    strResume = ResumeEmployee(strFullName, dteHired, curHourlySalary)
    txtResume = strResume
End Sub

While you must respect this rule, Microsoft Visual Basic provides an alternative. You don't have to pass the arguments in their strict order. Instead, you can assign the desired value to each argument as long as you know their names. To do this, when calling the function, to assign the desired value to an argument, on the right side of the sub procedure or in the parentheses of the function, type the name of the argument, followed by the := operator, followed by the (appropriate) value.

Practical Learning: Randomly Passing Arguments

  1. From the Forms section of the Database window, open the Employees Records1 form
  2. After view the form in Form View, on the main menu, click View -> Design View
  3. On the form, click the Resume button
  4. In the Properties window, click Events and double-click the On Click field
  5. Change the file as follows:
     
    Function ResumeEmployee$(salary As Currency, name As String, dHired As Date)
        Dim strResult$
        
        strResult = name & ", " & CStr(dHired) & ", " & CStr(salary)
        ResumeEmployee = strResult
    End Function
    
    Private Sub cmdResume_Click()
        Dim strFullName As String
        Dim dteHired As Date
        Dim curHourlySalary As Currency
        Dim strResume$
        
        strFullName = [txtFullName]
        dteHired = CDate([txtDateHired])
        curHourlySalary = CCur(txtHourlySalary)
        strResume = ResumeEmployee(name:=strFullName, dHired:=dteHired, _
                                   salary:=curHourlySalary)
        txtResume = strResume
    End Sub
  6. Return to Microsoft Access and switch the form to Form View
  7. Test it
     
  8. Close the form
  9. When asked whether you want to save it, click Yes

Passing Arguments By Value

So far, when creating a procedure with one or more arguments, we simply assumed that, when calling the procedure, we would provide the desired value(s) for the argument(s). With this technique, the procedure  receives the value of the argument and does what it wants with it. The argument itself is not changed. This technique is referred to as passing an argument by value. To reinforce this, you can type the ByVal keyword on the left side of the argument. Here is an example:

Function CalculateTriangleArea#(ByVal Base As Double, ByVal Height As Double)
    CalculateTriangleArea = Base * Height / 2
End Function

Practical Learning: Passing Arguments By Value

  1. Open the Triangle form in Design View
  2. On the form, click the Calculate button
  3. In the Properties window, click Events and double-click the On Click field
  4. Change the file as follows:
     
    Function CalculateTriangleArea#(ByVal Base As Double, ByVal Height As Double)
        CalculateTriangleArea = Base * Height / 2
    End Function
    
    Private Sub cmdCalculate_Click()
        Dim dblBase#
        Dim dblHeight#
        
        dblBase = CDbl([txtBase])
        dblHeight = CDbl([txtHeight])
        
        txtArea = CalculateTriangleArea(dblBase, dblHeight)
    End Sub
  5. Return to Microsoft Access and switch the form to Form View
  6. Test it
     
  7. Switch the form back to Design View

Passing Arguments By Reference

We also saw that the main difference between a sub procedure and a function is that a function can return a value but a sub procedure cannot. Microsoft Visual Basic, like many other languages, provides an alternative to this. Not only can a sub procedure return a value but also it makes it possible for a procedure (whether a sub or a function) to return more than one value, a feature that even a regular function doesn't have.

When creating a procedure with an argument, we saw that, by default, the procedure could not modify the value of the argument. If you want to procedure to be able to alter the argument, you can pass the argument by reference. To do this, type the ByRef keyword on the left side of the name of the argument.

If you create a procedure that takes more than one argument, you can decide which one(s) would be passed by value and which one(s) would be passed by reference. There is no order that the arguments must follow.

Practical Learning: Passing Arguments By Reference

  1. Return to Microsoft Visual Basic and change the file as follows:
     
    Sub CalculateTriangleArea(ByRef Area As Double, _
                              ByVal Base As Double, _
                              ByVal Height As Double)
        Area = Base * Height / 2
    End Sub
    
    Private Sub cmdCalculate_Click()
        Dim dblBase#
        Dim dblHeight#
        Dim dblArea#
        
        dblBase = CDbl([txtBase])
        dblHeight = CDbl([txtHeight])
        
        CalculateTriangleArea dblArea, dblBase, dblHeight
        txtArea = dblArea
    End Sub
  2. Return to Microsoft Access and switch the form to Form View
  3. Test the form with different values than previously
     
  4. Close the form
  5. When asked whether you want to save it, click Yes

Programmer-Defined Data Types

 

Introduction

The built-in data types we have used so far allow you to declare a variable of a specific known type. Alternatively, you can create a new data type by using one of the above or by combining some them to get a new one. To do this, you must create a new module for the new type. You start the new type with the Type keyword followed by the name of the new type. The create of the type ends with the End Type expression:

Type SampleType
      
End Type

Between the Type line and the End Type line, you can declare one or more existing types as variables. That is, each declaration can be made of a name for a variable, followed by As, and followed by a known data type. Here is an example:

Type Sphere
	Radius As Double
	Diameter As Double
	Area As Double
End Type

Using a Programmer-Defined Data Type

After creating the type, in the procedure or event where you want to use it, declare a variable based on it. To access any of the member variables of the type, enter the name of its variable, followed by a period operator, and followed by the name of the member variable. After accessing a member variable of a type, you can initialize, change its value, or assign it to another variable.

Practical Learning: Using a Custom Type

  1. On the Database window of Microsoft Access, click the Modules button
  2. To create a new module, click the New button on the toolbar of the Database window
  3. Under the Option Explicit line, type the following:
     
    Type Employee
        DateHired As Date
        FullName As String
        IsMarried As Boolean
        HourlySalary As Double
    End Type
  4. To save the module, on the Standard toolbar, click the Save button
  5. Set the name to modRoutines and click OK
  6. Return to Microsoft Access and open the Employee form in Design View
  7. Right-click the Create button and click Build Event...
  8. Double-click Code Builder and change the event as follows:
     
    Private Sub cmdCreate_Click()
        Dim Contractor As Employee
        
        Contractor.DateHired = #12/4/2000#
        Contractor.FullName = "Leslie Abramson"
        Contractor.IsMarried = True
        Contractor.HourlySalary = 20.15
        
        txtDateHired = CStr(Contractor.DateHired)
        txtFullName = Contractor.FullName
        chkIsMarried.Value = Contractor.IsMarried
        txtHourlySalary = Contractor.HourlySalary
    End Sub
  9. Close Microsoft Visual Basic
  10. Switch the form to Form View and click the Create button
     
    Employee
  11. Close the form. When asked whether you want to save, click Yes
  12. Close Microsoft Access

Introduction to Built-In Functions and Procedures

 

Introduction

Microsoft Access and Microsoft Visual Basic ship with various functions and procedures you can use in your database. Before creating your own procedures, you should know what is already available so you don't have to re-invent and waste a great deal of your time. The functions already created are very efficient and were tested in various scenarios so you can use them with complete reliability. The available functions range in various types. There are so many built-in functions and procedures that we can only introduce some of them here. You can find out about the other in the Help files because they are fairly documented.

Conversion Functions

When studying variables, we introduced and also reviewed the conversion functions. Here is a summary of these functions.

Function  
Name Return Type Description
CBool Boolean Converts an expression into a Boolean value
CByte Byte Converts an expression into Byte number
CDate Date Converts and expression into a date or time value
CDbl Double Converts an expression into a flowing-point (decimal) number
CInt Integer Converts an expression into an integer (natural) number
CCur Currency Converts an expression into a currency (monetary) value
CLng Long Converts an expression into a long integer (a large natural) number
CSng Single Converts an expression into a flowing-point (decimal) number
CStr String Converts an expression into a string
 

Message and Input Boxes

 

The Message Box

A message box is a special dialog box used to display a piece of information to the user. As opposed to a regular form, the user cannot type anything on the message box. There are usually two kinds of message boxes you will create: one that simply displays information and one that expects the user to make a decision.

A message box is created using the MsgBox function. Its syntax is:

MsgBox([Message] [Buttons] [Title] [HelpFile] [Context])

The MsgBox function takes five arguments and only the first one is required: the Message.

The Message argument is the string that the user will see displaying on the message box. As a string, you can display it in double quotes, like this "That's All Folks". Here is an example:

Private Sub cmdMessageBox_Click()
    MsgBox ("Your credentials have been checked.")
End Sub

This would produce:

You can also create it from other pieces of strings. The Message argument can be made of up to 1024 characters. To display the Message on multiple lines, you can use either the constant vbCrLf or the combination Chr(10) & Chr(13) between any two strings. Here is an example:

Private Sub cmdMessageBox_Click()
    MsgBox ("Your logon credentials have been checked." & _
            vbCrLf & "To complete your application, please " & _
            "fill out the following survey")
End Sub

This would produce:

The Buttons argument specifies what button(s) should display on the message box. There are different kinds of buttons available and Visual Basic recognizes them by a numeric value assigned to each. The Buttons argument can have one of the following constant

Button Constant Value Display
vbOKOnly 0 OK
vbOKCancel 1 OK Cancel
vbAbortRetryIgnore 2 Abort Retry Message Box Button: Ignore
vbYesNoCancel 3 Yes Message Box Button: No Cancel
vbYesNo 4 Yes Message Box Button: No
vbRetryCancel 5 Retry Cancel

If you decide to display one of these buttons, you Here is an example that displays the Yes and the No buttons on the message box:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", vbYesNo)
End Sub

This would produce:

Besides the buttons, to enhance your message box, you can display an icon in the left section of the message box. To display an icon, you can use the following icons on the message box

Icon Constant Numeric Value Description
vbCritical 16
vbQuestion 32 Question
vbExclamation 48 Exclamation
vbInformation  64 Information

To use one of these icons, you must combine its Icon Constant with one of the Button Constants reviewed previously. To perform this combination, you use the OR operator. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", vbYesNo Or vbQuestion)
End Sub

This would produce:

If you create a message box with more than one button, the most left button usually has a thick border, indicating that it is the default. If the user presses Enter after viewing the button, the effect would be the same as if he had clicked the default button. If you want, you can designate another button as the default. To do this, you can use one of the following values:

Default Button Constant Numeric Value If the message box contains more than one button, the default would be
vbDefaultButton1  0 The first button
vbDefaultButton2  256 The second button
vbDefaultButton3  512 The third button
vbDefaultButton4  768 The fourth button

Once again, to specify a default value, use the OR operator to combine a Default Button Constant with any other combination. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2)
End Sub

This would produce:

These additional buttons can be used to further control what the user can do:

Constant  Value Effect
vbApplicationModal 0 The user must dismiss the message box before proceeding with the current database
vbSystemModal 4096 The user must dismiss this message before using any other open application of the computer

The Title argument is the caption that would display on the title bar of the message box. It is a string whose word or words you can enclose between parentheses or that you can get from a created string. The Title argument is optional. As you have seen so far, if you omit, the message box is equipped with the "Microsoft Access" string as its default value. Otherwise, if you want a custom title, you can provide it as the third argument to the MsgBox() function. The caption can be a simple string. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
                     "Crofton Circle of Friends - Membership Application")
End Sub

This would produce:

Notice that the caption is now customized instead of the routine "Microsoft Access". The caption can also be a string created from an expression or emanating from a variable or value. Here is an example:

Private Sub cmdMessageBox_Click()
    Dim iAnswer As Integer
    Dim dteCurrent As Date
    Dim strTitle As String
    
    dteCurrent = Date
strTitle = "Crofton Circle of Friends - Membership Application: " + Str(dteCurrent)
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", _
                     vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
                     strTitle)
End Sub

If your application is using a help file, you can specify this and let the message box use it. The HelpFile argument is a string that specifies the name of the help file, and the Context argument provides the number that corresponds to the appropriate help topic for the message box.

The MsgBox() function can be used to return a value. This value corresponds to the button the user clicks on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return one of the following values:

If the user click The function returns Numeric Value
OK vbOK 1
Cancel vbCancel 2
Abort vbAbort 3
Retry vbRetry 4
Ignore vbIgnore 5
Yes vbYes 6
No vbNo 7
 

Practical Learning Practical Learning: Creating Message Boxes

  1. Open the Exercise2 database and, in the Database Window, click Forms
  2. Double-click the Messages form to open it
     
  3. Switch the form to Design View.
  4. Right-click the Message 1 button and click Build Event... On the Choose Builder dialog box, click Code Builder and click OK
  5. In the Code Editor, implement it as follows:
     
    Private Sub cmdMessage1_Click()
        MsgBox "This is Visual Basic as simple as it can get"
    End Sub
  6. To test the form, return to Microsoft Access and switch the form to Form View.
  7. On the form, click the Message 1 button.
  8. Notice that a message box displays. Also notice the caption on the title bar displays Microsoft Access.
  9. Click OK to close the message box.
  10. Switch the form back to Design View and return to the Code Editor
  11. Instead of the title bar displaying Microsoft Access as the caption, you can set your own caption. This is done through the 3rd argument of the MsgBox function. To see an example, on the Object combo box, select cmdMessage2 and implement its Click event as follows:
     
    Private Sub cmdMessage2_Click()
        MsgBox "Before formatting a floppy disk, " & _
               "make sure you know its content", , _
               "Disk Formatting Instructions"
    End Sub
  12. Test the form and the Message 2 button. Then return to the Code Editor
  13. When creating a message box using the MsgBox function, you can decide which button you want to use, using one of the constants we have listed earlier.
    To see an example, on the Object combo box, select cmdMessage3 and implement its Click event as follows:
     
    Private Sub cmdMessage3_Click()
        MsgBox "This will be your only warning", _
               vbOKOnly + vbExclamation, _
               "Attention! Attention!! Attention!!!"
    End Sub
  14. Test the form and the Message 3 button. Return to the Code Editor
  15. If you want to display a message on different lines, you can use the vbCrLf constant. As an example, on the Object combo box, select cmdMessage4 and implement its Click event as follows:
     
    Private Sub cmdMessage4_Click()
        MsgBox "You are about to embark on a long journey." & _
               vbCrLf & "If your courage is still fresh, " & _
               "now is the time to let us know!", _
               vbOKCancel + vbQuestion, _
               "Accept or Cancel the Mission"
    End Sub
  16. Test the form and experiment with the Message 4 button. Then return to the Code Editor.
  17. You can also display a message on various lines using the Chr() function. To see an example, on the Object combo box, select cmdMessage5 and implement its Click event as follows:
     
    Private Sub cmdMessage5_Click()
        MsgBox "This message usually appears when trying " & _
               "to format a floppy disk while the floppy drive " & _
               "is empty. " & Chr(13) & Chr(10) & _
               "When or if this happens, make sure you have a " & _
               " floppy disk in the floppy drive.", _
               vbAbortRetryIgnore + vbCritical, _
               "Floppy Disk Formatting"
    End Sub
  18. Test the form and the Message 5 button. Then return to the Code Editor.
  19. The usefulness of the MsgBox function is demonstrated in your ability to perform an action based on the button the user has clicked on the message box. Indeed, the implementations we have used so far were on the MsgBox method. If you want to get the button that the user has clicked, you have to use the function itself. The true capture of the clicked button is revealed by your finding out the clicked button. This is done using conditional statements that we have not learned so far. Therefore, we will just learn how to implement the function and how to assign a response button to it; throughout this tutorial, and whenever necessary, we will eventually see what to do when a certain button has been clicked. To see an example, on the Object combo box, select cmdMessage6 and implement its Click event as follows:
     
    Private Sub cmdMessage6_Click()
        Dim intResponse As Integer
        
        intResponse = MsgBox("Your printing configuration " & _
                             "is not fully set." & vbCrLf & _
                             "If you are ready to print, click" & vbCrLf & _
                             "(1) Yes: To print the document anyway" & vbCrLf & _
                             "(2) No: To configure printing" & vbCrLf & _
                             "(3) Cancel: To dismiss printing", _
                             vbYesNoCancel + vbInformation, _
                             "Critical Information")
    End Sub
  20. Test the form and the Message 6 button. Then return to the Code Editor
  21. When a message box displays, one of the buttons, if more than one is displaying, has a thicker border than the other(s); such a button is called the default button. By default, this is the 1st or most left button on the message box. If you want to control which button would be the default, use one of the default constant buttons listed above. To see an example, on the Object combo box, select cmdMessage7 and implement its Click event as follows:
     
    Private Sub cmdMessage7_Click()
        Dim intAnswer As Integer
        
        intAnswer = MsgBox("Do you want to continue this " & _
                           "operation?", _
                           vbYesNoCancel + vbQuestion + vbDefaultButton2, _
                           "National Inquiry")
    End Sub
  22. Test the form and the Message 7 button. Then return to the Code Editor.
  23. Although the user cannot type on a message box, not only can you decide what it displays, but you can also use string variables that would be available only when the form is running. As an example, on the Object combo box, select cmdMessage8 and implement its Click event as follows:
     
    Private Sub cmdMessage8_Click()
        Dim strEmplName As String
        Dim intInquiry As Integer
        
        strEmplName = CStr(txtEmployeeName)
        intInquiry = MsgBox("Hi, " & strEmplName & Chr(13) & _
                            "I think we met already." & vbCrLf & _
                            "I don't know when. I don't know where." & vbCrLf & _
                            "I don't know why. But I bet we met somewhere.", _
                            vbYesNo + vbInformation, _
                            "Meeting Acquaintances")
    End Sub
  24. To test the form, return to Microsoft Access
  25. On the Employee Name text box, type Joseph Douglas
  26. Click the Message 8 button and see the result.
  27. Click one of the buttons to close the message box.
  28. Close the running form, click its close button

The Input Box

Microsoft Visual Basic provides a function that allows you to request information from the user who can type it in a text field of a dialog box. The function used to accomplish this is called InputBox and its basic syntax is:

InputBox(prompt)

The most basic piece of information you should provide to the InputBox() function is referred to as the prompt. It should be a string that the user will read and know what you are expecting. Here is an example:

Private Sub cmdRequestDOB_Click()
    InputBox "Enter your date of birth as mm/dd/yyyy"
End Sub

This would produce

Upon reading the message on the Input box, the user is asked to enter a piece of information. The type of information the user is supposed to provide depends on you, the programmer. Therefore, there are two important things you should always do. First you should let the user know what type of information is requested. Is it a number (what type of number)? Is it a string (such as the name of a country or a customer's name)? Is it the location of a file (such as C:\Program Files\Homework)? Are you expecting a Yes/No True/False type of answer (if so how should the user provide it)? Is it a date (if it is a date, what format is the user supposed to enter)? These questions mean that you should state a clear request to the user and specify what kind of value you are expecting. For example, instead of the question above, you can implement the InputBox() function as follows:

Private Sub cmdRequestDOB_Click()
    InputBox "Please enter your date of birth as mm/dd/yyyy"
End Sub

Another solution, also explicit enough, consists of providing an example to the user.

The second thing you should take care of is the value the user would have typed. After typing a value, the user would click one of the buttons: OK or Cancel. If the user clicks OK, you should retrieve the value the user would have typed. It is also your responsibility to find out whether the user typed a valid value. Because the InputBox() function can return any type of value, it has no mechanism of validating the user's entry. To retrieve the value of the Input Box dialog when the user clicks OK, you must use the InputBox() function. Here is an example:

Private Sub cmdRequestDOB_Click()
    Dim dteDOB As Date
    
    dteDOB = InputBox("Please enter your date of birth as mm/dd/yyyy")
    txtDOB = dteDOB
End Sub

Sometimes, even if you provide an explicit request, the user might not provide a new value but click OK. The problem is that you would still need to get the value of the text box and you might want to involve it in an expression. You can solve this problem and that of providing an example to the user by filling the text box with a default value. Besides the prompt, Microsoft Visual Basic provides a more elaborate InputBox() function that allows you to specify more options, including a default value. The syntax used then is:

InputBox(prompt, Title, Default, XPos, YPos, HelpFile, Context)

Using this syntax, you can provide a title to display on the title bar of the Input Box dialog. This is taken care of by the Title argument. The XPos and YPos arguments allow you decide the position of the Input Box from left (XPos) and top (YPos) measurements of the screen.

 
 

Previous Copyright 2007, Yevol Next