A Field object can be identified by its Name property, which corresponds to the field name in the table from which the data in the field was retrieved. Because the Fields collection is the default collection of a Recordset object, you can use either of the following syntax forms to return the field’s current value:
rst!LastName
rst.Fields("LastName")
Within the Fields collection, each Field object can also be identified by its index:
rst.Fields(0)
The index enables you to walk through the collection in a loop, replacing the index with a variable that is incremented with each pass through the loop. Object numbers in the collection are zero-based, so the first Field in the collection is number 0, the second is 1, and so on. The field order is determined by the underlying table. Fields are usually numbered in the order that they are retrieved when the Recordset object is opened. One drawback to using the index to identify a field is that you can’t be certain which field will be referenced, because the underlying table structure may change, fields may be added or deleted, and so on.
The Field object also provides two properties that indicate its position relative to the other fields in the collection — the OrdinalPosition property and the CollectionIndex property. These properties are closely related, yet subtly different. CollectionIndex is a read-only number that indicates the Field object’s absolute position within the collection as determined by the collection population. The OrdinalPosition property, in contrast, is a read-write property that you can set to any positive integer to change the field order when data is displayed in a form or copied to an array or a Microsoft Excel worksheet, and so on. The value of the OrdinalPosition property usually corresponds to the value of the field’s CollectionIndex property.
When you write a loop to walk through the Fields collection, the loop counter always identifies fields in the same sequence, which corresponds to the CollectionIndex property, regardless of the OrdinalPosition property’s value.
Caution The CollectionIndex property is not exposed to DAO users through the Object Browser. Although CollectionIndex is available, its use is discouraged because at the present time Microsoft is not committed to maintaining it in future versions of DAO. Consequently, this feature is not tested to the same reliability level as other DAO features, and assistance on its use is not available through Microsoft Technical Support.
Microsoft Excel Users You can also copy individual fields from a Recordset object to a ListBox control by using the ListBox control’s AddItem method. For more information, see the Microsoft Excel Visual Basic Help file.