Share via

Field Object (DAO)

  • Article

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 a Microsoft Access workspacee, 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.

  • 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.

  • 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.

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.

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

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.)

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