Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with Constants

A

constant is a meaningful name given to a meaningless number or string. Constants can be used only for values that don't change at runtime. A tax rate or commission rate, for example, might be constant throughout your application. There are three types of constants in Access:

• Symbolic

• Intrinsic

• System defined

Symbolic constants , created by using the Const keyword, are used to improve the readability of your code and make code maintenance easier. Rather than referring to the number .0875 every time you want to refer to the tax rate, you can refer to the constant mccurTaxRate. If the tax rate changes, and you need to modify the value in your code, you'll make the change in only one place. Furthermore, unlike the number .0875, the name mccurTaxRate is self-documenting.

Intrinsic constants are built into Microsoft Access; they are part of the language itself. As an Access programmer, you can use constants supplied by Microsoft Access, Visual Basic, Data Access Objects (DAO), and ADO. You can also use constants provided by any object libraries you're using in your application.

There are only three system-defined constantsTrue, False, and Nulland they are available to all applications on your computer.

Working with Symbolic Constants

As mentioned, you declare a symbolic constant by using the Const keyword. You can declare a constant in a subroutine or function, or in the General section of a Form or Report module. You can strong-type constants in Access 2000 and above. The declaration and use of a Private constant looks like this:

Private Const TAXRATE As Currency = .0875

This code, when placed in a module's Declarations section, creates a Private constant called TAXRATE and sets it equal to .0875. Here's how you use the constant in code:

Function TotalAmount(curSaleAmount As Currency)
TotalAmount = curSaleAmount * TAXRATE
End Function

This routine multiplies the curSaleAmount, received as a parameter, by the constant TAXRATE. It returns the result of the calculation by setting the function name equal to the product of the two values. The advantage of the constant in this example is that the code is more readable than TotalAmount = curSaleAmount * .0875 would be.

Scoping Symbolic Constants

Just as regular variables have scope, user-defined constants have scope. In the preceding example, you created a Private constant. The following statement, when placed in a module's General Declarations section, creates a Public constant:

Public Const TAXRATE = 0.0875 As Currency

Because this constant is declared as Public, you can access it from any subroutine or function (including event routines) in your entire application. To better understand the benefits of a Public constant, take a case where you have many functions and subroutines all making reference to the constant TAXRATE. Imagine what would happen if the tax rate were to change. If you hadn't used a constant, you would need to search your entire application, replacing the old tax rate with the new tax rate. However, because your Public constant is declared in one place, you can easily go in and modify the one line of code where this constant is declared.

By definition, the values of constants cannot be modified at runtime. If you try to modify the value of a constant, you get this VBA compiler error:

Assignment to constant not permitted

Figure 7.13 illustrates this message box. You can see that an attempt is made to modify the value of the constant TAXRATE, which results in a compile error.

If you need to change the value at runtime, you should consider storing the value in a table rather than declaring it as a constant. You can read the value into a variable when the application loads, and then modify the variable if needed. If you choose, you can write the new value back to the table.

Working with Intrinsic Constants

Microsoft Access declares a number of intrinsic constants that you can use in Code, Form, and Report modules. Because they're reserved by Microsoft Access, you can't modify their values or reuse their names; however, you can use them at any time without declaring them.

You should use intrinsic constants whenever possible in your code. Besides making your code more readable, they make your code more portable to future releases of Microsoft Access. Microsoft might change the value associated with a constant, but Microsoft isn't likely to change the constant's name. All intrinsic constants appear in the Object Browser; to activate it, simply click the Object Browser tool on the Visual Basic toolbar. To view the constants that are part of the Access library, select Access from the Object Browser's Project/Library drop-down list. Click Constants in the Classes list box, and a list of those constants is displayed in the Members Of 'Constants' list box (see Figure 7.14).

In the list shown in Figure 7.14, all the constant names begin with

ac . All VBA constants are prefixed with

vb , all Data Access Object constants are prefixed with

db , and all constants that are part of the Access language are prefixed with

ac . To view the Visual Basic language constants, select VBA from the Project/Library drop-down list and Constants from the Classes list box. If the project you are working with has a reference to the ADO library, you can view these constants by selecting ADODB from the Project/Library drop-down list. Click <globals>. A list of the ADODB constants appears (these constants have the prefix

ad ).

Another way to view constants is within the context of the parameter you're working with in the Code window. Right-click the name of a parameter and select List Constants to display the constants associated with the parameter.