>

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.

You manipulate a field using a Field object and its methods and properties. For example, you can:

You can refer to the Value property of a Field object that you create and append to a Fields collection by its Name property setting using following syntax:

Fields("name")

Note

Fields that have names with embedded spaces must be delimited with brackets ([ ]), for example [Part Number], or enclosed in single quotation marks (' ') or double quotation marks (" "), as in 'Part Number' or "Part Number".

You can also refer to the Value property of a Field object that you create and append to a Fields collection by its position in the Fields collection using this syntax:

Fields(0)

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

When the Field object is accessed 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, as in the following example.

 

Dim dbsCatalog As Database, rstParts As Recordset
Dim strSelect As String

Set dbsCatalog = Workspaces(0).OpenDatabase("Catalog.mdb")
strSelect = "SELECT [Part Name], Size,  " _
    & "[Part Type], [Part Age] AS Age FROM Parts"
Set rstParts = dbsCatalog.OpenRecordset(strSelect)

'Return Part Name field in Recordset object.
Debug.Print rstParts.Fields(0).Value
' Otherwise, use indirect coding.
Debug.Print rstParts(0)    ' Return value of Part Name field.
Debug.Print rstParts![Part Name]    ' Also return Part Name field value.
Debug.Print rstParts![Part Type]    ' Return Part Type field.
Debug.Print rstParts!Age    ' Return Part Age field aliased as Age.
Properties

AllowZeroLength Property; Attributes Property; CollatingOrder Property; DataUpdatable Property; DefaultValue Property; ForeignName Property; Name Property; OrdinalPosition Property; Required Property; Size Property; SourceField, SourceTable Properties; Type Property; V1xNullBehavior Property; ValidateOnSet Property; ValidationRule Property; ValidationText Property; Value Property.

Methods

AppendChunk Method, CreateProperty Method, FieldSize Method, GetChunk Method.

See Also

CreateField Method; Appendix, "Data Access Object Hierarchy."

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.

Example

This example creates a TableDef object and a Field object, appends the Field to the Fields collection in the new TableDef, and appends the TableDef to the TableDefs collection in the current database. The example enumerates all the fields in the new TableDef object and all the properties of the new Field. See the methods and properties listed in the Field summary topic for additional examples.


Function EnumerateField () As Integer
    Dim dbsNorthwind As Database
    Dim tdfTest As TableDef
    Dim fldTest As Field
    Dim I As Integer
    Set dbsNorthwind =_
        DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
    Set tdfTest = dbsNorthwind.CreateTableDef("MyTable") 
    Set fldTest = tdfTest.CreateField("MyField", dbDate)
    tdfTest.Fields.Append fldTest
    dbsNorthwind.TableDefs.Append tdfTest
' Get database name. 
    Debug.Print
    Debug.Print "Database Name: "; dbsNorthwind.Name
    Debug.Print
' Enumerate all fields in tdfTest.
    Debug.Print "TableDefs: Name; Fields: Name"
    For I = 0 To tdfTest.Fields.Count - 1
        Debug.Print "  "; tdfTest.Name;

        Debug.Print "; "; tdfTest.Fields(I).Name
    Next I
    Debug.Print
' Enumerate built-in properties of fldTest.
    Debug.Print "fldTest.Name: "; fldTest.Name
    Debug.Print "AllowZeroLength: "; fldTest.AllowZeroLength
    Debug.Print "Attributes: "; fldTest.Attributes
    Debug.Print "CollatingOrder: "; fldTest.CollatingOrder
    Debug.Print "DefaultValue: "; fldTest.DefaultValue
    Debug.Print "OrdinalPosition: "; fldTest.OrdinalPosition
    Debug.Print "Required: "; fldTest.Required
    Debug.Print "Size: "; fldTest.Size
    Debug.Print "SourceField: "; fldTest.SourceField
    Debug.Print "SourceTable: "; fldTest.SourceTable
    Debug.Print "Type: "; fldTest.Type
    Debug.Print "ValidationRule: "; fldTest.ValidationRule
    Debug.Print "ValidationText: "; fldTest.ValidationText
    EnumerateField = True
End Function
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 Database variable that points to current database.
    Set dbs = CurrentDb
    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
End Sub