In a simple application, you may need to use Visual Basic only to create event procedures and simple functions, as shown in the previous sections. However, as your applications get larger and more sophisticated, you’ll want to use the full power of the Visual Basic language. This section lays out the fundamental rules for writing Visual Basic code in Microsoft Access.
You store your Visual Basic code in modules in a Microsoft Access database. Modules provide a way to organize your procedures.
Your database can contain two types of modules:
To create a new standard module, you can either click New on the Modules tab in the Database window, or you can click Module on the Insert menu.
To create a new class module, click Class Module on the Insert menu. Saved class modules appear on the Modules tab in the Database window with saved standard modules. You can distinguish an open class module from an open standard module by the title bar of the Module window—the title bar for a class module always includes the label Class Module.
Each form and report in your database can contain an associated form module or report module. Form and report modules are also class modules, but you can’t save them separate from the form or report that they belong to. The class module that is associated with a form is especially useful because you can use it to create multiple instances of a form.
Most frequently, you’ll use a form or report module to contain event procedures associated with the form or report. Each module can also contain other procedures that belong to the form or report. And, as with other class modules, you can use the Property Get, Property Let, and Property Set statements to create custom properties for the form or report.
A form or report module is part of the form or report’s design. Thus, if you copy a form or report to another database, its module goes with it; if you delete a form or report, its module is deleted as well. Microsoft Access creates the form or report module automatically when you first add Visual Basic code to the form or report. All you need to do is write the event procedures and other procedures you want to store in the module.
Note that a form or report doesn't have a module associated with it when it’s first created. These lightweight forms and reports typically load and display faster than forms or reports with modules. However, once you add any Visual Basic code to the form or report, the form or report module is automatically created. You can use the HasModule property to specify or determine if a form or report has an associated module.
A module can contain:
When Microsoft Access recognizes that an event has occurred on a form, report, or control, it automatically runs the event procedure named for the object and event. If you want to run code in response to a particular event, you add code to the event procedure for that event.
See Also For information on the Microsoft Access event model, see Chapter 6, “Responding to Events.” For more information on all events, search the Help index for “events.”
When you create an event procedure (by using the procedures described earlier in this chapter), Microsoft Access automatically creates a code template for the event and adds it to the form or report module. The name of an event procedure for a form or report is a combination of the word “Form” or “Report,” an underscore (_), and the event name. For example, if you want a form to run an event procedure when it’s clicked, use the procedure Form_Click.
An event procedure for a control uses the same naming convention. For example, if you want a command button named MyButton to run an event procedure when it’s clicked, use the procedure MyButton_Click. If a control name contains characters other than numbers or letters, such as spaces, Microsoft Access replaces those characters with an underscore (_) in any event procedures for the control.
Important If you want to change the names of your controls, it’s a good idea to do so before you start writing event procedures for them. If you change the name of a control after attaching a procedure to it, you also must change the name of the procedure to match the control’s new name. Otherwise, Visual Basic can’t match the control to the procedure. When a procedure name doesn’t match a control name, Microsoft Access makes it a general procedure. You can find general procedures in the Module window by clicking (General) in the Object box, and then clicking the procedure name in the Procedure box.
Microsoft Access runs event procedures in response to a particular event on a form, report, or control. A general procedure, in contrast, runs only when you explicitly call it. A function, for example, is a type of general procedure.
Why use general procedures? One reason is to create your own functions to automate tasks you perform frequently. For example, you can create a function and then either create a custom menu command or custom toolbar button that runs the function, or use the function in an expression.
See Also For information on customizing your menus and toolbars, see Chapter 1, “Creating an Application.” For information on using functions in expressions, search the Help index for “expressions.”
Another reason to use general procedures is that you may want several different event procedures to perform the same actions. A good programming strategy is to put common code in a separate general procedure and have event procedures call it. This eliminates the need to duplicate code, making the application easier to maintain.
You can create general procedures either in a class module (which can be a form or report module) or in a standard module. If you want a general procedure that’s always available from anywhere in your application, place it in a standard module. If a procedure applies primarily to a specific form or report, place it in the module for that form or report.
This section explains the syntax you use to create and call procedures in your application. Procedures can be either Sub procedures or Function procedures:
The syntax for a Sub procedure is:
[Private|Public] [Static] Sub procedurename [(arguments)]
statements
End Sub
The statements are the Visual Basic statements that make up the code you want to run each time the procedure is called. The arguments are argument names, separated by commas if there are more than one. Each argument looks like a variable declaration and acts like a variable in the procedure. The syntax for each argument is:
[Optional] [ByVal] variablename [( )] [As type]
Type can be any of the fundamental data types: Byte, Integer, Long, Single, Double, Currency, Decimal, String, Boolean, Date, Object, or Variant. If you don’t provide a type, the argument takes the Variant type and can contain any kind of data. Parentheses after variablename indicate that the argument is an array.
By default, arguments to a procedure are passed by reference, meaning that changing the value of the variable changes it in the calling procedure as well. To pass arguments by value rather than by reference, use the ByVal keyword.
See Also For information on the Optional keyword, see “Using a Variable Number of Arguments” later in this chapter. For information on the Static and Private keywords, search the Help index for “Sub statement.” For information on passing arguments by value or by reference, see “Argument Data Types” in Chapter 4, “Working with Variables, Data Types, and Constants.”
When you call a Sub procedure, you specify the arguments you want the procedure to use. For example, the following Sub procedure makes a beep sound the number of times you specify with the intBeeps argument.
Sub MultiBeep(intBeeps As Integer)
Dim intX As Integer, lngY As Long
For intX = 1 To intBeeps
Beep
For lngY = 1 To 100000 ' Short delay between beeps.
Next lngY
Next intX
End Sub
The following statement calls the MultiBeep Sub procedure by using an intBeeps argument of 3, making a beep sound three times.
MultiBeep 3
You don’t enclose arguments in parentheses when you call a Sub procedure, as you do when you declare one, unless you use the Call statement to call the procedure, in which case the parentheses are required.
Note To make your code more readable, you can pass arguments to Sub or Function procedures by name. For example, the following call to the MultiBeep Sub procedurepasses the intBeeps argument by name:
MultiBeep intBeeps:=3
When you pass multiple arguments by name, you can include them in any order you want. For more information on passing arguments by name, search the Help index for “named arguments.”
The syntax for a Function procedure is:
[Private|Public] [Static] Function procedurename [(arguments)] [As type]
statements
End Function
See Also For information on the Static and Private keywords, search the Help index for “Function statement.”
The arguments for a Function procedure work in exactly the same way as the arguments for a Sub procedure, and have the same syntax. Function procedures differ from Sub procedures in three ways:
For example, you could write a Function procedure that calculates the third side, or hypotenuse, of a right triangle given the other two sides.
Function Hypotenuse (dblA As Double, dblB As Double) As Double
Hypotenuse = Sqr(dblA ^ 2 + dblB ^ 2)
End Function
You call a Function procedure the same way you call any of the built-in functions in Visual Basic. For example:
dblResult = Hypotenuse(dblWidth, dblHeight)
Tip If you aren’t interested in the result of a Function procedure, you can call it without including parentheses or assigning it to a variable, as you would a Sub procedure. For example, you can use the following code to call a function called DisplayForm and ignore its return value:
DisplayForm strMessage
You can declare optional arguments in a procedure definition with the Optional keyword. An optional argument is one that doesn’t have to be passed every time you call the procedure. You must declare optional arguments after any required arguments in the argument list. They can be of any type.
If you include an optional argument in a procedure definition, then you need to consider what happens in the procedure when the argument is not passed. You can initialize an optional argument to a default value when you declare the argument, so that if the optional argument is not included when the procedure is called, the default value is used. If you don’t initialize the argument to a default value, Microsoft Access initializes it as it would initialize a variable of that type. If the argument is a number type, then it is initialized to zero. If it is a string, then it is initialized to a zero-length string ("").
In the following example, if a value is not passed for the optional argument, this argument is assigned the default value of 100.
Sub DisplayError(strText As String, Optional intNumber As Integer = 100)
If intNumber = 100 Then
MsgBox strText
Else
MsgBox intNumber & ": " & strText
End If
End Sub
You can call the procedure with either of the following lines of code.
DisplayError "Invalid Entry"
DisplayError "Invalid Entry", 250
Note If an optional argument is of type Variant, then you can use the IsMissing function to determine whether an optional argument was included when the procedure was called. The IsMissing function only works with arguments of type Variant.
To write a procedure that accepts an arbitrary number of arguments, use the ParamArray keyword to pass an array of arguments with the Variant data type. With the ParamArray keyword, you can write functions like Sum, which calculates the sum of an arbitrary number of arguments.
Function Sum(ParamArray varNumbers() As Variant) As Double
Dim dblTotal As Double, var As Variant
For Each var In varNumbers
dblTotal = dblTotal + var
Next var
Sum = dblTotal
End Function
You can call the Sum function with the following line of code.
dblSum = Sum(1, 3, 5, 7, 8)
Unless you specify otherwise, general procedures you create are public, meaning that you can call them from anywhere in your application.
Tip If you know you will use a procedure only within its module, you should declare it with the Private keyword to avoid confusion and to speed up compilation of your code. Event procedures are always declared with the Private keyword, because they normally apply only to the form or report in which they are stored. For information on procedures declared with the Private keyword, search the Help index for “Private statement.”
When you call a procedure that isn’t in the current module, Microsoft Access searches other modules and runs the first public procedure it finds that has the name you called. If the name of a public procedure isn’t unique in the database, you can specify the module it’s in when you call the procedure. For example, to run a Sub procedure called DisplayMsg that’s stored in a module called Utility, you use the following code:
Utility.DisplayMsg
You can call procedures in a class module from other modules as well. To do this, specify the name of the class module along with the procedure name. For example, to run a Function procedure called AddValues in a class module called Class1 and print the result to the Debug window, use the following code:
Debug.Print Class1.AddValues
Because form and report modules are also class modules, you call a procedure in a form or report module in the same way. To call a procedure in a form or report module, specify the name of the form or report module along with the procedure name. The name of the form or report module includes the qualification Form_ or Report_ followed by the name of the form or report. For example, to run a Sub procedure called DisplayRecords that’s stored with the Orders form, use the following code:
Form_Orders.DisplayRecords
Alternatively, you can call a procedure in a class module or a form or report module by referring to an object variable that points to an instance of either the class or the form or report. For example, the following code opens an instance of the Orders form, and then runs the DisplayRecords procedure.
Dim frmOrders As New Form_Orders ' Declare an object variable.
frmOrders.Visible = True ' Open and display the Orders form.
frmOrders.DisplayRecords ' Call the form's procedure.
.
.
.
Set frmOrders = Nothing ' Close the new instance of the Orders form.
By storing the DisplayRecords procedure in the Orders form module and making it public, you in effect create a custom method of the Orders form.
Sub DisplayRecords
' This procedure can be called from another form
' to cause the Orders form to update itself.
.
.
.
End Sub
Often you store values temporarily when performing calculations with Visual Basic. For example, you may want to calculate several values, compare them, and perform different operations on them, depending on the result of the comparison. You want to retain the values so you can compare them, but because you need to store them only while your code is running, you don’t want to store them in a table.
Visual Basic uses variables to store values. Variables are like fields except that they exist within Visual Basic rather than in a table. Like a field, a variable has a name, which you use to refer to the value the variable contains, and a data type, which determines the kind of data the variable can store. Before you use a variable, it’s a good idea to declare it with a Dim statement, which tells Microsoft Access to set aside space for the variable.
For example, in the following procedure, dtmAny, dtmYear, and dtmMonth are variables with the Date data type.
Function DueDate(dtmAny As Date)
Dim dtmYear As Date, dtmMonth As Date
dtmYear = Year(dtmAny)
dtmMonth = Month(dtmAny) + 1
DueDate = DateSerial(dtmYear, dtmMonth, 1)
End Function
See Also For information on declaring and using variables, see Chapter 4, “Working with Variables, Data Types, and Constants.”
While you are writing your Visual Basic code, you declare and name many elements (Sub and Function procedures, variables and constants, and so forth). The names of the procedures, variables, and constants you declare in your Visual Basic code must:
A keyword is a word that Visual Basic uses as part of its language. This includes predefined statements (such as If and Loop), functions (such as Len and Abs), methods (such as Close and FindFirst), and operators (such as Or and Mod).
Visual Basic has several commands that help you control the execution of your code. For example, you can define groups of statements that may or may not be run, depending on the value of an expression, or you can define groups of statements that Visual Basic runs repeatedly. You can also define groups of statements that each are applied to one or more objects in your application.
To run code conditionally, use the following statements:
To run one or more lines of code repetitively, use the following statements:
To apply one or more lines of code to an object or objects in your application, use the following statements:
See Also For information on using these statements, search the Help index for any keyword in the statement.
Many common actions you perform in an application don’t have a corresponding command in the Visual Basic language. To perform the equivalent of a macro action, use methods of the DoCmd object. The syntax for the DoCmd object is:
[Application.]DoCmd.method [arguments]
Replace method with the name of a macro action. How many and what kind of arguments come after method depends on the specific macro action you want to run. You list the arguments in the same order they appear in the Macro window, or you can use named arguments. Specifying the Application object is optional; you can start a line with the DoCmd object.
For example, the Close method, which corresponds to the Close action, takes two arguments that specify the type and name of the database object you want to close. You use commas to separate the arguments when a method takes multiple arguments.
DoCmd.Close acForm, "Add Products"
The first argument, acForm, is a Microsoft Access intrinsic constant specifying that the object to be closed is a form. Microsoft Access automatically declares a number of intrinsic constants that you can use to represent a variety of objects, actions, or data types. For example, you often use intrinsic constants with methods of the DoCmd object to specify action arguments that you can enter in the lower part of the Macro window.
See Also For information on intrinsic constants, see Chapter 4, “Working with Variables, Data Types, and Constants,” or search the Help index for “intrinsic constants.”
Some methods of the DoCmd object take optional arguments. If you leave these arguments unspecified, Microsoft Access uses their default values. For example, if you leave both arguments for the Close method unspecified, Microsoft Access closes the active database object (whatever it may be).
DoCmd.Close
If you omit an optional argument but specify an argument that follows that argument, you must include a comma as a placeholder for the omitted argument. For example, the syntax for the MoveSize method is:
DoCmd.MoveSize [right] [, down] [, width] [, height]
The following code uses the default (current) settings for its right and down arguments, while using the specified values for its width and height arguments.
DoCmd.MoveSize , , 5000, 3000
You can use methods of the DoCmd object to perform most macro actions, including the RunMacro action (which runs an existing macro as if it were a procedure). However, eight macro actions have no equivalent methods of the DoCmd object. In most cases, Visual Basic provides equivalent functionality with built-in statements or functions.
| Action | Visual Basic equivalent |
| AddMenu | No equivalent (but you can now create custom menus by using the command bars object model in Visual Basic) |
| MsgBox | MsgBox statement or function |
| RunApp | Shell function |
| RunCode | Function procedure call |
| SendKeys | SendKeys statement |
| SetValue | Assignment statement |
| StopAllMacros | Stop or End statements |
| StopMacro | Exit Sub or Exit Function statements |
See Also For information on methods of the DoCmd object, search the Help index for “DoCmd object.”
Occasionally, you may want your application to perform a command that’s on a Microsoft Access menu or toolbar. To perform a built-in command just as if the user clicked it, use the RunCommand method. The syntax for the RunCommand method is:
RunCommand command
Command is a constant that corresponds to the Microsoft Access command you want to run. For example, the following line of code performs the Options command, causing Microsoft Access to display the Options dialog box:
RunCommand acCmdOptions
See Also To determine the constant for the command you want to run, search the Help index for “RunCommand method.”