Microsoft Access Database Development With VBA

Introduction to Collections

 

A Collection of Objects

 

Introduction

Besides considering each object in its own right, objects can be grouped, that is, considered together. This is the basis of a collection. A collection is a series of objects that share the same structure but each can be described using different values. Here is an example of a collection of people and some description about each:

Property/Type Person
Category Kid Adult Adult Teen Adult
Gender Female Male Female Male Female
Doing What Drawing Hunting Speaking Fishing Reading

Notice that, what constitutes this collection is that each item share many basic characteristics with the others. For example, all of them are human beings. All of them are doing something.

Creating and Using a Collection

To support collections, the Visual Basic language has a class named Collection. To formally create a collection, declare a variable of type Collection:

Private Sub Detail_Click()
    Dim colPersons As Collection
End Sub

After declaring the variable, you must initialize it and allocate memory for it. To initialize it, use the Set operator to identify the variable. To allocation memory for the variable, use the New operator followed by the name of the class: Collection. Here is an example:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
End Sub

As done in the real worlds, Microsoft Access also heavily relies on collections to manage the objects of a database. To make this possible, most of the collections you will use need have already been created, are available in each database, and Microsoft Access knows how to find them. To distinguish them, each collection is recognized with a name. For example, all of the forms of a database belong to a collection named AllForms. There are many other collections. In many tasks of your database development, you will usually need to know what collection an object belongs to. We will always specify the collection.

Each of the collections you will use has its own implementation (its own interpretation) of the Collection class. As we will review the Collection class in the next few sections, some of the collections have different names for the properties and methods of the Collection class. Some of the classes also have a (usually slightly) different behavior compared to the same functionality from the Collection class. Still, once you have a good idea of how the Collection class works, you should be able to use any built-in collection class.

Adding Items to a Collection

If you create your own collection, after declaring the variable, it is empty. To make it useful, you must add items to it. To support this operation, the Collection class is equipped with a method named Add. Its syntax is:

Collection.Add(Item, [Key], [Before], [After])

The only required thing you must provide is the item you want to add. If you are only interested in adding the new item and do not need to get any value back, you can omit the parentheses. Here is an example:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
End Sub

In the same way, you can add as many items as you want. Here are examples:

Private Sub Detail_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
End Sub

As mentioned already, many collections have different versions of the names of methods in the Collection class. For example, the combo box has built-in collection functionality. Its method used to add items to its list is called AddItem. Other than that, the functionality is the same. Here is an example of adding items to a combo box named 

Private Sub Detail_Click()
    cbxPersons.AddItem "Hermine Poussaint"
    cbxPersons.AddItem "Laurent Tigers"
    cbxPersons.AddItem "Gertrude Simms"
    cbxPersons.AddItem "James Cranston"
    cbxPersons.AddItem "Paula Aurora"
End Sub

(If you want to do this, you must set its Row Source Type to Value List).

Accessing an Object of a Collection

After an item has been added to a collection, or once an object exists in a series, you can retrieve it and eventually use it as you see fit. You can use the collection as a whole or you may want to access only one of its items. To give you access to an item, The Collection class, and each built-in collection, has a property called Item (the name of this property may be different from one collection to another but in most cases, it is called Item). This property is flexible with the way it allows you to access a member of its collection.

A member of a collection is accessed using its index and you have two main choices. Consider our People collection. Each item of that collection has a numeric position, also called its index. The first item has a position or index of 1. The second has an index of 2 and so on:

Property/Item
Index 1 2 3 4 5

To access an item using its numeric index, type the name of the collection, followed by the period operator, followed by Item with an opening and a closing parentheses. In the parentheses of Item, enter the index of the item that you want to access. For this example, imagine that you want to access the first little girl item, you would use the following statement:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
    txtPerson = People.Item(1)
End Sub

To access the lady overwhelmed with work and submerged in papers, you would use:

People.Item(3)

Based on the design of the Visual Basic language, Item is the default property of a collection. Based on this, you do not have to use the Item name to access the item. You can directly apply the parentheses on the name of the collection. Here is an example:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint"
    People.Add "Laurent Tigers"
    People.Add "Gertrude Simms"
    People.Add "James Cranston"
    People.Add "Paula Aurora"
    
    txtPerson = People.Item(3)
End Sub

You may already think of problems that would occur when trying to access an item by its index. For example, you must know with certainty what item is stored at a particular position and Microsoft Access has no way of giving this information (but you can use VBA to find out, with code). The reason is that you may have created the collection, so you, not Microsoft Access, should know what item was stored where in the collection.

Imagine someone, or something, changes the order of items in the collection:

Property/Item
Index 1 2 3 4 5

 

Notice that the indexes have not changed (because the indexes never change) but the previous indexes do not point to the same objects anymore. This means that if you use an index again to locate an item, you would get the wrong item. Fortunately, the Collection class provides a solution to this problem.

When we reviewed the ability to add items to a collection, we saw that the syntax of the Add() method was:

Collection.Add(Item, [Key], [Before], [After])

The second argument allows you to create a type of tag and apply it to each item. This tag is like an identification. It can be used to identify an item using something else than its index. This tag is called a key.

To create a key when adding an item, pass a second string to the method. The string can be anything you want but you should not make it too complicated. Here are examples where we used to initial of each person's name to create its corresponding key:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint", "HP"
    People.Add "Laurent Tigers", "LT"
    People.Add "Gertrude Simms", "GS"
    People.Add "James Cranston", "JC"
    People.Add "Paula Aurora", "PA"
End Sub

Based on this, if you know the key of an item, you can use it instead of the index to get the item. To use the key, apply an opening and a closing parentheses to the name of the collection. In the parentheses, use the key, as a string. Here is an example:

Private Sub cmdSelectPerson_Click()
    Dim People As Collection
    Set People = New Collection
    
    People.Add "Hermine Poussaint", "HP"
    People.Add "Laurent Tigers", "LT"
    People.Add "Gertrude Simms", "GS"
    People.Add "James Cranston", "JC"
    People.Add "Paula Aurora", "PA"
    
    txtPerson = People("JC")
End Sub

When you use a key, even if the collection changes, for example, if the items are moved by their positions, when you refer to one by its name, Microsoft Access would look for the item that has that key, regardless of its position.

Accessing an Item of a Microsoft Access Collection

To access a certain object that belongs to a collection, first type the name of a collection, such as Forms. Then, type the exclamation point operator "!", followed by the name of the object that belongs to the collection. This object is considered a parent but it can be referred to as a container because it "contains" other objects. For example, as we will see in future lessons, a form as a container can container one or more controls such as text boxes or list boxes. Therefore, this container could be a form named Customers. After the name of the form, type the exclamation point operator again "!", followed by the name of an object that is positioned. This object could be a text box named txtLastName. Once you have the object, you can then access any of its properties using the period operator and the name of the property as we saw in the previous section. Here is an example that changes the background color of a control named Text2 that is positioned in a form named Form1 that is part of the collection of forms named Forms of the current database:

Private Sub cmdChangeColor_Click()
    Forms!frmMain!Text3.BackColor = 39759
End Sub

As mentioned earlier, it is not unusual to have the name of an object made of more than one word. In this case, always remember to enclose the name of an object in square brackets. Based on this, the above code would be written:

Private Sub cmdChangeColor_Click()
    [Forms]![frmMain]![Text3].BackColor = 39759
End Sub

The Count of Items

One of the pieces of information you can get from a collection is the number of its members. To give you access to the number of items of a collection, the Collection class is equipped with a property named Count. To get the number of items of a collection, type the name of the collection, followed by the period operator, followed by the name of the count property. Here is an example:

People.Count

In most, if not all cases, the Count property is read-only. This means that you cannot change it and therefore you cannot assign a value to it. You can only retrieve the value stored in the Count property.

Removing an Item From a Collection

If you have an item in a collection but do not need that item anymore, you can delete it. To support the ability to remove an item from a collection, the Collection class is equipped with a method named Remove. Its syntax is:

Remove(Index)

When calling this method, pass the index of the item you want to delete. If you pass an index that does not exist, you would receive an error.

Programmer-Defined Data Types

 

Introduction

The built-in data types we have used so far allow you to declare a variable of a specific known type. Alternatively, you can create a new data type by using one of the above or by combining some them to get a new one. To do this, you must create a new module for the new type. You start the new type with the Type keyword followed by the name of the new type. The create of the type ends with the End Type expression:

Type SampleType
      
End Type

Between the Type line and the End Type line, you can declare one or more existing types as variables. That is, each declaration can be made of a name for a variable, followed by As, and followed by a known data type. Here is an example:

Type Employee
    DateHired As Date
    FullName As String
    IsMarried As Boolean
    HourlySalary As Double
End Type

Using a Programmer-Defined Data Type

After creating the type, in the procedure or event where you want to use it, declare a variable based on it. To access any of the member variables of the type, enter the name of its variable, followed by a period operator, and followed by the name of the member variable. After accessing a member variable of a type, you can initialize, change its value, or assign it to another variable. Here is an example:

Private Sub cmdCreate_Click()
    Dim Contractor As Employee
    
    Contractor.DateHired = #12/4/2000#
    Contractor.FullName = "Leslie Abramson"
    Contractor.IsMarried = True
    Contractor.HourlySalary = 20.15
    
    txtDateHired = CStr(Contractor.DateHired)
    txtFullName = Contractor.FullName
    chkIsMarried.Value = Contractor.IsMarried
    txtHourlySalary = Contractor.HourlySalary
End Sub 

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 arrangement 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. To create an enumeration, you use the Enum keyword, followed by the name of the enumeration, press Enter, and end with End Enum:

Enum HouseTypes

End Enum

The name of the enumerator and the name of each item of the list follows the rules we reviewed for names. 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. The name follows the same rules we reviewed for other names. Here are examples:

Enum HouseTypes
    SingleFamily
    Townhouse
    Condominiium
    Unknown
End Enum

Using an Enumeration

After creating an enumeration, you can use it. To do this, declare a variable for it, just like we have done so far to declare other variables. Here is an example:

Private Sub cmdEnumeration_Click()
    Dim HouseType As HouseTypes
End Sub

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. Here is an example:

Private Sub cmdEnumeration_Click()
    Dim HouseType As HouseTypes
    
    HouseType = HouseTypes.Condominiium
End Sub

After initializing the variable, you can use its values wherever necessary. Here is an example:

Private Sub cmdEnumeration_Click()
    Dim HouseType As HouseTypes
    
    HouseType = HouseTypes.Townhouse
    txtFilename = HouseType
End Sub

The Values of Members of an Enumeration

When you create a variable by listing the members in its body, each members holds a value of a natural number. By default, 0 is assigned to the first member. 1 is assigned to the second member, and so on. Consider the above code:

Private Sub cmdEnumeration_Click()
    Dim HouseType As HouseTypes
    
    HouseType = HouseTypes.Townhouse
    txtFilename = HouseType
End Sub

This produces 1 because Townhouse is the second member of the enumeration. 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. The enumerator would be:

Enum HouseTypes
    SingleFamily = 5
    Townhouse
    Condominiium
    Unknown
End Enum

In this case, SingleFamily now has a value of 5, Townhouse now has a value of 6, and Condominium has a value of 7. If you want, you can also assign a value to more than one member of an enumeration. Here is an example:

Enum HouseTypes
    SingleFamily = 5
    Townhouse = 12
    Condominiium = 8
    Unknown
End Enum

Notice that you don't have to assign a value to each member, and you don't have to assign an incremental number to each member. If you omit to assign a value to a member, the member gets the value of the previous member + 1. In the above code, SingleFamily has a value of 5. Townhouse has a value of 12. Condominium has a value of 8 and Unknown has a value of 9.

Enumerations Visibility

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

Private Enum HouseTypes
    SingleFamily = 5
    Townhouse = 12
    Condominiium = 8
    Unknown = 114
End Enum
 

 

 
 
 

	

Fundamental Built-In Collections and Objects

 

Introduction to Built-In Objects

Microsoft Access heavily relies on built-in objects and already created collections. These collections are made in entities we call classes. Besides the collections and objects, there are also many enumerations.

One of the characteristics of a class is that it has properties. Many of the properties only provide values. Some other properties are collections themselves. This means that when you access such a property, you actually get a collection, not a value.

A property that holds a collection is called an indexed property. As mentioned already, when you access an indexed property, you get a collection of items. To access an item inside the collection, you can apply the parentheses of the Item property. This would give you access to one of the items in the actual collection. In some cases, most of the members of the collection have already been created. In this case, you can use an index to access an item in the collection. In some other cases, objects are continually added to, or removed from, the collection. You can still use an index to access a member of the collection. In most cases, you will know the type you are accessing. In some other cases, after retrieving an item using its index, you will be able to enquire about the object in order to identify it. Fortunately, if you know the name of the item, you can access it using that name, which makes the access safer and precise.

When it comes to their values, properties can be considered in two categories: read-only and read/write. A property is referred to as read-only when you can only retrieve its value. You cannot change it. A read/write property is one that can receive a value or its value can be retrieved, if it exists already. Most read/write properties have a default value. This means two things. On one hand, before accessing a read/write property, you should have assigned a value to it, which means at the time you are retrieving the value, you should know (exactly what value the property is holding). On the other hand, if you did not previously specify a value, the property may have a default value assigned by the designers of the class. In most cases, this default property is suitable in most scenarios.

The Object Browser

There are so many objects, collections, classes, and enumerations that it is unrealistic to memorize all of them. Instead, you will use many available help shortcuts. For example, while you are working in Microsoft Visual Basic, you can take a look at a list of available objects and enumerations. To get this list, you can use the Object Browser.

To display the Object Browser:

  • On the main menu, click View -> Object Browser
  • On the Standard toolbar, click the Object Browser button Object Browser

Any of these actions would display the Object Browser:

Object Browser

We saw in Lesson 1 that Microsoft Access ships with many libraries. Each library has its own set of objects, collections, classes, and enumerations. When using the Object Browser, to see the objects of a certain library, in the Project/Library combo box, select the desired library:

Object Browser

After selecting a library, its objects, collections, and enumerations appear in two columns in the body of the window:

Object Browser

To explore (to see the members of) an object or a collection, click it in the left column. The properties and methods of the selected item would appear in the right column:

Object Browser

To get some information about an item, whether from the left or the right column, click it. The bottom frame of the window gives some details about the selected item. For example, to know the syntax of a method, after locating and clicking its class in the left column, click that method in the right column:

Object Browser

If an item appears as a link (bold and green) in the bottom frame, you can click it. This would select it in the left column.

The Object Object

As mentioned earlier and as you will find out for the rest of our lessons, a Microsoft Access database is made of various objects and various types of objects. All of these have in common that they are primarily considered as objects. To represent them, each object is of type Object. Sometimes, before using an object, you may not know exactly what particular type it would be. In these cases, you can declare a variable of Object type. Here is an example:

Private Sub Detail_Click()
    Dim AppObject As Object
End Sub

After declaring the variable, you can then initialize it with the desired object. In various sections of our lessons, we will see examples of various objects you can use.

The Application Object

When you create a database using Microsoft Access, you are said to have created an application. To identify the database you are currently using or that is opened, Microsoft Visual Basic provides the Application object. The Application object contains all of the object that you create or use and that belong to the database you are working on.

If you are planning to refer to the current database in your code, you can directly use the Application object. If you want to refer to a database other than the one that is currently opened, you should first declare an Application variable. To refer to a database as an application, you can declare a variable of type Object and initialize it with the Application object. Here is an example:

Private Sub Detail_Click()
    Dim AppObject As Object
    
    Set AppObject = Application
End Sub

After initializing the variable, you can use it as you see fit. 

One the properties of the Application object is called Application. This property gives you access to the current application, which ultimately gives you access to all objects used in the current database.

To assist you with getting the name of the current database, the Application class is equipped with a property named CurrrentObjectName. This property simply produces the name of the database that called with, without the extension. If you want to know the complete path to the database, we will see how to get it in the next section.

To let you know the Microsoft Access version of the current application, the Application class is equipped with a property named Version. In Microsoft Access 2007, this property produces 12.0.

The Current Project

One of the characteristics of an application is that it holds a project. For example, when you are working in 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 call 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 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, and Microsoft Office Access 2010. 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

The Current Database

When working in a Microsoft Access application, the database that is currently opened is identified as the CurrentDb object. This object is a child of (it is a property of) the Application object. The CurrentDb object allows you to refer to the current database in your code.

One of the ways you can get a reference to the current database is to declare a variable of type Object and Set it to the CurrentDb object. This can be done as follows:

Private Sub cmdReference_Click()
    Dim curDatabase As Object
    
    Set curDatabase = CurrentDb
End Sub

Alternatively, and this is necessary if you are working outside of Microsoft Access, first declare a variable of type Application or Access.Application, then assign that the CurrentDb property of the Application object by qualifying it. This can be done as follows:

Private Sub cmdReference_Click()
    Dim curDatabase As Object
    Dim curApplication As Application
    
    Set curDatabase = curApplication.CurrentDb
End Sub

The Objects in the Current Database

While using an application, the objects that belong to the database are stored in a collection called CurrentData. The CurrentData object itself is a property of the Application object. To access the CurrentData object, you can first call the Application object, type the period operator, and type CurrentData. This would be done as follows:

Application.CurrentData

The CurrentData object holds the collections of objects that belong to the database.

An Access Object

There are various types of objects you will use in your databases. Each object belongs to a particular collection. Still, to generally identify these various objects, each is identified as an AccessObject. This means that the AccessObject object is used to identify an object that belongs to a collection, whatever that object is and whatever its parent collection is.

The DoCmd Object

Another one of the most regularly used properties of the Application class is called DoCmd. This property is used to carry many of the actions you will need. An action performed on a database is also called a command.

The methods of the DoCmd are:

AddMenu ApplyFilter Beep CancelEvent Close
CopyDatabaseFile CopyObject DeleteObject DoMenuItem Echo
FindNext FindRecord GoToControl GoToPage GoToRecord
Hourglass Maximize Minimize MoveSize OpenDataAccessPage
OpenDiagram OpenForm OpenFunction OpenModule OpenQuery
OpenReport OpenStoredProcedure OpenTable OpenView OutputTo
PrintOut Quit Rename RepaintObject Requery
Restore RunCommand RunMacro RunSQL Save
SelectObject SendObject SetMenuItem SetWarnings ShowAllRecords
ShowToolbar TransferDatabase TransferSpreadsheet TransferSQLDatabase TransferText

Because the DoCmd is equipped with so many methods, we will review the necessary ones as each is introduced at the appropriate time.

 
 
   
 

Previous Copyright © 2002-2015, FunctionX, Inc. Next