Access Hacks

Hack #88. Use Custom Enumerations

Use familiar names, instead of memorizing equivalent numbers, to avoid errors and speed up coding.

Having a list of properties and methods appear while typing really helps when you're coding. For example, entering Application in a code module and then entering a period opens up a list of methods and properties that belong to the Application object.

You can use this same helpful facility to provide you with constants that are particular to your project or business. Take, for example, an application that has to take the department code into account for some specific processing. Each department has a unique code number, but the numbers themselves are meaningless. This makes the code numbers difficult to remember.

That is where a set of enumerated variables comes in handy. Not only can you give names to the numerical code numbers, but the names become available in a list while typing.

Figure 8-31 shows a VBA code module. In the declaration section, I used the Enum statement to create the variables. I gave a name to the block (DepartmentCodes in this example) and used the End Enum statement to end the block.

Figure 8-31. Using Enum for more efficient coding

Within the Enum block, I assigned each department's code number to its name. Now, when coding within a procedure, all I have to do is type DepartmentName and enter a period. The enumerated variables ...