O'Reilly logo

Access 2003 VBA Programmer's Reference by Armen Stein, Graham Seach, Teresa Hennig, Patricia Cardoza

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.3. Using Variables

When using variables in your VBA code, there are several things to remember to ensure that your code runs smoothly. Choosing the appropriate data type for each variable is critical, and it's also important to use global variables correctly.

Naming conventions for variables are very important. In Appendix G, "Naming Conventions," the Reddick naming conventions for variables are described. If you get into the habit of consistently naming your variables, your code will be easier to maintain over time, faster to debug, and will look more professional.

8.3.1. 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required