English

How To: Access Fields in Sequential Order

Summary

The Fields object stored in the Recordset's Fields property is used for named random access to the fields in the table. Thus, the order of the fields if you use a 'For Each' loop may not be consistent with the physical order of the fields in the source table. The Fields collection supports access to a field by its name. The Fields collection does not support access of a field by its index position.

Procedure

If you know a field's name, you can access it this way:

Code:
Set fldID = recs.Fields.Item("ID")

If you know a field's index, you can access it this way:

Code:
Set fld = recs.Fields.Item(recs.TableDesc.FieldName(7))


The TableDesc object stored in the Recordset's TableDesc property is used for sequential and ordered access to the fields in the table. The indexed position of the field information stored in the TableDesc matches the physical order of the fields in the table. The TableDesc class does not allow the access of a field by its name, only by its indexed position.

One common purpose for accessing fields sequentially is for populating lists and grids. To do so, you will want to use the Recordset's TableDesc property, not the Fields property.

Correct way to access the fields:

Code:
List1.Clear
For i = 0 to recs.TableDesc.FieldCount - 1
List1.AddItem recs.TableDesc.FieldName(i)
Next i


Incorrect way to access fields:

Code:
List1.Clear
For Each fld In recs.Fields
List1.AddItem fld.Name
Next fld



Note:
Another important difference between the Fields property and the TableDesc property in a MapLayer's Recordset is that the TableDesc references all of the fields in the attribute table. The Fields collection also references those same fields plus the 'Shape' and 'FeatureID' fields. The Fields collection will always contain two more fields than the TableDesc.