Using Appropriate Data Types and Sizes

First, make sure that your variable types will handle the size of data they are expected to store. Many overflow errors occur because an AutoNumber key value from a table was stored in a variable defined as an Integer. This may work fine during testing, because an integer can store numbers with values up to 32,767. Then, when a user starts adding more data, the application breaks on an overflow error.

It's a good idea to define variables with the maximum size that is possible to occur. AutoNumber fields should be stored in variables defined as Long (which is the same as the Long Integer in Access tables). Defining a field as String will allow it to store very long strings, whether they are defined as Text or Memo in a table.

If a variable can possibly contain a Null, then you must define it as a Variant, in which case it will be able to store just about anything that you throw into it—a messy approach, and one that takes Access a bit longer to process. It's usually better to decide what kind of data each variable is going to hold, then set the appropriate data type so that Access doesn't have to figure out what's in there every time it uses the variable. However, sometimes it's useful to allow a variable to contain a Null, especially when there might not always be data to load into the field. If you do use a Variant data type, use it because there's a specific reason that it might contain a Null, not because you don't know what type to make it.

If you don't specify a variable's data type, it will be a Variant by default. A common error is to define more than one variable on a single line of code, like this:

Dim strCallingForm, strReportTitle as String

Many novice VBA programmers think that both variables in this example will be defined as Strings, but they won't be. VBA requires that each and every variable have its data type defined. In this example, strCallingForm will be defined as a Variant because its data type wasn't mentioned.

A correct way to define the two string variables on one line is like this:

Dim strCallingForm as String, strReportTitle as String

This style is technically correct (both variables are defined as Strings), but the second variable is easy to miss when you are looking at your code. The clearest and most consistent style for defining variables is to give each one its own line:

Dim strCallingForm as String Dim strReportTitle as String

This may take an extra line of code, but it is much easier to read and understand.

0 0

Post a comment