Creating and Calling Procedures


Now that you have a sample database, I return to a discussion of modules and procedures. You already saw a procedure inside the sample module illustrations provided previously, but let’s now analyze what the procedures are and how they can be used.

Procedures are the building blocks of modules. Each module contains one or more sub procedures or functions, and each sub procedure or function contains one or more VBA code statements.

Sub versus Function Procedure

Procedures can either be sub procedures or function procedures. A sub procedure performs an action but does not return a particular result. A function performs an action and returns a particular result. I will provide various examples to illustrate these differences. Sub procedures and functions can be called both from within other procedures and functions and when an event associated with an object is triggered (for example, button_click), as I describe in more detail in Chapter 3.

Create and Call a New Sub Procedure

The syntax for declaring a new sub procedure without parameters is:

  Sub ProcedureName 'code for the procedure End Sub 

The syntax for a new sub procedure that contains variables as parameters is:

  Sub ProcedureName(Variables) 'code for the procedure End Sub 

Variables are containers for storing data. If a procedure must use the value stored in a particular variable, that variable can be passed as a parameter to the procedure. Let’s create a new procedure to see how this works.

Try It Out-Creating the VerifyCreditAvail Procedure

image from book

Next, you will create a VerifyCreditAvail procedure that will compare two numbers to see if enough credit is available for the purchase.

  1. Insert the following code in the modBusinessLogic module underneath the declarations section (underneath Option Explicit statement). See Figure 2-2 for an example if you are unsure where to put the code.

      Sub VerifyCreditAvail(curTotalPrice As Currency, curAvailCredit As Currency) 'inform user if not enough credit for purchase If curTotalPrice > curAvailCredit Then    MsgBox "You do not have enough credit available for this purchase." End If End Sub 

  1. Run the procedure from the Immediate Window. If the Immediate Window is not already open, select View image from book Immediate Window. Type the command (shown in Figure 2-8) in the Immediate Window and then click Enter.

    image from book
    Figure 2-8

    Tip 

    If you receive an error message that says “the Macros in this project are disabled,” then you need to take a few steps to authorize the modules you just created to run. To do so, close the VBA Editor window you are in and return to Access. Locate the area underneath the ribbon toolbar that says “Security Warning – certain content in the database has been disabled.” Click the Options button to the right of this warning. Select the “Enable this Content” option from the pop-up that appears, and click OK. Return to the VBA Editor and repeat step 2 above to run the procedure. It should run this time.

  2. You should see a message box like the one shown in Figure 2-9.

    image from book
    Figure 2-9

How It Works

The VerifyCreditAvail sub procedure accepts two variables as parameters, curTotalPrice and curAvailCredit:

 Sub VerifyCreditAvail(curTotalPrice As Currency, curAvailCredit As Currency)

The procedure contains an If statement that compares the value of curTotalPrice to curAvailCredit to see if curTotalPrice is larger:

  'inform user if not enough credit for purchase If curTotalPrice > curAvailCredit Then     MsgBox "You do not have enough credit available for this purchase." End If

From the Immediate Window, you call the VerifyCreditAvail sub procedure and pass it the values 5.0 and 4.93. The value 5.0 represents curTotalPrice and the value 4.93 represents curAvailCredit. When the preceding If statement was evaluated, Access determined that 5.0 was greater than 4.93, and thus the message box indicating insufficient available credit was displayed.

image from book

Call a Sub Procedure from Another Procedure

After a procedure has been created, it can be called from within another procedure if desired. Here’s a simple example to illustrate the syntax:

  Sub FirstProcedure    'This code calls a procedure called SecondProcedure   SecondProcedure End Sub 

Now that you know the syntax for calling a procedure from within another procedure, you can write code to call the VerifyCreditAvail procedure you created previously from another procedure.

Try It Out-Calling VerifyCreditAvail from Another Procedure

image from book

You can now create a new procedure, called FinalizeOrder, that will call the VerifyCreditAvail procedure.

  1. Add the following procedure, called FinalizeOrder, to the modBusinessLogic module underneath VerifyCreditAvail:

      Sub FinalizeOrder() 'declare variables for storing Price and Credit Avail Dim curPrice As Currency Dim curCreditAvail As Currency 'give variables values here for illustration purposes curPrice = 4.5 curCreditAvail = 3.75 'call VerifyCreditAvail procedure VerifyCreditAvail curPrice, curCreditAvail End Sub 

  2. Run the FinalizeOrder procedure from the Immediate Window as shown in Figure 2-10.

    image from book
    Figure 2-10

  3. The same message box you received earlier is again displayed to indicate that available credit is not sufficient.

How It Works

The FinalizeOrder procedure uses two local variables: curPrice and curCreditAvail.

 Sub FinalizeOrder() 'declare variables for storing Price and Credit Avail Dim curPrice As Currency Dim curCreditAvail As Currency

The variables are then assigned values for illustration purposes.

  'give variables values here for illustration purposes curPrice = 4.5 curCreditAvail = 3.75 

Note that, in most cases, you would not hard-code the values in the procedure, but instead you would retrieve them from a database, another variable, or another source. After the variables were assigned values, you call the VerifyCreditAvail procedure, with those variables passed as parameters:

  'call VerifyCreditAvail procedure  VerifyCreditAvail curPrice, curCreditAvail 

You then run the FinalizeOrder procedure from the Immediate Window to test it. The FinalizeOrder procedure executes the code to declare the variables, assign the variable values, and call the VerifyCreditAvail procedure. Because the value of 4.5 specified for curPrice was greater than the value specified for curCreditAvail, the VerifyCreditAvail procedure caused a message box to be displayed, as in the prior example.

image from book

Call a Sub Procedure from an Event

In addition to being called from another procedure, sub procedures can also be called from an event. As discussed in detail in Chapter 4, events occur in response to an action taken by the user or the system. Recall that earlier I discussed the two types of modules: standard modules and class modules. The code examples you have written so far were written in the standard module called modBusinessLogic. You will now write code in a class module associated with a particular form.

Creating a Procedure for an Object Using the Code Builder

The first time you write code for a class module of a particular form or report, you must generate the empty procedure using the Code Builder. You may recall that in Chapter 1 there was an example of creating a new event procedure for cmdTest_Click using the Code Builder. We’ll now revisit the Code Builder idea to further investigate how it works and to let you try it out for yourself. The concept of events and their associated objects is described in greater detail in Chapter 3.

Try It Out-Create New Procedure for frmTest Using Code Builder

image from book

Let’s now create a new procedure for frmTest using the Code Builder.

  1. Return to the database window and open frmTest in design view. To do so, select Object Type image from book Forms from the Objects list, select frmTest, right-click, and then click the Design View option. Alternatively, select the Home ribbon on the toolbar, and then select View image from book Design View from the drop-down menu.

  2. Select the cmdRun button on the form. You should see the Properties window. If the Properties window is not visible, select the Design ribbon, and then Property Sheet in the Tools area of the toolbar and select the cmdRun button again.

  3. Select the Event tab from the cmdRun Properties window. Click the On Click event from the list, as shown in Figure 2-11.

    image from book
    Figure 2-11

  4. Click the ellipsis () button in the Event tab, and the screen in Figure 2-12 will appear.

    image from book
    Figure 2-12

  5. Select the Code Builder option from the list, as shown in the previous figure, and click the OK button.

  6. The Visual Basic Editor will now be displayed with the cursor flashing in a newly created empty procedure called cmdRun_Click (see Figure 2-13).

    image from book
    Figure 2-13

  7. Add the following code to the newly created procedure:

      Private Sub cmdRun_Click() 'declare variables to store price and avail credit Dim curPrice As Currency Dim curCreditAvail As Currency 'assign variables from current values in text boxes on Form curPrice = txtValue1 curCreditAvail = txtValue2 'call VerifyCreditAvail procedure VerifyCreditAvail curPrice, curCreditAvail End Sub 

  8. After adding the preceding code to the cmdRun_Click procedure, click the Save button in the Visual Basic Editor to make sure all code so far has been saved. It is a good idea to select the Save option periodically to ensure you do not lose any of your work.

  1. Return to the frmTest form and open it in View mode to run it. To do so, select the Home ribbon on the toolbar, and then click View image from book Form View from the toolbar menu with the form open. Input a value of 2000 for Value 1 and 1500 for Value 2, as shown in Figure 2-14.

    image from book
    Figure 2-14

  2. You should see the same message box that appeared before, indicating that not enough credit is available (see Figure 2-15).

    image from book
    Figure 2-15

How It Works

To create the empty procedure, you used the Code Builder from the frmTest form. Because you selected the click Event on the Properties window for the cmdRun button and then chose the Code Builder option, Access automatically generated an empty event procedure called cmdRun_Click. This event procedure will be called anytime the cmdRun button is clicked when the frmTest form is running.

When the code in cmdRun_Click runs, it first declares some local variables and assigns values to the local variables that the user specified in the text boxes. For example, the value of 2000 specified for the first text box is assigned to the curPrice variable. The value of 1500 for the second text box is assigned to the curCreditAvail variable.

  'declare variables to store price and avail credit Dim curPrice As Currency Dim curCreditAvail As Currency 'assign variables to current values in text boxes on Form curPrice = txtValue1 curCreditAvail = txtValue2

The VerifyCreditAvail procedure is then called using the local variables as parameters.

  'call VerifyCreditAvail procedure  VerifyCreditAvail curPrice, curCreditAvail 

When the VerifyCreditAvail procedure executed, it evaluated the available credit and displayed the same error message you saw earlier because the value you input for curPrice was greater than the value for curCreditAvail.

image from book

Creating Additional Procedures for an Object Using the Code Window

After the first event procedure is created for a form or report, an associated form or report module is automatically created. You can then create additional procedures for that particular form easily from within the code window itself. For example, after you added the cmdRun_Click event, the Form_frmTest object appears in the Project window below. When you select Form_frmTest in the Project window and then the Object navigation drop-down list, all the objects associated with frmTest are displayed (see Figure 2-16).

image from book
Figure 2-16

In the preceding example, txtValue1 is selected. This is the name of the first text box on the frmTest form. After you select the txtValue1 value from the Object navigation list, the values in the declarations/ procedures navigation list are then updated to correspond with those available for the txtValue1. Figure 2-17 displays some of these available procedures that can be created for the txtValue1 text box.

image from book
Figure 2-17

When you select any of the procedures in the list, such as BeforeUpdate, a corresponding empty procedure will automatically be created with the cursor flashing, ready for you to input your code (see Figure 2-18).

image from book
Figure 2-18

Writing code for object events will be explored in detail in Chapter 3, but at this point you should just know what a procedure is and that it can be called from various places, such as from an event or another procedure.

Create and Call a New Function Procedure

So far, all the examples you have reviewed have dealt with creating and calling sub procedures. Earlier, you learned that a function procedure is used to perform a particular action and also return a particular result. Let’s look at that in more detail now.

The syntax for declaring a new function procedure without parameters is:

  Function ProcedureName() As DataType 'code for the function End Function 

The syntax for a new sub procedure that contains variables as parameters is:

  Function ProcedureName(Variables) As DataType 'code for the procedure End Function 

Note that in both the preceding cases, the Function keyword is used instead of the Sub keyword in two places. Also note that a data type is required as part of the function declaration. The data type should be specified to correspond to the type of the value you want the function to output.

Now you can create a new function to illustrate these procedures.

Try It Out-Creating the CalculateSalesTax Function

image from book

The CalculateSalesTax function you create next accepts a price and a tax rate as parameters and calculates the amount of tax.

  1. In the modBusinessLogic standard module, add the following function, called CalculateSalesTax, after the FinalizeOrder sub procedure.

      Function CalculateSalesTax(curPrice As Currency, dblTaxRate As Double) As Currency 'declare variable for storing calculated tax Dim curTaxAmt As Currency 'calculate amt of tax based on price and rate curTaxAmt = curPrice * dblTaxRate 'return the calculated amt CalculateSalesTax = curTaxAmt End Function 

  2. After the function has been added, the Visual Basic Editor window should look something like Figure 2-19.

    image from book
    Figure 2-19

  3. Run the new function from the Immediate Window using the syntax shown in Figure 2-20 and press Enter. You can select View image from book Immediate Window if it is not already displayed.

    image from book
    Figure 2-20

  4. After you press Enter, the 0.25 value is displayed in the Immediate Window.

How It Works

The CalculateSalesTax function has a declaration containing two variables as parameters. It uses Currency as the return data type.

  Function CalculateSalesTax(curPrice As Currency, dblTaxRate As Double) As Currency 

A variable is declared to store the calculated tax value.

  'declare variable for storing calculated tax  Dim curTaxAmt As Currency 

The amount of tax is calculated by multiplying the curPrice value by the dblTaxRate value.

  'calculate amt of tax based on price and rate  curTaxAmt = curPrice * dblTaxRate 

Because the procedure is a function procedure, a particular value should be returned. In this case, it is desirable to return the amount of sales tax that was calculated. The name of the function is assigned to the curTaxAmt value. This, in effect, returns the curTaxAmt value to the calling procedure for further use and processing.

  'return the calculated amt  CalculateSalesTax = curTaxAmt 

When the function procedure was tested using the Immediate Window, the preceding code was executed using the values 5.0 and .05 you specified as the parameters. The value of 5.0 was assigned to the curPrice variable, and the value of .05 was assigned to the dblTaxRate variable. The result of 0.25 was then displayed as the result.

Did you happen to notice that this time you had to use a question mark and put the parameters in parentheses? The prior examples did not use the question mark and parenthesis when calling sub procedures from the Immediate Window. As you just learned, the syntax differs slightly depending on whether you want to call a sub procedure or function from the Immediate Window.

image from book

Call a Function from Another Procedure

Just like sub procedures, functions can be called from other procedures. The procedures you follow are basically the same as those described in the prior example that illustrates calling a sub procedure from another sub procedure. The main difference is that when you call a function from another function or sub procedure, you typically assign the return value of the function to a local variable and then use it appropriately. For example, to call the CalculateSalesTax function from within another function or sub procedure, you could use the following code:

  Dim curSalesTax As Currency 'call the CalculateSalesTax function and assign the result to the local variable curSalesTax = CalculateSalesTax(100,.06) 

Calling a Function from an Event

Again, just as with sub procedures, you can call a function from a class module associated with a form or report. The procedures are basically the same as those described in the prior example that illustrates calling a sub procedure from an event. A more detailed explanation of events can be found in Chapter 3.

Scope and Lifetime of Sub Procedures and Functions

Sub procedures and functions have a particular life cycle that determines how long they will live. They also have characteristics that determine from where they can be called. This is referred to as scope. How the procedure is declared determines how long the procedure will live and from where the procedure can be called.

First, let’s look at the procedure life cycle. Traditionally, a procedure executes and then everything it contained - such as the values in the variables - are released from memory. In circumstances where you must preserve all the values within a procedure after it finishes executing, you can declare the procedure with the Static keyword.

Using the Static keyword in the declaration will basically keep the procedure in memory from the last time the procedure ran, including all associated values of the variables. As you may be aware, there are other ways to preserve particular values after a procedure ends. Examples include using public variables or storing certain values in a database for later retrieval.

Let’s now look at how to determine and specify procedure scope from where a procedure is called. Event procedures are private by default, but all other procedures and functions are public by default. In the examples used so far in this chapter, all the procedures were standard public declarations. The sub and function declaration syntax did not always use the word “Public” per se, but those declarations were indeed private declarations. When a procedure is declared as private, it can only be called from within the same object. The Public keyword can be used with to make modules available from outside the same object.

Using Built-In Functions

You have looked at some examples of creating your own functions. You should also know that VBA has an extensive list of built-in functions that you can use instead of writing the code yourself for certain common actions. One example of a built-in function that you have already used is the MsgBox function. You can obtain a complete list of available functions in the VBA Help documentation, as in the help topic shown in Figure 2-21.

image from book
Figure 2-21

Tip 

You can also view a list of available functions using the Expression Builder. Consult the VBA Help documentation for how to access the Expression Builder.

Macros versus Procedures

Now that you are familiar with procedures and functions, it is worth mentioning that you can use macros instead of procedures for some very basic tasks. Procedures provide you with much greater functionality and flexibility than do macros. Thus, in most cases, you should start using procedures instead of macros. However, with the many improvements in Access 2007 for macros, you may want to start using macros for some of the simpler programming-related tasks, and then use VBA for the more complex tasks. With Access 2007, macros now support simple variables, limited looping, and even some error handling. The applications you build in later chapters will use both macros and VBA code so you can get a better idea of when to use one versus the other. For now, let’s look at a simple example of a macro.

The AutoExec macro is one example of a macro that you can use to control which form loads when the database opens. Let’s create a new AutoExec macro to open the frmTest form you created earlier any time the Ch2CodeExamples.ACCDB file is opened. Create a new macro by selecting the Create ribbon on the toolbar, and then selecting Macro from the group called Macro, as shown in Figure 2-22.

image from book
Figure 2-22

A new empty macro is displayed. To have a form load, select the OpenForm action from the action list, as shown in Figure 2-23. Next, to specify which form to open, select frmTest from the Form Name field, as shown in Figure 2-24.

image from book
Figure 2-23

image from book
Figure 2-24

Finally, you need to save the macro, such as by selecting the Save icon or by selecting the X in the right corner of the macro window (not of Access 2007 overall or you will close Access). You will be prompted to save the macro. Name the macro AutoExec, as shown in Figure 2-25, so it will run when the database opens. Whenever an Access database opens, it looks for a macro named AutoExec, and if one is found, the commands it contains are executed automatically.

image from book
Figure 2-25

Now, anytime you open the database, the AutoExec macro will load and open the frmTest form. Other types of macros will be illustrated throughout this book. Give it a try on your database. Close and reopen the database you created in this chapter and see if the form now loads automatically.

Tip 

As mentioned earlier in the chapter, if a security warning appears, you may need to enable macros and VBA code when you open Access in order for the code to execute. The Trust Center can be used to further customize the security settings in Access. To access the Trust Center, click the Office Button, and then choose Access Options near the bottom. Select the group called Trust Center from the left navigation area.




Beginning Access 2007 VBA
Beginning Access 2007 VBA
ISBN: 0470046848
EAN: 2147483647
Year: 2004
Pages: 143

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net