Procedures and Modules

Procedures are containers for VBA code. There are three types of these containers: subprocedures, function procedures, and property procedures. Although their functions overlap in some areas, each type has a specific and unique purpose. Access offers two basic kinds of containers for procedures: standard modules and class modules. Class modules can be custom classes for forms and reports. You can also use them to define your own classes to simplify reuse of code for such routine tasks as adding new employees, making a deposit to an account, or withdrawing money from an account.

Sub Procedures

Sub procedures can perform actions, compute values, and update and revise built-in property settings. ("Sub procedure" is usually shortened to "subprocedure" or simply "procedure.") As you have seen, Access 2000 automatically invokes procedures for events, but you can use them more broadly. Procedures never return values. They also do not define custom properties for form, report, or class modules.

A procedure consists of a series of VBA statements between Sub and End Sub statements. The Sub statement must declare a name for the procedure. While event procedures have very stylized names (such as object_event), procedure names generally follow standard variable naming conventions. They must begin with a letter, cannot exceed 255 characters, cannot include punctuation or spaces, and should not include a VBA keyword, function, or operator name. Procedures can take arguments, which you insert after the procedure name. If there is more than one argument, you separate them with commas.

One way to gain a basic familiarity with procedures is by using the Command Button wizard, which writes VBA code for more than 30 functions. All you have to do is make a few selections in dialog boxes. The procedures written by the wizard are generally simple, so they make good learning models. Even intermediate and advanced developers can benefit from the wizard because it generates a good skeleton for adding code with more specifics. Beginners can use the wizard to quickly automate record navigation, database maintenance, general form and report operations, invoking of other applications, and miscellaneous tasks such as running a query or dialing a phone number.

You invoke the Command Button wizard from the toolbox in Form Design view. Select the Control Wizards button and then drop a button on the form. This opens the dialog box shown in Figure 1-3. You can select from about a half dozen actions in each category. After completing all the dialog boxes for the action you select, you can view the code in Access 2000 VBE. Click the Code button on the Form Design toolbar to switch to VBE.

Figure 1-3. Using the Command Button wizard, you can create a whole event procedure by replying to a few dialog boxes.

Many developers prefer to write their procedures from scratch. You can open a code window for an event procedure as described above, or you can create an ordinary procedure. There are two ways to start this kind of procedure, depending on where you put it. If the procedure is to reside behind a form or report, click the Code button on the Design toolbar. If the code is to exist in a standard module that is not behind any specific form or report, choose Tools-Macro-Visual Basic Editor or press Alt+F11. In either case, you end up in VBE. Choose Insert-Procedure, and in the Add Procedure dialog box enter a name and confirm the selection of the Sub option button. This creates a shell for a procedure with a Sub and an End Sub statement. You can then add code to it.

Use your knowledge of the Access object model to code some actions. Recall that the DoCmd object has many methods. Type DoCmd and then press the period key. This opens a drop-down list box that displays all the legitimate entries after DoCmd. As you type an entry, Access converges on the subset of responses that match your entry. (See Figure 1-4.) If you are unsure what to type, you can scroll through the entries to find a method. This technique works for all objects, not just DoCmd. Microsoft calls this feature IntelliSense because it intelligently senses the subset of appropriate replies. IntelliSense actually does two things: It lists the properties and methods that are legitimate at any point in the construction of a VBA statement, and it provides syntax information about the content for the fields required for selected VBA statements. This dramatically reduces errors and helps you get started quickly.

Figure 1-4. IntelliSense helps to complete a VBA statement for the DoCmd object. You type the beginning of a statement, and it shows a list of legitimate replies for the balance of that portion of the statement.

The following is a simple procedure consisting of three lines. The first line reserves a memory location for a calculated result. The second adds two constants. The third prints the result in the Immediate window. This window is like a work pad for storing intermediate results as you check your code. You can open it from VBE by choosing View-Immediate Window. You can run the procedure from VBE by clicking anywhere in the procedure and then clicking the Run Sub/UserForm button on the Standard toolbar.

 Sub MyFirstCalculator() Dim Result     Result = 1 + 2     Debug.Print Result End Sub 

In a more typical situation, you can call a procedure in either of two ways. You can include its name on a line by itself; if the procedure has any arguments, you can include them after the name, separated by commas. Alternatively, you can precede the procedure name with Call. This is a VBA keyword for invoking a procedure. When you use Call, enclose arguments in parentheses after the procedure name.

The following is a slightly more flexible version of the initial calculator function. The calculator consists of a pair of procedures. The one named MySecondCalculator adds any two numbers and prints the result to the Immediate window. It determines what numbers to add from the two arguments that it receives. The other procedure calls the procedure that performs the sum. You can vary the numbers the second procedure adds by altering the value of the two arguments in the first procedure. In a more sophisticated application, you might tie these argument values to variables or form fields.

 Sub CallSecondCalculator()     MySecondCalculator 1, 3 End Sub Sub MySecondCalculator(First, Second) Dim Result     Result = First + Second     Debug.Print Result End Sub 

Function Procedures

Function procedures—usually called "functions"—differ from procedures in a couple of ways. First, they can return a value, so you can use them in expressions just as you use variables. Second, they do not serve as event procedures. Both procedures and functions can perform tasks. With the exceptions noted, functions and procedures are generally interchangeable.

A function is a collection of VBA statements bounded by Function and End Function statements. It can accept arguments in the same way that a procedure accepts arguments. A function can include one or more expressions. At least one of these can set the function's name equal to a value. You can terminate a function after setting its value by using an Exit Function statement. Any function can contain one or more Exit Function statements.

Although a function can return a value, it does not have to. A function can be a collection of statements that invoke methods and set properties without ever setting the function name equal to a value. This is one way in which functions and procedures are similar.

You start a function just like you do a procedure, but you select the Function option button instead of the Sub option button in the Add Procedure dialog box. You can invoke a function procedure by clicking the Run Sub/UserForm button on the Standard toolbar in VBE. You can also invoke a function from the Immediate window: Type a question mark followed by the function name. If the function has arguments, you place these in parentheses, separated by commas. You can run your own custom functions as well as built-in Access functions from the Immediate window.

Figure 1-5 shows a view of VBE with a simple function that determines whether the date submitted as an argument is in the third millennium. To match popular conventions, we'll compute this millennium to start in the year 2000 rather than 2001. The function Year2KTest accepts a date and returns 3 if the date is in the third millennium, or returns 0 otherwise. The Immediate window below the code window shows the outcome for running the function with two different dates. The Immediate window in Figure 1-5 confirms this result by returning a 3 for the first day in 2000 and a 0 for the first day in 1999. Notice that you must enclose a date between pound signs.

Figure 1-5. A simple function invoked from the Immediate window.

The procedure below is a more sophisticated approach to millennium assessment. The function starts by declaring the range of dates for which it is accurate. Then it tests its argument against two millennium milestones. If the date does not fall within the first or second millennium, the function assumes that it belongs in the third millennium. The initial message box statement indicates the range of dates for which the function is accurate.

Public Function PopularMillennium(dtmDateIn) As Byte     MsgBox "This works for dates after 12/31/0099" & _         " and before 1/1/3000.", _         vbInformation, _         "Programming Microsoft Access 2000"     If dtmDateIn <= #12/31/999# Then         PopularMillennium = 1     ElseIf dtmDateIn <= #12/31/1999# Then         PopularMillennium = 2     Else         PopularMillennium = 3     End If End Function 

Access 2000 dates are valid from 1/1/100 through 12/31/2999. This range is sufficient for the vast majority of desktop applications. If you need a range beyond these dates, you should consider coding dates independently of the Access serial date system.

NOTE

---

Access 2000 is Year 2000-compliant. Like previous versions, it stores dates with the full four digits. Access 2000 also handles leap years correctly: Years divisible by 4 are leap years unless they are divisible by 100. However, if they are divisible by 400, they are leap years. Since 2000 is divisible by 400, it is a leap year. This rule is critical for computing the difference between two dates. The General Date and Short Date formats in Access use the operating system's short date format options to determine the correct display. If you set your Regional settings in the Control Panel to show dates with four-digit years, all of the General date formats will show the full four digits.

The above rules do not force you to program in such a way that Y2K issues cannot emerge. Therefore, any Access application can have a Y2K bug even though Access 2000 is Year 2000-compliant. Visit www.microsoft.com/technet/topics/year2k/default.htm for Microsoft's overview of the topic along with specific product and version Y2K compliance reviews. The FMS site (www.fmsinc.com/tpapers/index.html#Year 2000 Papers) has a different perspective on some Y2K/Access issues. FMS markets a product called Total Access Inspector 2000 that specifically detects Y2K issues in Access applications.

You can often use both procedures and functions to develop a solution to a problem. Figure 1-6 shows a form that relies on both kinds of functions to develop answers. The form allows a user to type values in the text boxes labeled Number 1 and Number 2. Clicking a function button of /, *, -, or + computes a corresponding outcome in the Result text box.

Figure 1-6. This form serves as a simple calculator. VBA functions enable the form's buttons and populate the Result text box based on entries in the other two text boxes.

The VBA code to implement the form in Figure 1-6 uses just four pairs of procedures, as shown below. Four procedures act as the event handler for a click to the four function keys on the form. These event handlers simply call a function that pulls the values from the two text boxes, executes the computation indicated by the function button, and returns that value to the event procedure. The event procedure, in turn, assigns the return value from the function to the third text box on the form. Notice the optional use of the Me prefix before the text box names. Since the code is behind the form with the text box, specifying the form name is optional. The event procedures use Me instead of the form's longer, more formal class name (Form_frmCalculator1).

 Option Compare Database Option Explicit Dim dblResult As Double Private Sub cmdAddition_Click()     Me.txtResult = MyAdder End Sub Private Function MyAdder()     dblResult = CDbl(txtNumber1) + CDbl(txtNumber2)     MyAdder = dblResult End Function Private Sub cmdSubtraction_Click()     Me.txtResult = MySubtractor End Sub Private Function MySubtractor()     dblResult = CDbl(txtNumber1) - CDbl(txtNumber2)     MySubtractor = dblResult End Function Private Sub cmdMultiplication_Click()     Me.txtResult = MyMultiplier End Sub Private Function MyMultiplier()     dblResult = CDbl(txtNumber1) * CDbl(txtNumber2)     MyMultiplier = dblResult End Function Private Sub cmdDivision_Click()     Me.txtResult = MyDivider End Sub Private Function MyDivider()     dblResult = CDbl(txtNumber1) / CDbl(txtNumber2)     MyDivider = dblResult End Function 

The function and the procedures reside in the code module behind the form. The Dim statement at the top of the module declares a variable that all of the procedures in the module can use. Since users can click only one function key at a time, this sharing of dblResult works. The Option Explicit statement forces the declaration of variables before their use. This helps to guard against typographical errors, which are a common source of errors in programs. Option Compare Database is a module-level specification that designates string variables sorted in an order determined by the locale ID in the Control Panel.

The following pair of procedures illustrates much of what we have covered about procedures, functions, and methods. The form, frmCalculator2, has just two controls: a text box named txtInput and a command button named cmdSquarer. The form computes the square of the entry in the text box when the user clicks the button. The procedures display the result in a message box.

 Option Compare Database Option Explicit Dim dblResult As Double Private Sub cmdSquarer_Click()     MySquarer Form_frmCalculator2.txtInput End Sub Public Sub MySquarer(MyOtherNumber As Double)     dblResult = MyOtherNumber * MyOtherNumber     MsgBox dblResult, vbInformation, _         "Programming Microsoft Access 2000" 'Optional statements illustrating the use of methods '    DoCmd.GoToControl "txtInput" '    txtInput.SetFocus '    DoCmd.Close acForm, "frmCalculator2", acSaveNo End Sub 

The cmdSquarer_Click event procedure invokes the MySquarer procedure and passes the contents of txtInput as an argument. MySquarer computes its result and then displays the result in a message box.

Three more comment lines suggest additional actions that you can perform. A line calling the GoToControl method shows how to move the focus from the button to the text box. The SetFocus example on the next line illustrates an alternative way to achieve the same result. The Close method shows how to close a form. Notice that this line uses yet another name, frmCalculator2, to refer to the form. Using the acSaveNo constant is important because it allows the form to close without a prompt asking whether to save the form.

Property Procedures

You use property procedures to define custom properties for forms, reports, and class modules. We will discuss class modules in the next section, and we will look at samples of property procedures in Chapter 7.

There are three types of property statements: Property Get, Property Let, and Property Set. You can use these statements to add special properties to forms. The Property Get statement and its matching End Property statement can return a value, just like a function procedure. If you define a property with only a Property Get statement, that property is read-only. A read-only property is convenient when you have the right to view a quantity but not to alter it—think of your 401K balance or your grades.

With some properties, it is important to be able to change them without being able to read them. For example, database security administrators do not necessarily need to be able to read the user passwords that they supervise. They only need to be able to write over them when users forget their password. Use the Property Let statement with its matching End Property statement to set a password.

The Property Set statement works similarly to the Property Let statement. Both create a setting for a property. The Property Let statement sets a property equal to a data type, such as a string or integer. The Property Set statement sets a property equal to an object reference. You use the Property Set statement with object references, such as references to a form or a report.

Since many properties are both read and write, you will frequently use both Property Get and either Property Let or Property Set. In this case, the pair of Property statements must have the same name so that they refer to the same property.

Modules

A module is a container for procedures and declarations such as Option Explicit and Dim. There are two basic kinds of modules. First, there are standard modules. These are listed under Modules in the Database window. The procedures in a standard module are independent of existing objects in an Access database file. This means there are no references to Me or control names without appropriate prefixes for the controls. However, your applications can reference procedures in standard modules readily from any other object.

The second kind of module is a class module. There are three basic varieties of these: form class modules, report class modules, and custom class modules. The procedures within a module are generally accessible to other modules. You can take a procedure out of this general scope by using the Private keyword when you initially specify the procedure. (See Figure 1-7 for sample syntax.) You can also explicitly declare procedures to have global scope by using the Public keyword.

A form module is the module for any form that has at least one declaration or procedure. Creating an event procedure for a form or a control on a form creates a form class module. Report class modules work about the same as form class modules, but the Report events are different from those for forms and you are unlikely to have the same mix of controls on a report as a form. You can create custom class modules that comprise method functions and procedure functions for a concept, such as an employee or an account. You can reference the methods and properties for custom class modules much as you do for built-in Access classes.

You use custom class modules like cookie cutters to make new instances of a class. Access offers two ways to accomplish this. First, you can use a single Dim statement to both declare the class and create a new instance of it. The syntax for this type of statement is

 Dim objInstance As New objClass 

The second approach relies on a pair of statements. The first member of the pair declares the object instance. The second member sets a reference to the object. The syntax for these statements is

 Dim objInstance as objClass Set objInstance = New objClass 

The objClass name refers to a class module populated with property procedures and public method functions. These method functions act as methods for the class just as the property procedures serve to define properties. Chapter 7 shows how to create and use custom class modules.