Word Document Properties


Listing 9-6 Using Keyed Data with a Collection



Download 8,82 Mb.
Pdf ko'rish
bet181/207
Sana16.03.2022
Hajmi8,82 Mb.
#496217
1   ...   177   178   179   180   181   182   183   184   ...   207
Bog'liq
VBA For Dummies 5th Ed.pdf ( PDFDrive )

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

Download 8,82 Mb.

Do'stlaringiz bilan baham:
1   ...   177   178   179   180   181   182   183   184   ...   207




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©hozir.org 2024
ma'muriyatiga murojaat qiling

kiriting | ro'yxatdan o'tish
    Bosh sahifa
юртда тантана
Боғда битган
Бугун юртда
Эшитганлар жилманглар
Эшитмадим деманглар
битган бодомлар
Yangiariq tumani
qitish marakazi
Raqamli texnologiyalar
ilishida muhokamadan
tasdiqqa tavsiya
tavsiya etilgan
iqtisodiyot kafedrasi
steiermarkischen landesregierung
asarlaringizni yuboring
o'zingizning asarlaringizni
Iltimos faqat
faqat o'zingizning
steierm rkischen
landesregierung fachabteilung
rkischen landesregierung
hamshira loyihasi
loyihasi mavsum
faolyatining oqibatlari
asosiy adabiyotlar
fakulteti ahborot
ahborot havfsizligi
havfsizligi kafedrasi
fanidan bo’yicha
fakulteti iqtisodiyot
boshqaruv fakulteti
chiqarishda boshqaruv
ishlab chiqarishda
iqtisodiyot fakultet
multiservis tarmoqlari
fanidan asosiy
Uzbek fanidan
mavzulari potok
asosidagi multiservis
'aliyyil a'ziym
billahil 'aliyyil
illaa billahil
quvvata illaa
falah' deganida
Kompyuter savodxonligi
bo’yicha mustaqil
'alal falah'
Hayya 'alal
'alas soloh
Hayya 'alas
mavsum boyicha


yuklab olish