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.
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.
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).
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:
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:
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:
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.
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
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:
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.
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:
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.
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
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
Consider the following list:
When writing a program, you can use 1 to represent Female. You can use 2 to represent Male. Consider this other list:
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
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
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.
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
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.
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:
Any of these actions would display the 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:
After selecting a library, its objects, collections, and enumerations appear in two columns in the body of the window:
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:
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:
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.
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.
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.
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:
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:
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), and Microsoft Office Access 2007. When using a database, probably that someone else created, you may not know what version (of 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:
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 the Application object. The CurrentDb object allows you to refer to the current database in your code.
In the next few lessons, we will learn different techniques of creating the objects that would belong to a 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:
The CurrentData object holds the collections of objects that belong to the database.
There are various types of objects you will use in your databases. As we will learn in different lessons, each object belong 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.
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:
Because the DoCmd is equipped with so many methods, we will review the necessary ones as each is introduced at the appropriate time.