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

Working with Constants

A

constant is a meaningful name given to a number or string. You can use constants 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, 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, for instance, you can refer to the constant MTAXRATE ("M" indicating that it is a module-level constant). 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 MTAXRATE 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, and ActiveX Data Objects (ADO). You can also use constants provided by any object libraries you're using in your application.

There are only three system-defined constants available to all applications on your computer: True, False, and Null.

Defining Your Own 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, Report, or Class module. You can

strong-type constants, meaning that you can declare them with a data type. There are several naming conventions for constants. One of them is to use a suitable scoping prefix, the letter c to indicate that you're working with a constant rather than a variable, and then the appropriate tag for the data type. The declaration and use of a Private constant in the previous tax-rate example would look like this:

Private Const mccurTaxRate As Currency = .0875

I prefer using a scoping prefix and typing the name of the constant in all uppercase. Following this convention, the example given previously is changed to appear as follows:

Private Const MTAXRATE as Currency = .0875

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

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

This routine multiplies the curSaleAmount, received as a parameter, by the constant MTAXRATE. 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 Declarations section, creates a Public constant:

Public Const GTAXRATE As Currency = 0.0875

Because this constant is declared as Public, it can be accessed from any subroutine or function (including event routines) in your entire application. To better understand the benefits of a Public constant, suppose that you have many functions and subroutines, all referencing the constant GTAXRATE. 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 you declared your Public constant in one place, you can easily go in and modify the one line of code where you declared this constant.

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

Compile error: Assignment to constant not permitted

Figure 12.1 illustrates this message box. You can see that an attempt was made to modify the value of the constant called GTAXRATE, which resulted in a compile error.

If you must 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 it 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 while in the Visual Basic Editor (VBE). To view the constants that are part of the VBA language, select VBA 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 12.2).

All VBA constants are prefixed with vb; all ActiveX Data Object constants with ad; all Data Access Object (DAO) constants with db; and all constants that are part of the Access language with ac. In Chapter 7 in the section entitled "Tools for Working in the Visual Basic Editor."