Enumerations

Fundamentals of Enumerations

Introduction

Consider the following list:

 Integer Item 1 Female 2 Male 3 Unknown

When writing a program, you can use 1 to represent Female. You can use 2 to represent Male. Consider this other list:

 Integer Item 1 Single Family 2 Townhouse 3 Condominium 4 Unknown

This time, you can use 1 to represent a single family house and you can use 2 to represent a townhouse. One of the problems with these arrangements is that the numbers are vague, they can represent different things. So everytime you use one of those numbers, you must remember what a particular number represents. Instead of just remembering that the constant 1 represents Single Family, you can create a list where Single Family is 1. In another list, you can create another item that too, represents one. Consequently, in each list, the constant 1 would mean something more precise.

An enumeration is a series of constant integers that each has a specific position in the list and can be recognized by a meaningful name.

Practical Learning: Introducing Enumerations

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Altair Realtors1 database
3. On the Ribbon, click Create and click Module

Creating an Enumeration

To create an enumeration, use the Enum keyword, followed by the name of the enumeration, press Enter, and end with End Enum. Here is an example:

Enum HouseTypes

End Enum

By tradition or a good habit, an enumeration is usually created in the top section of a module.

The name of the enumeration and the name of each item of the list follows the rules for names of variables. The section between both lines is referred to as the body of the enumeration. In the body of the enumeration, type the name of each item on its own line.

Practical Learning: Creating an Enumeration

1. To create an enumeration, type the following code:
Enum HouseTypes
SingleFamily
Townhouse
Condominium
Unknown
End Enum
2. In the Properties window, click (Name) and type InspectionTools
3. In the Project window, expand Microsoft Access Class Objects if necessary and double-click Form_House Inspection

Using an Enumeration

After creating an enumeration, it becomes a normal data type and you can use it. For example, you can declare a variable for it, just like we have done so far to declare other variables. After initializing the variable, you can use its values wherever necessary.

Practical Learning: Using an Enumeration

1. In the Object combo box, select cmdDisplay
2. To declare a variable for an enumeration, type the following:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes
End Sub
3. To access a member of the enumeration, type the name of the variable, followed by a period, followed by the desired member of the enumeration. You can use that formula to initialize the variable.
To access a member of an enumeration, type the following:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes

houseType = HouseTypes.SingleFamily
End Sub

The Values of Members of an Enumeration

When you create the members of an enumeration, each member holds a value as a natural number. By default, 0 is assigned to the first member. 1 is assigned to the second member, and so on.

Practical Learning: Using the Values of Members of an Enumeration

1. To see the value of a member of an enumeration, change the code as follows:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes

houseType = HouseTypes.SingleFamily
txtPropertyType = houseType
End Sub
3. In the Navigation Pane, double-click House Inspection
4. Click the Display button:

5. Return to Microsoft Visual Basic and change the code as follows:
Private Sub cmdEnumeration_Click()
Dim HouseType As HouseTypes

HouseType = HouseTypes.Townhouse
txtPropertyType = HouseType
End Sub

7. Save and close the form
9. If you don't want the default values for the members, you can specify the value of one or each member of the enumeration. Suppose you want the SingleFamily member in the above enumeration to have a value of 5. To do this, use the assignment operator "=" to give the desired value.
Change the code as follows:
Enum HouseTypes
SingleFamily = 5
Townhouse
Condominiium
Unknown
End Enum
10. In the Project window, double-click Form_Listing Examination
11. In the Object combo box, select cmdDisplay and implement the event as follows:
Private Sub cmdDisplay_Click()
txtMember1 = HouseTypes.SingleFamily
txtMember2 = HouseTypes.Townhouse
txtMember3 = HouseTypes.Condominium
txtMember4 = HouseTypes.Unknown
End Sub

13. Click the Display button:

15. If you want, you can assign the same value to more than one member of an enumeration. The numbers you assign don't have to be consecutive. This means that you can randomly assign the numbers. For an example, change the code as follows:
Enum HouseTypes
SingleFamily = 5
Townhouse = 12
Condominiium = 8
Unknown
End Enum

Enumerations Visibility

By default, if you create an enumeration the way we have proceeded so far, it would be available only in the module to which it belongs. To control an enumeration's accessibility, you can precede it with the Private or the Public keyword.

Practical Learning: Setting the Visibility of an Enumeration

• Type Public before Enum
Public Enum HouseTypes
SingleFamily = 5
Townhouse = 12
Condominiium = 8
Unknown = 114
End Enum

Techniques of Access the Members of an Enumeration

Qualifying a Member

Microsoft Access provides various options to access the members of any enumeration. You have three primary options. As seen so far, to access a member of an enumeration, you can precede the member with the name of the enumeration and a period, Here are examples:

Private Sub cmdDisplay_Click()
txtMember1 = HouseTypes.SingleFamily
txtMember2 = HouseTypes.Townhouse
txtMember3 = HouseTypes.Condominium
txtMember4 = HouseTypes.Unknown
End Sub

Accessing a Member by its Name

Starting with the name of the enumerator makes it easy to locate the member you want. Otherwise, if you already know the member you want to access, you can simply type it. Here are examples:

Private Sub cmdDisplay_Click()
txtMember1 = SingleFamily
txtMember2 = Townhouse
txtMember3 = Condominium
txtMember4 = Unknown
End Sub

Accessing a Member by its Value

Remember that each member of an enumeration has a specific number. You can access a member by using that number. Here is an example:

Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes

houseType = 12
End Sub

Some operations require that you access two members at the same time. One option is to add the members by names. Here is an example:

Private Sub cmdDisplay_Click()
txtPropertyType = HouseTypes.SingleFamily + Townhouse
End Sub

If you know the numeric values of the members that interest you, you can add them. You have three options:

• You can add a member by name to the value of another member. Here is an example:
Private Sub cmdDisplay_Click()
Dim category As HouseTypes

category = Condominium + 28
End Sub
• You can add the value of a member to another member by name. Here is an example:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes
Dim category As HouseTypes

houseType = 12 + SingleFamily
category = Condominium + 28
End Sub
• You can add the constant values of the members. Here is an example:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes
Dim category As HouseTypes
Dim topic As HouseTypes

houseType = 12 + SingleFamily
category = Condominium + 28
topic = 25 + 28
End Sub

BIT Adding the Members of an Enumeration

Since the variables and members of an enumeration are stored in the computer memory, some operations require that you add the values based on their memory locations. To support this operation, the Visual Basic language provides a binary operator named Or. You can use this operator to add the members of an enumeration. The Or operator operates on bits and not on regular numeric constant values. As a result, it doesn't produce a value of an algebraic addition. Still, as seen for the + operator, you have many options:

• You can add two members by name. Here is an example:
Private Sub cmdDisplay_Click()
Dim category As HouseTypes

category = SingleFamily Or Condominium
End Sub
• You can add a member by name to the value of another member. Here is an example:
Private Sub cmdDisplay_Click()
Dim category As HouseTypes

category = Condominium Or 28
End Sub
• You can add the value of a member to another member by name. Here is an example:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes
Dim category As HouseTypes

houseType = 12 Or SingleFamily
category = Condominium + 28
End Sub
• You can add the constant values of the members. Here is an example:
Private Sub cmdDisplay_Click()
Dim houseType As HouseTypes
Dim category As HouseTypes
Dim topic As HouseTypes

houseType = 12 + SingleFamily
category = Condominium + 28
topic = 25 Or 28
End Sub

Enumerations and Functions

Returning an Enumeration From a Function

To create a function that returns an enumeration, specify its As factor with the name of the enumeration. In the function, do whatever you want. Before exiting the function, indicate the value it returns.

Practical Learning: Returning an Enumeration From a Function

1. Change the InspectionTools module as follows:
Public Enum HouseTypes
Unknown = 0
SingleFamily
TownHouse
Condominium
End Enum

Public Function SpecifyHouseType() As HouseTypes
Dim propType As HouseTypes

propType = SingleFamily

SpecifyHouseType = propType
End Function
2. In the Project window, double-click Form_House Inspection
3. You can call a function that returns an enumeration type by using just its name. And you can use its returned value as you see fit. For an example, change the code of the event as follows:
Private Sub cmdDisplay_Click()
Dim recognize

recognize = SpecifyHouseType()

txtPropertyType = recognize
End Sub
5. Click the button on the form

Passing an Enumeration as Argument

An enumeration can be passed as argument to a procedure. You pass an enumeration as argument using the same approach of a normal data type. Here is an example:

Enum HouseTypes
Unknown = 0
SingleFamily = 12
TownHouse = 27
Condominium = 248
End Enum

Private Sub PresentPropertyType(ByVal habitat As HouseTypes)

End Sub

In the body of the procedure, you can use the enumeration as you see fit. When calling the procedure, make sure you pass a value that is of the enumeration type. Here is an example:

Option Compare Database
Option Explicit

Enum HouseTypes
Unknown = 0
SingleFamily = 12
TownHouse = 27
Condominium = 248
End Enum

Private Function SpecifyHouseType() As HouseTypes
Dim propType As HouseTypes

propType = HouseTypes.TownHouse

SpecifyHouseType = propType
End Function

Private Sub PresentPropertyType(ByVal habitat As HouseTypes)
txtPropertyType = habitat
End Sub

Private Sub cmdEnumerator_Click()
Dim recognize

recognize = SpecifyHouseType()

PresentPropertyType recognize
End Sub

In the same way, you can pass as many enumeration types as parameters. You can also create the parameter as optional. Here is an example:

Option Compare Database
Option Explicit

Enum HouseTypes
Unknown = 0
SingleFamily = 12
TownHouse = 27
Condominium = 248
End Enum

Private Function SpecifyHouseType() As HouseTypes
Dim propType As HouseTypes

propType = 5

SpecifyHouseType = propType
End Function

Private Sub PresentPropertyType(Optional ByVal habitat As HouseTypes = Condominium)
txtPropertyType = habitat
End Sub

Private Sub cmdEnumerator_Click()
PresentPropertyType
End Sub

Enumerations and Classes

An Enumeration as a Member of a Class

In a class module, you can create a member that is based on an enumeration. Of course, you must first decide what enumeration you want to use. If you want to use your own enumeration, you can create it directly in the class module. Once that is done, you can declare a variable from it.

Practical Learning: Involving an Enumeration With a Class

1. On the Ribbon, click File and click Open
2. In the list of files, click Business Starter from Lesson 5
3. In the Navigation Pane, double-click Employee and add the following code:
Public Enum EmploymentStatus
Unknown = 0
PartTime = 12
FullTime = 25
Seasonal = 120
End Enum

Public EmployeeNumber As Long
Public FirstName As String
Public LastName As String
Public Status As EmploymentStatus
Public HourlySalary As Double
4. Once the variable exists, you can use it by respecting the rules of enumerations. As an example, in the Project window, double-click Form_Employment Hiring
5. In the Object combo box, select cmdCreate and implement the event as follows:
Private Sub ShowEmployee(staff As Employee)
txtEmployeeNumber = staff.EmployeeNumber
txtFirstName = staff.FirstName
txtLastName = staff.LastName
txtHourlySalary = staff.HourlySalary
txtEmploymentStatus = staff.Status
End Sub

Private Sub cmdCreate_Click()
Dim staff As New Employee

Set staff = New Employee

With staff
.EmployeeNumber = 38405
.FirstName = "Jeannette"
.LastName = "Gibson"
.HourlySalary = 14.88
.Status = FullTime
End With

ShowEmployee staff

Set staff = Nothing
End Sub
7. Click the Create button

8. Save and close the form

An Enumerated Property

Remember that an enumeration is primarily an integral group of values. This means that a property of type Enum follows the same rules as a property for an integer. Of course, before creating the property, you should decide what enumeration you will use. You can create the enumeration in the same class module that will use the property. You can then create the property.

Practical Learning: Creating and Using an Enumerated Property

1. Open the Road System1 database from the previous lesson
2. In the Navigation Pane, double-click Road and change the class module as follows:
Dim name As String
Dim length As Single
Dim summary As String

Unknown
StateHighway
Interstate
FederalHighway
CapitalBeltway
End Enum

Public Property Let RoadName(ByVal value As String)
name = value
End Property

Public Property Get RoadName() As String
End Property

Public Property Let Category(ByVal value As RoadType)
cat = value
End Property

Public Property Get Category() As RoadType
Category = cat
End Property

Public Property Let Distance(ByVal value As Single)
length = value
End Property

Public Property Get Distance() As Single
Distance = length
End Property

Public Property Let Description(ByVal value As String)
summary = value
End Property

Public Property Get Description() As String
Description = summary
End Property
3. In the Project window, double-click Form_Road Inspections
4. In the Object combo box, select cmdDescribe and implement the event as follows:
Private Sub cmdDescribe_Click()

rd.Distance = 2460.34
rd.Description = "From the Pacific Ocean in Santa Monica, CA, to  Jacksonville, FL"

txtDistance1 = CStr(rd.Distance) & " miles"
txtLocation1 = rd.Description

rd.Distance = 27.6
rd.Description = "Surrounds Harrisburg, PA"

txtDistance2 = CStr(rd.Distance) & " miles"
txtLocation2 = rd.Description

Set rd = Nothing
End Sub

6. On the form, click the button

7. Save everything and close the form

Enumerations of Built-In Classes and Objects: The Current Project

One of the characteristics of an application is that it holds a project. For example, when you are working on a database, you are also said to be working on a project. In fact, a database you have opened and are working on is referred to as the current project. To identify the current project in your code, the Application object is equipped with a property called CurrentProject. The expression you would use is:

Application.CurrentProject

To refer to the current project, you can declare a variable of type Object and initialize it with the Application.CurrentProject object. This would be done as follows:

Private Sub Detail_Click()
Dim CurrentDatabase As Object

Set CurrentDatabase = Application.CurrentProject
End Sub

Because the Application object is always implied in your code, you can omit it and simply usel CurrentProject.

After initializing or getting the current project, you can use it as you see fit. That is, you can access its properties. One of the properties of the CurrentProject object is called FullName. This property gives you the name of the current database and where it is located. Here is an example of using it:

Private Sub Detail_Click()
Dim CurrentDatabase As Object

Set CurrentDatabase = Application.CurrentProject
txtFilename = CurrentDatabase.FullName
End Sub

To get only the path to the current database, that is, where the database is located, you can use the Path property.

Besides its path, another important piece of information you may want to know about a database its its type, whether it is a regular Microsoft Access database or a project that is linked to a Microsoft SQL Server database. To allow you to get this information, the CurrentProject class is equipped with a property named ProjectType. When accessed, this property produces one of three values. They are:

 ProjectType Value Name Description 0 acNull Unknown type 1 acADP This is a project  database 2 acMDB This is a regular Microsoft Access database

As you may be aware, Microsoft Access has been released in various versions, including Microsoft Access 2.0, Microsoft Access 95, Microsoft Access 97, Microsoft Access 2000, Microsoft Office Access 2002 (a member of Microsoft Office XP), Microsoft Office Access 2007, Microsoft Office Access 2010, Microsoft Access 2013, and the current Microsoft Access 2016. When using a database, probably that someone else created, you may not know what version (of the database engine) the current database is made for. To assist you with getting this information, the CurrentProject class is equipped with a property named FileFormat. When accessed, this property produces the type of database of the current project. The values of this property are:

 FileFormat Value Name Type of Database 2 acFileFormatAccess2 Microsoft Access 2.0 7 acFileFormatAccess95 Microsoft Access 95 8 acFileFormatAccess97 Microsoft Access 97 9 acFileFormatAccess2000 Microsoft Access 2000 10 acFileFormatAccess2002 Microsoft Access 2002 12 acFileFormatAccess12 Microsoft Office Access 2007 14 acFileFormatAccess14 Microsoft Office Access 2010

Practical Learning: Ending the Lesson

• Close Microsoft Access