Naming variables

Every programming language has its own rules for naming variables. In VBA, a variable name must meet the following conditions:

• It must begin with an alphabetical character.
• It must have a unique name. The variable's name cannot be used elsewhere in the procedure or in modules that use the variables.
• It must not contain spaces or punctuation characters (except underscore).
• It must not be a reserved word, such as Sub, Module, or Form.
• It must be no longer than 255 characters.

Although you can make up almost any name for a variable, most programmers adopt a standard convention for naming variables. Some common practices include the following:

• Using a mix of uppercase and lowercase characters, as in TotalCost.
• Using all lowercase characters, as in counter.
• Separating the parts of a variable's name with underscores, as in Total_Cost.
• Preceding the name with the data type of the value. A variable that stores a number might be called iCounter, while a variable holding a string might be named sLastName.

The “Using a naming convention with variables” section later in this chapter goes into greater detail about the benefits of a naming convention.

When you need to use the contents of a variable, you simply reference its name. When you specify the variable's name, the computer program goes into memory, finds the variable, and gets its contents for you. This process means, of course, that you need to be able to remember and correctly reference the name of the variable.

Declaring variables

There are two principle ways to add variables to your applications. The first method—called implicit declaration—is to let VBA automatically create the variables for you. As with most things that are not carefully controlled, you'll find that letting VBA prepare your variables for you is not a particularly good idea and can lead to performance issues in, and problems debugging, your programs (see the “Comparing implicit and explicit variables” section later in this chapter).

Implicit declaration means that VBA automatically creates an Empty variable for each identifier it recognizes as a variable in an application. In the following, there are two implicitly declared variables—sFirstName and sLastName. In this example, two variables (sFirstName and sLastName) are assigned the text contained in two text boxes (txtFirstName and txtLastName), and a third variable (sFullName) is assigned the combination of sFirstName and sLastName, with a space between them.

Private Sub Combine_Implicit()


  sFirstName = Me.txtFirstName.Text
  sLastName = Me.txtLastName.Text
  sFullName = sFirstName & Space(1) & sLastName


End Sub

The second approach is to explicitly declare them with one of the following keywords: Dim, Static, Private, or Public (or Global). The choice of keyword has a profound effect on the variable's scope within the application and determines where the variable can be used in the program. (Variable scope is discussed in the “Understanding variable scope and lifetime” section later in this chapter.)

The syntax for explicitly declaring a variable is quite simple:

Dim VariableName As DataType
Static VariableName As DataType
Private VariableName As DataType
Public VariableName As DataType

In each case, the name of the variable and its data type are provided as part of the declaration. VBA reserves the amount of memory required to hold the variable as soon as the declaration statement is executed. Once a variable is declared, you can't change its data type, although you can easily convert the value of a variable and assign the converted value to another variable.

The following example shows the Combine_Implicit sub rewritten to use explicitly declared variables:

Private Sub Combine_Explicit()


  Dim sFirstName As String
  Dim sLastName As String
  Dim sFullName As String


  sFirstName = Me.txtFirstName.Text
  sLastName = Me.txtLastName.Text
  sFullName = sFirstName & Space(1) & sLastName


End Sub

So, if there's often very little difference between using implicit and explicit variables, why bother declaring variables at all? The following code demonstrates the importance of using explicitly declared variables in your applications:

Private Sub Form_Load()


  sDepartment = "Manufacturing"
  sSupervisor = "Joe Jones"
  sTitle = "Senior Engineer"


  'Dozens of lines of code go here


  Me.txtDepartment = sDepartment
  Me.txtSupervisor = sSuperviser
  Me.txtTitle = sTitle


End Sub

In this example code, the txtSupervisor text box on the form is always empty and is never assigned a value. A line near the bottom of this procedure assigns the value of an implicitly declared variable named Superviser to the txtSupervisor text box. Notice that the name of the variable (Superviser) is a misspelling of the intended variable (Supervisor). Because the source of the assignment appears to be a variable, VBA simply creates a new variant named Superviser and assigns its value (which is, literally, nothing) to the txtSupervisor text box. And, because the new Superviser variable has never been assigned a value, the text box always ends up empty. Misspellings such as this are very common and easy to overlook in long or complex procedures.

Furthermore, the code shown in this example runs fine and causes no problem. Because this procedure uses implicit variable declaration, Access doesn't raise an error because of the misspelling, and the problem isn't detected until someone notices the text box is always empty. Imagine the problems you'd encounter in a payroll or billing application if variables went missing because of simple spelling errors!

When you declare a variable, Access sets up a location in the computer's memory for storing a value for the variable ahead of time. The amount of storage allocated for the variable depends on the data type you assign to the variable. More space is allocated for a variable that will hold a currency amount (such as $1,000,000) than for a variable that will never hold a value greater than, say, 255. This is because a variable declared with the Currency data type requires more storage than another variable declared as a Byte data type. (Data types are discussed later in this chapter, in the “Working with Data Types” section.)

Even though VBA doesn't require you to declare your variables before using them, it does provide various declaration commands. Getting into the habit of declaring variables is good practice. A variable's declaration assures that you can assign only a certain type of data to it—always a numeric value or only characters, for example. In addition, you attain real performance gains by pre-declaring variables.

Dim [VariableName] [As DataType]

Dim iBeeps As Integer

The Public keyword

To make a variable available to all modules in the application, use the Public keyword when you declare the variable. Figure 25.2 illustrates declaring a public variable.

Although you can declare a public variable in any module, the best practice for declaring public variables is to declare them all in a single standard module that's used only to store public variables. Public variables, while necessary in some cases, should be limited. Because any procedure in your project can change a public variable's value, it can be difficult to find which procedure is making an unwanted change. With all the publically declared variables in one place, it's easy to locate them, and it's easy to see if you're using too many and may need to rethink your code structure.

Comparing implicit and explicit variables

The default data type for VBA variables is Variant. This means that, unless you specify otherwise, every variable in your application will be a Variant. As you read earlier in this chapter, although useful, the Variant data type is not very efficient. Its data storage requirements are greater than the equivalent simple data type (a String, for instance), and the computer spends more time keeping track of the data type contained in a Variant than for other data types.

Here's an example of how you might test for the speed difference when using implicitly declared Variant variables and explicitly declared variables. This code is found behind frmImplicitTest in Chapter25.accdb.

'Use a Windows API call to get the exact time:
Private Declare Function GetTickCount _
    Lib "kernel32" () As Long


Private Sub cmdGo_Click()


  Dim i As Integer
  Dim j As Integer
  Dim snExplicit As Single


  Me.txtImplicitStart.Value = GetTickCount()


  For o = 1 To 10000
    For p = 1 To 10000
      q = i / 0.33333
    Next p
  Next o


  Me.txtImplicitEnd.Value = GetTickCount()


  Me.txtImplicitElapsed.Value = _
  Me.txtImplicitEnd.Value - Me.txtImplicitStart.Value


  DoEvents 'Force Access to complete pending operations


  Me.txtExplicitStart.Value = GetTickCount()


  For i = 1 To 10000
    For j = 1 To 10000
      snExplicit = i / 0.33333
    Next j
  Next i


  Me.txtExplicitEnd.Value = GetTickCount()


  Me.txtExplicitElapsed.Value = _
    Me.txtExplicitEnd.Value - Me.txtExplicitStart.Value
  DoEvents


End Sub

In this small test, the loop using implicitly declared variables required approximately 2.7 seconds to run, while the loop with the explicitly declared variables required only 2.5 seconds. This is a performance enhancement of approximately 10 percent just by using explicitly declared variables.

The actual execution time of this—or any—VBA procedure depends largely on the relative speed of the computer and the tasks the computer is executing at the time the procedure is run. Desktop computers vary a great deal in CPU, memory, and other resources, making it quite impossible to predict how long a particular bit of code should take to execute.

Forcing explicit declaration

Access provides a simple compiler directive that forces you to always declare the variables in your applications. The Option Explicit statement, when inserted at the top of a module, instructs VBA to require explicit declaration of all variables in the module. If, for example, you're working with an application containing a number of implicitly declared variables, inserting Option Explicit at the top of each module results in a check of all variable declarations the next time the application is compiled.

Because explicit declaration is such a good idea, it may not come as a surprise that Access provides a way to automatically ensure that every module in your application uses explicit declaration. The Editor tab of the Options dialog box (shown in Figure 25.3) includes a Require Variable Declaration check box. This option automatically inserts the Option Explicit directive at the top of every module created from this point in time onward.

The Require Variable Declaration option doesn't affect modules already written. This option applies only to modules created after this option is selected, so you'll have to type the Option Explicit statement yourself in existing modules. Require Variable Declaration is not set by default in current versions of Access. You must set this option yourself to take advantage of having Access add Option Explicit to all your modules.

Using a naming convention with variables

Like most programming languages, applications written in VBA tend to be quite long and complex, often occupying many thousands of lines of code. Even simple VBA programs may require hundreds of different variables. VBA forms often have dozens of different controls on them, including text boxes, command buttons, option groups, and other controls. Keeping track of the variables, procedures, forms, and controls in even a moderately complicated VBA application is a daunting task.

One way to ease the burden of managing the code and objects in an application is through the use of a naming convention. A naming convention applies a standardized method of supplying names to the objects and variables in an application.

The most common naming convention used in Access applications uses a one- to four-character prefix (a tag) attached to the base name of the objects and variables in a VBA application. The tag is generally based on the type of control for controls and the type of data the variable holds or the scope for variables. For example, a text box containing a person's last name might be named txtLastName, while a command button that closes a form would be named cmdClose or cmdCloseForm.

The names for variables follow a similar pattern. The string variable holding a customer name might be named sCustomer, and a Boolean variable indicating whether the customer is currently active would be bActive.

Using a naming convention is not difficult. Most of the code in this book uses one-character prefixes for variables and three-character prefixes for control names. The actual naming convention you use is not important. The important point is that you use the convention consistently. As you write more VBA code, the right convention for you will become obvious. Table 25.3 shows one naming convention.

One benefit to using shorter prefixes for variables and longer prefixes for controls is that it becomes easy to tell them apart when you're reading your code. Also note that more commonly used data types get the one-character prefixes. You'll typically use Booleans more often than Bytes, so a shorter prefix for Booleans saves typing.

Some developers don't use any prefixes for variables. There's nothing wrong with that. There are some advantages to using prefixes, however. The first advantage is that you can identify the data type at the point you're using the variable. It's easy to see that a statement like sCustomer = chkActive may cause a problem. You know sCustomer is a String data type and chkActive, being a check box control, returns a Boolean value. Another advantage is variable name uniqueness. Recall that the variable naming rules state that all variable names must be unique and that you can't use reserved keywords for variable names. That means that you can't have a Boolean variable named Print that determines whether to print a report. By using a prefix, bPrint does not violate any rules.

Including an additional prefix for the scope conveys similar advantages. Knowing the scope of the variable in the portion of code you're working helps debug the code when things go wrong. It also allows you to use similar variables with different scopes. For example, you could have a private module-level variable mbIsEnabled that applies to all the code in your module and still have a local procedure-level variable bIsEnabled for use in only that procedure.

One final advantage to a naming convention that uses a mix of uppercase and lowercase letters is that you can detect spelling errors in your variable names very quickly. VBA will change the case of the variable name to match the case you use when you declare it. If you declare a variable using Dim sFirstName As String and later type sfirstname = "Larry" (all lowercase), as soon as you complete that line of code, your variable will change to sFirstName = "Larry". That immediate feedback will help you catch spelling errors before they become problems.

Understanding variable scope and lifetime

A variable is more than just a simple data repository. Every variable is a dynamic part of the application and may be used at different times during the program's execution. The declaration of a variable establishes more than just the name and data type of the variable. Depending on the keyword used to declare the variable and the placement of the variable's declaration in the program's code, the variable may be visible to large portions of the application's code. Alternatively, a different placement may severely limit where the variable can be referenced in the procedures within the application.

Examining scope

The visibility of a variable or procedure is called its scope. A variable that can be seen and used by any procedure in the application is said to have public scope. A variable that is available to any procedure in one module is scoped private to that module. A variable that is usable by a single procedure is said to have scope that is local to that procedure.

There are many analogies for public and private scope. For example, a company is likely to have a phone number that is quite public (the main switchboard number) and is listed in the phone book and on the company's website; each office or room within the company might have its own extension number that is private within the company. A large office building has a public street address that is known by anyone passing by the building; each office or suite within that building will have a number that is private within that building.

Variables declared within a procedure are local to that procedure and can't be used or referenced outside that procedure. Most of the listings in this chapter have included a number of variables declared within the procedures in the listings. In each case, the Dim keyword was used to define the variable. Dim is an instruction to VBA to allocate enough memory to contain the variable that follows the Dim keyword. Therefore, Dim iMyInt As Integer allocates less memory (2 bytes) than Dim dMyDouble As Double (8 bytes).

The Public (or Global) keyword makes a variable visible throughout an application. Public can be used only at the module level and can't be used within a procedure. Most often, the Public keyword is used only in standard (stand-alone) modules that are not part of a form. Figure 25.4 illustrates variables declared with three very different scopes. This code can be found in the modScope module in Chapter25.accdb.

Every variable declared in the declarations section of a standard module is private to that module unless the Public keyword is used. Private restricts the visibility of a variable to the module in which the variable is declared. In Figure 25.4, the gsAppName variable declared with Public scope at the top of the module will be seen everywhere in the application while the mbIsComplete variable declared in the next statement is accessible only within the module. The sMessage variable is declared inside a procedure, so only that procedure can see it.

Misunderstanding variable scope is a major cause of serious bugs in many Access applications. It's entirely possible to have two same-named variables with different scopes in an Access VBA project. When ambiguity exists, Access always uses the “closest” declared variable.

Consider two variables named MyVariable. One of these variables is global (Public) in scope, while the other is a module-level variable declared with the Private keyword. In any procedure Access uses one or the other of these variables. In a module where MyVariable is not declared, Access uses the public variable. The private variable is used only within the module containing its declaration.

The problem comes when multiple procedures use a variable with the same name as the multiple-declared MyVariable. Unless the developer working on one of these procedures has diligently determined which variable is being used, a serious error may occur. All too easily, a procedure might change the value of a public variable that is used in dozens of places within an application. If even one of those procedures changes the public variable instead of a more local variable, a very difficult-to-resolve bug occurs.

Private Static Sub StaticTest()


  'Both variables are static because of the
  ' 'Static' keyword in the procedure declaration
  Static lStatic As Long
  Dim lLocal As Long


  lStatic = lStatic + 1
  lLocal = lLocal + 1


  Me.txtLocal.Value = lLocal
  Me.txtStatic.Value = lStatic


End Sub

Using constants

Constants differ from variables in one major respect: A constant's value never changes. The value is assigned to the constant when it's declared, and attempts to change that value in code will result in an error.

Declaring constants

Constants are declared with the Const keyword. The format of a constant declaration is as follows:

[Public | Private] Const constname [As type] = constvalue

Using constants improves the readability of your code. Constants can also aid in error-proofing your code if you use the same value in more than one place. Figure 25.5 shows a procedure, found in modConstants, that uses a constant.

If the procedure in Figure 25.5 did not use a constant for the discount rate, it might contain a line that looks like this:

dFinalPrice = dFullPrice * (1 – 0.15)

Because of the variable names, you might be able to decipher that 0.15 is a discount rate. By using a constant like dDISCOUNT, its purpose is obvious to anyone reading the code.

The scope and lifetime of a constant are very similar to variables. Constants declared inside a procedure are available only within that procedure. Constants declared with the Private keyword in a module are available to all the procedures in that module and none of the procedures in other modules. Global constants, declared with the Public keyword, are available throughout the project. The values of constants never change, so the Static keyword is not available, and unnecessary, when declaring constants.

dFinalPrice = dFullPrice * (1 – dDISCOUNT)

Sub DiscountedAmount2()


  Dim dFullPrice As Double
  Dim dFinalPrice As Double


  Const dDISCOUNT As Double = 0.15
  Const dDISCTHRESHOLD As Double = 5000
  Const sPROMPT As String = "The price is "


  dFullPrice = 8000


  If dFullPrice > dDISCTHRESHOLD Then
    dFinalPrice = dFullPrice * (1 - dDISCOUNT)
  Else
    dFinalPrice = dFullPrice
  End If


  MsgBox sPROMPT & dFinalPrice


End Sub

Working with arrays

An array is a special type of variable. One array variable actually holds multiple pieces of data. Instead of reserving one block of memory, like a variable, an array reserves several blocks of memory. The size of an array can be fixed or dynamic. With dynamic arrays, you can increase or decrease the size in a procedure. The code in this section can be found in the modArrays module in Chapter25.accdb.

Dim aCustomers(10) as Long

Dim aCustomers(1 to 10) as Long

Sub ArrayAssignment()


  Dim aCustomers(1 To 5) As Double


  aCustomers(1) = 0.2
  aCustomers(2) = 24.6
  aCustomers(3) = 7.1
  aCustomers(4) = 99.9
  aCustomers(5) = 14.7


End Sub

Sub ArrayRandom()


  Dim aRandom(1 To 5) As Double
  Dim i As Long


  For i = 1 To 5
    aRandom(i) = Rnd
  Next i


  For i = 1 To 5
    aRandom(i) = aRandom(i) * 10
  Next i


  For i = 1 To 5
    Debug.Print aRandom(i)
  Next i


End Sub

Dim aContact(1 to 10, 1 to 3) As String

Sub TwoDArray()


  Dim aPotus(1 To 2, 1 To 3)
  Dim i As Long


  aPotus(1, 1) = "George"
  aPotus(1, 2) = "Washington"
  aPotus(1, 3) = "1789-1797"
  aPotus(2, 1) = "John"
  aPotus(2, 2) = "Adams"
  aPotus(2, 3) = "1797-1801"


  For i = 1 To 2
    Debug.Print aPotus(i, 1) & Space(1) & aPotus(i, 2) & Space(1) & _
      "was President in the years" & Space(1) & aPotus(i, 3)
  Next i


End Sub

Dim aProductIDs() as Long

ReDim aProductIDs(1 to 100)

Sub FormArray()


  Dim aForms() As String
  Dim frm As Form
  Dim lFrmCnt As Long
  Dim i As Long


  If Application.Forms.Count > 0 Then
    ReDim aForms(1 To Application.Forms.Count)


    For Each frm In Application.Forms
      lFrmCnt = lFrmCnt + 1
      aForms(lFrmCnt) = frm.Name
    Next frm


    For i = LBound(aForms) To UBound(aForms)
      Debug.Print aForms(i) & " is open."
    Next i
  End If


End Sub

ReDim Preserve aCustomerIDs(1 to x) As Long

For i = LBound(aContacts) To UBound(aContacts)
  Debug.Print aContacts(i)
Next i

For i = LBound(aBounds, 1) To UBound(aBounds, 1)
  For j = LBound(aBounds, 2) To UBound(aBounds, 2)
    Debug.Print aBounds(i, j)
  Next j
Next i

Array(ParamArray ArgList() as Variant)

Sub ArrayFunction()


  Dim vaRates As Variant
  Dim i As Long


  vaRates = Array(0.05, 0.055, 0.06, 0.065, 0.07)


  For i = LBound(vaRates) To UBound(vaRates)
    Debug.Print vaRates(i)
  Next i


End Sub

Split(string_expression, [delimiter],[limit],[compare])

Sub TheSplitFunction()


  Dim vaWords As Variant
  Dim i As Long


  vaWords = Split("Now is the time.", Space(1))


  For i = LBound(vaWords) To UBound(vaWords)
    Debug.Print vaWords(i)
  Next i


End Sub

Join(source_array, [delimiter])

Sub TheJoinFunction()


  Dim sResult As String
  Dim aWords(1 To 5) As String


  aWords(1) = "The"
  aWords(2) = "quick"
  aWords(3) = "brown"
  aWords(4) = "fox"
  aWords(5) = "jumped"


  sResult = Join(aWords, Space(1))


  Debug.Print sResult


End Sub

Understanding where to create a procedure

You create procedures in one of two places:

• In a standard VBA module: You create a subroutine or function in a standard module when the procedure will be shared by code in more than one form or report or by an object other than a form or report. For example, queries can use functions to handle very complex criteria.
• Behind a form or report: If the code you're creating will be called only by a single procedure or form, the subroutine or function should be created in the form or report's module.

Calling VBA procedures

VBA procedures are called in a variety of ways and from a variety of places. They can be called from events behind forms and reports, or they can be placed in module objects and called simply by using their name or by using the Call statement. Here are some examples:

SomeSubRoutineName
Call SomeSubRoutineName
Somevalue = SomeFunctionName

Only functions return values that may be assigned to variables. Subroutines are simply called, do their work, and end. Although functions return a single value, both subroutines and functions can place values in tables, in form controls, or even in public variables available to any part of your program. You can see several examples of different ways to use subroutines and functions throughout this chapter.

The syntax used for calling subroutines with parameters changes depending on how you call the procedure. For example, when using the Call keyword to call a subroutine that includes arguments, the arguments must be enclosed in parentheses:

Call SomeSubRoutineName(arg1, arg2)

However, when the same procedure is called without the Call keyword it requires no parentheses:

SomeSubRoutineName arg1, arg2

Also, using the Call keyword with a function tells Access your code is not capturing the function's return value:

Call SomeFunctionName

Or, when arguments are required:

Call SomeFunctionName(arg1, arg2)

In this case, the function is treated as if it is a subroutine.

Creating subs

Conceptually, subroutines are easy to understand. A subroutine (usually called a sub and sometimes called a subprocedure) is a set of programming statements that is executed as a unit by the VBA engine. VBA procedures can become complex, so this elementary description of subroutines is quickly overwhelmed by the actual subroutines you'll compose in your Access applications.

Figure 25.6 shows a typical subroutine. Notice the Sub keyword that begins the routine, followed by the name of the subroutine. The declaration of this particular subroutine includes the Private keyword, which restricts the availability of this subroutine to the module containing the subroutine.

The subroutine you see in Figure 25.6 contains most of the components you'll see in almost every VBA sub or function:

• Declaration: All procedures must be declared so that VBA knows where to find them. The name assigned to the procedure must be unique within the VBA project. The Sub keyword identifies this procedure as a subroutine.
• Terminator: All procedures must be terminated with the End keyword followed by the type of procedure that is ending. In Figure 25.6, the terminator is End Sub. Functions are terminated with End Function.
• Declarations area: Although variables and constants can be declared anywhere in the body of the procedure (as long as it's before they're used), good programming conventions require variables and constants to be declared near the top of the procedure where they'll be easy to find.
• Statements: A VBA procedure can contain many statements. Usually, however, you'll want to keep your VBA procedures small to make debugging as painless as possible. Very large subroutines can be difficult to work with, and you'll avoid problems if you keep them small. Instead of adding too many features and operations in a single procedure, place operations in separate procedures, and call those procedures when those operations are needed.

At the conclusion of a subroutine, program flow returns to the code or action that originally called the sub. The subroutine shown in Figure 25.6 may be called from a form's Load event, so control is returned to that event.

As an example of a useful VBA subroutine, the next several paragraphs describe building an event procedure for a control on an Access form. This procedure retrieves a value from one of the cboCustomerID combo box columns and uses it to find a record. The RowSource of the cboCustomerID combo box is a SQL statement that returns the CustomerID and the Company fields. Here's the SQL statement:

SELECT DISTINCT tblCustomers.CustomerID, tblCustomers.Company
FROM tblCustomers
INNER JOIN tblSales
ON tblCustomers.CustomerID = tblSales.CustomerID
ORDER BY tblCustomers.Company;

The tblCustomers table is inner-joined with the tblSales table so that only those customers with an invoice are displayed in the combo box. The DISTINCT keyword is used so that each customer is only returned once.

The objective of this exercise is to learn about procedures, but it also serves to teach you some additional VBA commands. The code is added to the form as the cboCustomerID_AfterUpdate event.

To create an event procedure in a form, follow these steps:

1. Select the cboCustomerID control in frmSales Design view.
2. Press F4 to display the Property window for the control.
3. Click in the After Update event property in the Event tab of the Property Sheet and select [Event Procedure] from the event's drop-down list.
4. Press the builder button (…) to open the VBA code editor.
5. Enter the following code into the cboCustomerID_AfterUpdate event procedure, as shown in Figure 25.7. The following code goes between Private Sub cboCustomerID_AfterUpdate() and End Sub in the VBA code editor:

     Me.txtCustomerID.SetFocus
   
   
     If Not IsNull(Me.cboCustomerID.Value) Then
         DoCmd.FindRecord Me.cboCustomerID.Value
     End If
   
   
     Me.txtInvoiceDate.SetFocus

   

6. Select Compile Chapter25 from the Debug menu in the code editor to check your syntax.
7. Close the VBA window and return to the frmSales form.

The code first moves the focus to the txtCustomerID text box to make that field the current field. The Me. refers to the current form and substitutes in this example for Forms!frmSales!.

The first If statement checks to make sure a Customer ID was selected by making sure the current value of the combo box's bound column—CustomerID—is not null.

The heart of the procedure is the FindRecord method of the DoCmd object. FindRecord searches through the recordset and returns a record that matches the arguments. There are several arguments to FindRecord, but we supply only the first, FindWhat. The FindWhat argument is what FindRecord searches for through the records. In this case, it's searching for Me.cboCustomerID.Value. The other arguments to FindRecord are optional, and we have accepted the defaults. By setting the focus to Me.txtCustomerID, we made that field the current field. By default, FindRecord only searches in the current field, and setting the current field before calling FindRecord achieves our aims.

The final line of code sets the focus to the txtInvoiceDate text box. When the user locates a record, it's a good practice to set the focus to a good starting point for navigating through the record. While not required, it provides a good user experience.

Figure 25.7 shows the procedure created in the code editor after entering the procedure described earlier. After you finish entering these statements, press the Save button on the toolbar to save your code before closing the VBA window.

The procedure behind this form runs each time the user selects a different customer in cboCustomerID. This code shows the first invoice for that customer.

Handling parameters

Now, the question you should be asking is: Where did the lQuantity, cPrice, and dDiscount variables come from? The answer is simple. They're the parameters passed from another procedure, as you may have already guessed.

Parameters (often called arguments) passed to a procedure are treated like any other variable by the procedure. Parameters have a name and a data type and are used as a way to send information to a procedure. Parameters are often used to get information back from a procedure, as well.

The following table shows the names and data types of the arguments used in the CalcExtendedPrice function:

These parameter names can be anything you want them to be. Think of them as variables you would normally declare. All that's missing is the Dim statement. They don't have to be the same name as the variables used in the call to the function. Very often, you'll pass the names of fields in a table or controls on a form or variables created in the calling procedure as parameters to a procedure.

The completed CalcExtendedPrice function is shown in Figure 25.8. Notice how this function's parameters are defined in the function's declaration statement. The parameters are separated by continuation characters (a space followed by an underscore) to make the code easier to read.

Calling a function and passing parameters

Now that you've completed the function, it's time to test it.

Normally, a function call comes from a form or report event or from another procedure, and the call passes information as parameters. The parameters passed to a procedure are often variables or data taken from a form's controls. You can test this function by going to the Immediate window and using hand-entered values as the parameters.

Follow these steps to test the function:

1. Press Ctrl+G to display the Immediate window.
2. Enter ? CalcExtendedPrice(5, 3.50, .05). This statement passes the values as 5, 3.50, and .05 (5 percent) to the lQuantity, dPrice, and dDiscount parameters, respectively. CalcExtendedPrice returns 16.625 using those values, as shown in Figure 25.9.

   

3. Close the Immediate window and the VBA window to return to the Database window.

The next task is to use the function to calculate the extended price (price multiplied by quantity) of each item included in a sales invoice. You can add a call to the function from the Amount box on fsubSalesLineItems. This is a subform embedded on frmSales. Follow these steps:

1. Display the frmSales form in Design view.
2. Click into the fsubSalesLineitems subform.
3. Click into the txtAmount control in the subform.
4. Display the Property window and enter the following into the Control Source property, as shown in Figure 25.10: =CalcExtendedPrice (Nz(txtQuantity,0),Nz(txtPrice,0),Nz(txtDiscountPercent,0)).

   

This expression passes the values from three controls—txtQuantity, txtPrice, and txtDiscountPercent—in the subform to the CalcExtendedPrice function in the module and returns the value back to the control source of the txtAmount control each time the line is recalculated or any of the parameters change. The references to txtQuantity, txtPrice, and txtDiscountPercent are enclosed in calls to the Nz function, which converts null values to zero. This is one way to avoid Invalid use of null errors that would otherwise occur.

The sales form (frmSales) enforces a business rule that the extended price is recalculated any time the user changes the quantity, price, or discount on the sales form.

In Figure 25.10, notice that the Control Source property for txtAmount simply calls the CalcExtendedPrice function. The call does not specify the module that contains the function. Because CalcExtendedPrice was declared with the Public keyword, Access easily finds it and passes the required arguments to it.

Creating a function to calculate sales tax

In the Collectible Mini Cars application, whenever you add a line item to a sales invoice, you specify whether the item is taxable. The sales form adds up the extended prices for all the taxable line items to determine the sales tax for the sale. This total can then be multiplied by the tax rate to determine the tax.

The Collectable Mini Cars sales form (frmSales) includes a Text Box control for the tax amount. You can simply create an expression for the control's value such as:

=fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate

This expression references txtTaxableTotal in the subform (fSubSalesLineitems) and multiplies it by the tax rate (txtTaxRate) from the main form (frmSales).

However, although this expression displays the tax amount, the expression entered into the txtTaxAmount control would make the txtTaxAmount control read-only because it contains an expression. You wouldn't be able to override the calculated amount if you wanted to. The tax applied to a sale is one of the fields that needs to be changed once in a while for specific business purposes.

Better than using a hard-coded expression is creating a function to calculate a value and then place the value of the calculation in the control. This way, you can simply type over the calculated value if needed.

You can enter the following lines of code to the AfterUpdate events behind the txtQuantity, txtPrice, txtDiscountPercent, and chkTaxable controls. This way, each time one of those controls' values is changed, the tax is recalculated after the contact's tax rate is retrieved on the frmSales form.

txtTaxAmount = _
  fSubSalesLineitems.Form!txtTaxableTotal * txtTaxRate

Actually, better would be to place this statement in the AfterUpdate event of fsubSalesLineitems. This way, the tax is recalculated each time a value is updated in any record of this form. Because fsubSalesLineitems is displayed as a datasheet, the AfterUpdate event fires as soon as the user moves to another line in fsubSalesLineitems.

Although you can use a simple expression that references controls on forms and subforms, this technique works only behind the form containing the code. Suppose you also need to calculate tax in other forms or in reports. There's a better way than relying on a form.

This is an old developer's expression: “Forms and reports lie; tables never lie.” This means that the controls of a form or report often contain expressions, formats, and VBA code that may make a value seem to be one thing when the table actually contains a completely different value. The table containing the data is where the real values are stored, and it's where calculations and reports should retrieve data from.

You can easily use VBA code to extract data from a table, use the data in a complex calculation, and return the result to a control on a form, on a report, or to another section of code.

Figure 25.11 shows the completed CalcTax function.

The function is called from the AfterUpdate event behind the frmSalesLineitems subform. The CalcTax function calculates the sum of the taxable line items from the tblSalesItems table. The SQLstatement is combined with a bit of ADO code to determine the total. The calculated total amount is then multiplied by the dTaxPercent parameter to calculate the tax. The tax is set to the cReturn variable, which is set to CalcTax (the name of the expression) at the end of the function.

An important feature of this example code is that it combines data extracted from a database table (Quantity, RetailPrice, DiscountPercent) with data passed as parameters (dTaxPercent, lInvoiceNum). All the extraction and calculations are automatically performed by the code, and the user is never aware of how the tax amount is determined.