Word Document Properties

Listing 9-6
Using Keyed Data with a Collection
Public Sub DisplayContacts()
‘ Create a loop counter variable.
Dim Counter As Integer
Counter = 1
‘ Create a recordset object.
Dim CurrentData As DAO.Recordset
(continued)
217
Chapter 9: Working with Arrays and Collections
15_046500 ch09.qxp 12/5/06 5:36 PM Page 217

---

Listing 9-6
(continued)
‘ Create the collection variable.
Dim ContactList As Collection
Set ContactList = New Collection
‘ Create the output variables.
Dim Element As Variant
Dim Output As String
‘ Get the current data.
Set CurrentData = _
Application.CurrentDb.OpenRecordset(“Contacts”)
‘ Create the collection from the data.
While Not CurrentData.EOF
‘ Get the information and place it in the user
‘ data type.
ContactList.Add _
CurrentData.Fields(“Name”).Value, _
“Name” + CStr(Counter)
ContactList.Add _
CurrentData.Fields(“Telephone”).Value, _
“Telephone” + CStr(Counter)
ContactList.Add _
CurrentData.Fields(“LastContact”).Value, _
“LastContact” + CStr(Counter)
‘ Update the Counter.
Counter = Counter + 1
‘ Move to the next database record.
CurrentData.MoveNext
Wend
‘ Create an output string by getting the values from
‘ the collection.
For Counter = 1 To (ContactList.Count / 3)
‘ Access the collection elements by name.
Element = ContactList(“Name” + CStr(Counter))
Output = Output + Element
Element = ContactList(“Telephone” + CStr(Counter))
Output = Output + vbTab + Element
Element = ContactList(“LastContact” +
CStr(Counter))
Output = Output + vbTab + CStr(Element) + vbCrLf
218
Part III: Expanding Your VBA Horizons 
15_046500 ch09.qxp 12/5/06 5:36 PM Page 218

---

Next
‘ Create the message box.
MsgBox Output, vbInformation, “Contact List”
End Sub
The code begins by defining and initializing some variables. Notice the
CurrentData
declaration. It’s a 
DAO.Recordset
, not the default
ADODB.Recordset
object. When you use the default, the program fails with
a type mismatch error. This example points out one of the problems that you
can encounter when working with objects. Make sure that you use specific
object references as necessary.
You can use a number of methods to get hold of the Contacts table in the exam-
ple database. The easiest method is to use the 
Application.CurrentDb.
OpenRecordset
method. This method includes constants that determine
how VBA opens the recordset. For example, you can tell VBA that you want
to read only the recordset by using the 
dbReadOnly
constant. See the
OpenRecordset Method help topic for additional information.
The code uses a 
While...Wend
structure to retrieve the individual data ele-
ments. Notice the use of a string to access the 
Fields
collection, which acts
as the first argument for the 
ContactList.Add
method. The second argument
for the 
Add
method used to add a new item to the 
ContactList
collection is
the string key that you can use to access it. When you look at 
ContactList
in the Debugger, you see that it contains a single list of entries. Make sure
that you use the 
CurrentData.MoveNext
method to select the next record
at the end of the loop, or else the database never reaches the end of the file
(the 
EOF
property set to 
True
).
In this case, the code relies on a 
For...Next
loop to create the output string.
The collection isn’t in a configuration where one element equals one database
record — the code requires three elements for each database record. The
code shows how you can create a loop to compensate for this fact.
The 
For...Next
loop expression isn’t actually optimal in this case. I use this
presentation to make the example clearer. Notice the 
ContactList.Count
/ 3
portion of the expression. VBA has to calculate that value during every
loop. Calculating the value outside the loop would save time and make this
program faster.
Accessing predefined collection items
VBA uses collections quite often. For example, the 
Fields
collection of a
database is a collection of 
Field
objects. Likewise, the 
Drives
collection
contains multiple 
Drive
objects.
219