Home

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 and you must remember them exactly. Instead of just remembering that the constant 1 represents Single Family, you can create a list that has that type of house. In another list, instead of using 1 again, you can give it a name. Consequently, in each list, although the constant 1 would still be considered, at least it would mean something 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, you 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 enumerator 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:
    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
  2. Return to Microsoft Access
  3. In the Navigation Pane, double-click House Inspection
  4. Click the Display button:

    The Values of Members of an Enumeration

  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
  6. Return to Microsoft Access and click the Display button:

    The Values of Members of an Enumeration

  7. Save and close the form
  8. Return to Microsoft Visual Basic and access the InspectionTools module
  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
  12. Return to Microsoft Access and, in the Navigation Pane, double-click Listing Examination

    The Values of Members of an Enumeration

  13. Click the Display button:

    The Values of Members of an Enumeration

  14. Return to Microsoft Visual Basic and access InspectionTools module
  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
  16. Return to Microsoft Access and click the Display button:

    The Values of Members of an Enumeration

  17. Return to Microsoft Visual Basic

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

Access a Member by its Value

Remember that each member of an enumeration has a specify 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

Adding Members by Addition

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 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 three 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
  4. Return to Microsoft Access and, in the Navigation Pane, double-click House Inspection
  5. Click the button on the form

    Returning an Enumeration From a Function

  6. Return to Microsoft Visual Basic

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
  6. Return to Microsoft Access and, in the Navigation Pane, double-click Employment Hiring
  7. Click the Create button

    Involving an Enumeration With a Class

  8. Save and close the form

An Enumerated Property

Remember that an enumeration is primarily an integral group of value. 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
    
    Public Enum RoadType
        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
        RoadName = name
    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()
        Dim rd As Road
        
        Set rd = New Road
        
        rd.RoadName = "I-10"
        rd.Category = RoadType.Interstate
        rd.Distance = 2460.34
        rd.Description = "From the Pacific Ocean in Santa Monica, CA, to  Jacksonville, FL"
        
        txtRoadName1 = rd.RoadName
        txtRoadType1 = rd.Category
        txtDistance1 = CStr(rd.Distance) & " miles"
        txtLocation1 = rd.Description
        
        Set rd = New Road
        
        rd.RoadName = "PA 581"
        rd.Category = RoadType.CapitalBeltway
        rd.Distance = 27.6
        rd.Description = "Surrounds Harrisburg, PA"
        
        txtRoadName2 = rd.RoadName
        txtRoadType2 = rd.Category
        txtDistance2 = CStr(rd.Distance) & " miles"
        txtLocation2 = rd.Description
        
        Set rd = Nothing
    End Sub
  5. Return to Microsoft Access and, in the Navigation Pane, double-click Employment Hiring

    Creating and Using an Enumerated Property

  6. On the form, click the button

    Creating and Using an Enumerated Property

  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
 
 
   
 

Previous Copyright © 2016, FunctionX, Inc. Next