Collections of Objects

Besides considering each object in its own right, objects can become characterized in groups or 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
Category	Kid	Adult	Adult	Teenager	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.

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.

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

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.

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:

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.

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 the People collection above. 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:

Property/Item
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:

People.Item(0)

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

People.Item(2)

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. 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 order 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:

Property/Item
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:

People.Item("Man")

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

People.Item("Boy")

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.

A typical application is made of various objects that a person uses to interact with the computer. To make this interaction possible without confusion, each object creates its own messages and sends them to the operating system. The messages are as varied as possible, so are the objects of an application. To reduce any type of confusion, each message must carry three to four pieces of information: the name of the object that composed the message, the type of message that must be dealt with, additional information provided by the object that sent the message.

Because the operating system cannot decide what type of action a particular object needs to perform at a particular time, each object is responsible to create its own messages. The first piece of information that an object must provide is its name. The reason is that there can be so many objects that are part of an application and there can be many applications opened at the same time.

The second piece of information that a message must carry is its type. There are many types of messages that a control can compose. Many controls can also send the same types of messages. The operating system is already aware of the various types of messages that are available and, most of the time, it knows the types of messages that a particular object can control, but the operating system cannot decide what type of message a control wants to compose at one particular moment. The types of messages are mostly known with particular names. In the operating system, the names of messages start with WM_. If you log to the MSDN web site and do a search on words that start with WM_, you would find out that there are many messages available but you will not need to memorize their names or to be aware of all of them. In fact, there are many messages you will hardly use.

After a control has composed a message, it must send it. The action of composing and sending a message is called an event. The action of actually sending a message is referred to as firing an event. To make it easy to identify an event, each event has a simplified name. When an object fires an event, the name of the event is appended to the name of the object. To distinguish the name of the object and the name of the event, there is an underscore between them. Examples are MainForm_Load or Customers_Dirty. As you will find out, the names of some events are made of more than one word. Examples are AfterUpdate or BeforeInsert. In this case, the name of the object and the name of the event are still added with an underscore between them. Examples are MainForm_AfterUpdate or Customers_BeforeInsert.

After composing a message and firing its event, when the operating system receives it, in order to perform the necessary action, in some cases, it (the operating system) may need additional information that the object must provide. The additional information is also referred to as parameter.

The additional information that an object must provide is typed in the parentheses of the event. Normally, the operating system is aware of the type of additional information that the object must provide. If the object doesn't provide this information, the operating system would use some default value(s). Because the type of additional information of an event is already decided, when you generate an event, Microsoft Visual Basic includes this information in the parentheses of the event. If the message doesn't require additional information, its parentheses are left empty. If it may need parameters, they are automatically included in its parentheses.

Because the structure of messages can be difficult to cope with, Microsoft Visual Basic has a simplified mechanism to help you code an event. Since an event is treated as a private matter, its code starts with the Private keyword. An event is treated as an action, like the methods we reviewed earlier. For this reason, it is created as a sub procedure and it must use the Sub keyword. After the Private Sub expression, follow the combination of the name of the object that is firing the event, the underscore, and the name of the event, and its parentheses with optional parameters depending on the event.

There are various ways you can initiate an event on an object of your database. If you are working in Microsoft Access and if you open a form or a report, you should access its Properties window in Design View. If the form or report "carries" the control whose event you want to program, you should also first open the form or report in Design View.

• First Technique
  

  1. If you want to write code that relates to the whole form or event, double-click its selection button which is at the intersection of the rulers .
     If you want to write code for a control, on the form or report, double-click the control to display its Properties window.

  2. Once the Properties window is displaying, you can click the Event property page and inspect the list of events:
      
     
     

  3. After locating the event you want, double-click it. The [Event Procedure] string will be added to the field. Once the [Event procedure] is displaying, click the ellipsis button to launch Microsoft Visual Basic. The keyboard caret would be positioned in the event and wait for you.

• Second Technique

  1. In the Properties window, click the name of the event in the Event property page to reveal its combo box

  2. Click the arrow of the combo box and click [Event procedure]

  3. Once [Event Procedure] is displaying for an event, click the ellipsis button of the event . This will launch Microsoft Visual Basic and will position the caret in the event's body, waiting for your coding instructions.

• Third Technique

  1. Right-click the form, report or control whose event you want to write code for.

  2. Click Build Event...

  3. On the Choose Builder dialog box, click Code Builder and click OK. This will launch Microsoft Visual Basic with the default event of the form, report, or control

• Fourth Technique

  1. First open a form or report in Microsoft Access

  2. To launch Microsoft Visual Basic, click the Code button

  3. In the Object combo box, select the form (or report) to launch its default event

  4. In the Procedure combo box, select the event you want.

Sometimes you or Microsoft Visual Basic will have inserted an event that you didn't want or that you don't want to program. If this happens, simply ignore the event: you do not have to delete it because if an event has been initiated but no code was written for it, the application will ignore it and use a default behavior.

1. To return to Microsoft Visual Basic, on the task bar, click Microsoft Visual Basic - Exercise1
2. Notice that the Object combo box is displaying the word Form.
   Click the arrow of the Procedure combo box to display the events of the form:
    
   
3. Click Open.
    
    
4. Click the arrow of the Procedure combo box again but this time, select ApplyFilter.
   Notice that the Load takes no parameter, the Open event takes one parameter, and the ApplyFilter takes 2 parameters
    
   
5. Click the arrow of the Object combo box and notice that Company_Name is in one word. Click Company_Name
6. Click the arrow of the Procedure combo box and select Change to generate that event