Introduction to Collections


Collections of Objects



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:

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

As done in the real worlds, Microsoft Access also heavily relies on collections to manage the objects of a database. To make this possible, all of the collections you will use 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 called 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.

Accessing an Object of a 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 Properties of a Collection



To make its use easy, a collection is primarily considered as an object. This means that a collection has characteristics, called properties, and can perform actions, called methods. Consider the above collection of people and let's call it People.

The Count of Items

One of the pieces of information you can get from a collection is the number of its members. This is sometimes referred to as a count. Some collections give this property a different name but in many cases, it is called 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.

The Item of a Collection

Once you have a collection, you can use it as a whole or you may want to access only one of its members. To support this, a (each) 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. Once again, 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 0. The second has an index of 1 and so on:

Index 0 1 2 3 4

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:


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


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. The other problem is that, if you, someone else, or something, changes the other of items in the collection, the index you were using may still be valid but it may not refer to the same object anymore. If you were using such a reference in an expression or in your code, after such a change, you could get unpredictable results.

Besides a position, each item of a collection usually has a name. Here are examples:

Index 0 1 2 3 4
Name Girl Man Woman Boy Reader

Notice that no two items have the same name. This is a rule you must observe when creating the items that belong to the same collection. In Microsoft Access, the name of an item is another type of index you can use to refer to a member of a collection. To refer to an item by its name, 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 name of the item as a string, that is, between double-quotes. For example, based on the above People collection, if you want to access the man carrying a gun, you can use a statement as follows:


To access the little boy holding a fish, you would use:


This time, 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 name, regardless of its position.

Fundamental Built-In Objects


Introduction to Built-In Objects

To lay a solid foundation and assist you in creating your applications, Microsoft Access ships with various pre-configured objects. These start with any primary thing that appears on the MS Access interface: the menus, the toolbars, and the Database window. Other objects are almost hidden but you can access them any time when needed.

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 Lesson 4, we will learn how to declare variables and how you can declare a variable for any type of object using the Object type.

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. In Lesson 3, we will learn how to declare a variable and how to initialize it.

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 in 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. To access it in your code, you can write:


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

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 the Application object. The CurrentDb object allows you to refer to the current database in your code.

The Objects in the Current Database

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.

An Access Object

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.

The DoCmd Object



When using a database, the user routinely performs actions such as opening or closing an object, etc. An action performed on a database is also called a command. To carry the various actions that can be performed on a database, Microsoft Access provides an object called DoCmd.

DoCmd Methods

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.

Accessories for Code Writing


The Indentation

Microsoft Visual Basic makes it very easy to write code by providing you skeleton code as we saw above, a very intuitive Code Editor, and other accessories. Indentation is a technique that allows you to write easily readable code. It consists of visually showing the beginning and end of a section of code. Indentation is done at various levels. For the code of an event, indentation consists of moving your code to the right side so that the only line to the extreme left are those of the Private and End Sub lines, unless specified otherwise.

The easiest and most common way to apply indentation consists of pressing Tab before typing your code. By default, one indentation, done when pressing Tab, corresponds to 4 characters. This is controlled by the Editor property page of the Options dialog box. To change it, on the main menu of Microsoft Visual Basic, you would click Tools -> Options and use the Tab Width text box of the Editor property page:

If you don't want the pressing of Tab to be equivalent to 4 characters, change the value of the Tab Width text box to a reasonable value and click OK. Otherwise, it is (strongly) suggested that you keep it to its default of 4 characters.

Practical LearningPractical Learning:  Using Indentation

  1. Click anywhere on the line that has NavigationButtons and press Home

  2. Press Tab to indent the line to the right

  3. In the same way, indent the other line of code:
    Private Sub Form_Load()
        NavigationButtons = False
        [Company Name].SetFocus
    End Sub


A comment is a piece of text in a code section that the database engine would not consider when reading your code. As such, a comment can be written any way you want.

In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
End Sub

Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
    Rem I can write anything I want on this line
End Sub

Comments are very useful and it is strongly suggested that you use them regularly. They can never hurt your code and they don't increase the size of your database. Comments can help you and other people who read your code to figure out what a particular section of code is used for, which can be helpful when you re-visit your code after months or years of not seeing it.

Line Continuation

It is usually suitable to write lines of code that are not too long. This makes it easy to read code and avoid scrolling left and right. In some cases, you will not have much choice but to create long expressions. Unlike many other languages such as C/C++/C#, Java, etc, Visual Basic doesn't allow to simply continue a line of code from one line to the next without alerting the compiler. Still, if a line of code becomes too long, there is a technique you can use to span on various lines.

To continue a piece of code from one line to the next, type an empty space followed by an underscore symbol, then continue your code on the next line.


Previous Copyright Yevol, 2007 Next