Field Object

Description

A Field object represents a column of data with a common data type and a common set of properties.

Remarks

The Fields collections of 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 set values for the fields in the current record of the Recordset object.

In both Microsoft Jet and ODBCDirect workspaces, you manipulate a field using a Field object and its methods and properties. For example, you can:

Use the OrdinalPosition property to set or return the presentation order of the Field object in a Fields collection. (This property is read-only for ODBCDirect databases.)
Use the Value property of a field in a Recordset object to set or return stored data.
Use the AppendChunk and GetChunk methods and the FieldSize property to get or set a value in an OLE Object or Memo field of a Recordset object.
Use the Type, Size, and Attributes properties to determine the type of data that can be stored in the field.
Use the SourceField and SourceTable properties to determine the original source of the data.

In Microsoft Jet workspaces, you can:

Use the ForeignName property to set or return information about a foreign field in a Relation object.
Use the AllowZeroLength, DefaultValue, Required, ValidateOnSet, ValidationRule, or ValidationText properties to set or return validation conditions.
Use the DefaultValue property of a field on a TableDef object to set the default value for this field when new records are added.

In ODBCDirect workspaces, you can:

Use the Value, VisibleValue, and OriginalValue properties to verify successful completion of a batch update.

To create a new Field object in an Index, TableDef, or Relation object, use the CreateField method.

When you access a Field object as part of a Recordset object, data from the current record is visible in the Field object's Value property. To manipulate data in the Recordset object, you don't usually reference the Fields collection directly; instead, you indirectly reference the Value property of the Field object in the Fields collection 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.

Properties

AllowZeroLength property, Attributes property, CollatingOrder property, DataUpdatable property, DefaultValue property, FieldSize property, ForeignName property, Name property, OrdinalPosition property, OriginalValue property, Required property, Size property, SourceField, SourceTable properties, Type property, ValidateOnSet property, ValidationRule property, ValidationText property, Value property, VisibleValue property.

Methods

AppendChunk method, CreateProperty method, GetChunk method.

See Also CreateField method.

Specifics (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a Field object in the Fields collection of a QueryDef object or a TableDef object may also contain the following Microsoft Access application-defined properties.

For details on reading and setting these properties, see the individual properties and the Property object.

Caption	ColumnHidden
ColumnOrder	ColumnWidth
DecimalPlaces	Description
Format	InputMask

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.

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)

    ' Print report.
    FieldOutput "TableDef", fldTableDef
    FieldOutput "QueryDef", fldQueryDef
    FieldOutput "Recordset", fldRecordset
    FieldOutput "Relation", fldRelation
    FieldOutput "Index", fldIndex

    rstEmployees.Close
    dbsNorthwind.Close

End Sub
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

Example (Microsoft Access)

The following example creates a new Field object, sets some of its properties, and appends it to the Fields collection of a TableDef object. The procedure then enumerates all fields in the Fields collection of the TableDef object.

Sub NewField()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Create new Field object.
    Set fld = tdf.CreateField("SSN#")
    ' Set Type and Size properties of Field object.
    fld.Type = dbText
    fld.Size = 11
    ' Append field.
    tdf.Fields.Append fld
    ' Enumerate all fields in Fields collection of TableDef object.
    For Each fld in tdf.Fields
        Debug.Print fld.Name
    Next fld
    Set dbs = Nothing
End Sub