The Access Wizard Newsletter
Tips, Tricks and Traps for Access Users and Developers.


Get it Right Once and Every Time - Enums


There was once a group of monks whose vows required them to remain silent. The only exception was a ten-minute period every Sunday evening beginning and ending with the tolling of a bell. Because their talking time was so precious, they had worked out systems to talk as succinctly as possible.
When Sunday night rolled around for a newly arrived monk, Brother John, he was bursting to talk. At the gong, Brother John started jabbering away. Suddenly, one of the older monks shouted “17!”
All the monks, except for Brother John, exploded with laughter. Then another monk addressed the group with “32!”  The monks burst into laughter once again, tears of merriment streaming down their faces.
The new monk turned to Brother Phil on his right and said, “What’s going on?”
Brother Phil explained “Since we have such a short time to talk, we have a list of jokes that we have all memorized. By using numbers, we can get more of them into our 10 minutes of talk time. Here’s a list for you to memorize.”
Young Brother John took the list and memorized the jokes. The next Sunday night when the gong sounded, he immediately jumped up and yelled “22!” which was followed by dead silence.
He turned to Brother Phil and said “I don’t get it, 22 is a funny joke!” Brother Phil replied, “Well, some people can tell a joke, and some people can’t.”


Enum - The Purpose

Like in the apocryphal monastery where numbers represented jokes, there is an analogous feature in Access called Enums. Enums are the flip side of monastery scenario; you use words to represent numbers.
Say you are running a pet shop and you have a series of different types of dogs, as in the following table:
DogTypeKey DogType
1 German Shepherd
2 French Poodle
3 Lhasa Apso
4 Bulldog
5 Boxer
6 Silly little teacup dog
When dogs come into your inventory, they get classified into one of the breeds from the table. You know that French Poodles have the need for a special diet, accommodations, and grooming. So anytime a Poodle comes in, you want to make sure that your application gives you an alert.
You would essentially write code along the lines of
If DogTypeKey = 2  then
            Send out some type of alert
End if
Further, say that you wanted different types of alerts if the dog was a Boxer or a German Shepherd.  You could write code that uses the keys of 5 for Boxers and 1 for German Shepherds.
Keeping track of all those keys is not easy, and what you would probably end up doing is opening the table to remind yourself what each key represents.
However, there is an easier way, and it involves using enums. As mentioned above, enums are words that translate into numbers.

Setting it Up

The way you would set things up is to put the following into a code module as follows:
Enum DogType
    GermanShepherd = 1
    FrenchPoodle = 2
    LhasaApso = 3
    Bulldog = 4
    Boxer = 5
    SillyTeacupDog = 6
End Enum
 Important things to note:
  • You start with the word Enum followed by whatever you want to call your list. In this case, I used DogType, but I could have used DogBreeds, or whatever I wanted.
  • You end with an End Enum Statement
  • You then assign a word for each corresponding number that you want to substitute.
  • The order of elements is not important.
  • Spaces are forbidden for both the name of your list and for every element.

Testing and Putting It into Practice

 Once you have the code in place, you can test it by opening the immediate window (Ctrl-G is a shortcut) and typing “? DogType” followed by a period.
If you have set things up correctly, you will see the VBA auto sense kick in and you will get a drop down of the elements declared in your enum list.

At this point, if you select Bulldog and hit return, you will see the program returns the number 4.

From here on, you no longer have to remember the particular numbers associated with your dog types, you can just use the word DogType followed by a period and just choose the one you want. Access in turn will convert your selection to the appropriate number.


The Enum technique can make your life a lot simpler and it has few bounds. Although I used it to refer to the Primary keys in my Dog Type table, you can use it any place you need a number and would prefer to see words as you type.
If the behavior of the drop down as you're coding looks familiar, it should. It is similar to the technique the native VBA intellisense uses to help you speed though your coding process.

   Trap of the Month – Make Sure you Test Enums 

Enums help you code more efficiently. However, it is imperative that you test them. If you put an Enum in place and it has a bug, a space within a list element for example, none of your code will work. This is different than a typical bug that will only present itself when you compile the code or your program encounters that particular chunk of code.
The fix is usually simple, but its best to fix it immediately rather than have your program just explode.

Tip of the Month: Easy Ways to Select List Items

In this month's Wizard, we showed a way of creating an onboard list of elements that you can use in your code. When you do, a list pops up. There are three ways you can choose from the list: 
  1. Use your mouse to select the element..
  2. Use your down arrow key to select the element.
  3. Keep typing until your selected item shows up and then hit return. There's no need to type the whole word, just enough to get it to float to the top.
These techniques also work for combo boxes in Access forms.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886

Custom Software · 3 Stonebolt Way · Westford, MA 01886