A Fields collection contains all stored Field objects of an Index, QueryDef, Recordset, Relation, or TableDef object.
Remarks
The Fields collections of the Index, QueryDef, Relation, and TableDef objects contain the specifications for the fields those objects represent. The Fields collection of a Recordset object represents the Field objects in a row of data, or in a record. You use the Field objects in a Recordset object to read and to set values for the fields in the current record of the Recordset object.
To refer to a Field object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:
Fields(0)
Fields("name")
Fields![name]
With the same syntax forms, you can also refer to the Value property of a Field object that you create and append to a Fields collection. The context of the field reference will determine whether you are referring to the Field object or the Value property of the Field object.
Example
This example shows what properties are valid for a Field object depending on where the Field resides (for example, the Fields collection of a TableDef, the Fields collection of a QueryDef, and so forth). The FieldOutput procedure is required for this procedure to run.
Visual Basic for Applications
Sub FieldX()
Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim fldTableDef As Field
Dim fldQueryDef As Field
Dim fldRecordset As Field
Dim fldRelation As Field
Dim fldIndex As Field
Dim prpLoop As Property
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")
' Assign a Field object from different Fields
' collections to object variables.
Set fldTableDef = _
dbsNorthwind.TableDefs(0).Fields(0)
Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)
Set fldRecordset = rstEmployees.Fields(0)
Set fldRelation =dbsNorthwind.Relations(0).Fields(0)
Set fldIndex = _
dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)
Sub FieldOutput(strTemp As String, fldTemp As Field)
' Report function for FieldX.
Dim prpLoop As Property
Debug.Print "Valid Field properties in " & strTemp
' Enumerate Properties collection of passed Field
' object.
For Each prpLoop In fldTemp.Properties
' Some properties are invalid in certain
' contexts (the Value property in the Fields
' collection of a TableDef for example). Any
' attempt to use an invalid property will
' trigger an error.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " = " & _
prpLoop.Value
On Error GoTo 0
Next prpLoop
End Sub
This example uses the CreateField method to create three Fields for a new TableDef. It then displays the properties of those Field objects that are automatically set by the CreateField method. (Properties whose values are empty at the time of Field creation are not shown.)
Visual Basic for Applications
Sub CreateFieldX()
Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim fldLoop As Field
Dim prpLoop As Property
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")
' Create and append new Field objects for the new
' TableDef object.
With tdfNew
' The CreateField method will set a default Size
' for a new Field object if one is not specified.
.Fields.Append .CreateField("TextField", dbText)
.Fields.Append .CreateField("IntegerField", dbInteger)
.Fields.Append .CreateField("DateField", dbDate)
End With
dbsNorthwind.TableDefs.Append tdfNew
Debug.Print "Properties of new Fields in " & tdfNew.Name
' Enumerate Fields collection to show the properties of
' the new Field objects.
For Each fldLoop In tdfNew.Fields
Debug.Print " " & fldLoop.Name
For Each prpLoop In fldLoop.Properties
' Properties that are invalid in the context of
' TableDefs will trigger an error if an attempt
' is made to read their values.
On Error Resume Next
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
On Error GoTo 0
Next prpLoop
Next fldLoop
' Delete new TableDef because this is a demonstration.
dbsNorthwind.TableDefs.Delete tdfNew.Name
dbsNorthwind.Close