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.
Methods
Name
| Description
|
|---|
|
| Appends data from a string expression to a Memo or Long Binary Field object in a Recordset. |
|
| Creates a new user-defined Property object (Microsoft Access workspaces only). |
|
| Returns all or a portion of the contents of a Memo or Long Binary Field object in the Fields collection of a Recordset object. |
Properties
Name
| Description
|
|
|---|
|
| Sets or returns a value that indicates whether a zero-length string ("") is a valid setting for the Value property of the Field object with a Text or Memo data type (Microsoft Access workspaces only). |
|
|
| Sets or returns a value that indicates one or more characteristics of a Field object. Read/write Long. |
|
|
| Returns a value that specifies the sequence of the sort order in text for string comparison or sorting (Microsoft Access workspaces only). Read-only Long.
|
|
|
| Returns a value that indicates whether the data in the field represented by a Field object is updatable. |
|
|
| Sets or returns the default value of a Field object. For a Field object not yet appended to the Fields collection, this property is read/write (Microsoft Access workspaces only). |
|
|
| Returns the number of bytes used in the database (rather than in memory) of a Memo or Long Binary Field object in the Fields collection of a Recordset object. |
|
|
| Sets or returns a value that specifies the name of the Field object in a foreign table that corresponds to a field in a primary table for a relationship (Microsoft Access workspaces only). |
|
|
| Returns or sets the name of the specified object. Read/write String if the object has not been appended to a collection. Read-only String if the object has been appended to a collection.
|
|
|
| Sets or returns the relative position of a Field object within a Fields collection. . |
|
|
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Returns the value of a Field in the database that existed when the last batch update began (ODBCDirect workspaces only).
|
|
| Returns the Properties collection of the specified object. Read-only. |
|
|
| Sets or returns a value that indicates whether a Field object requires a non-Null value. |
|
|
| Returns the number of bytes used in the database (rather than in memory) of a Memo or Long Binary Field object in the Fields collection of a Recordset object. |
|
|
| Returns a value that indicates the name of the field that is the original source of the data for a Field object. Read-only String.
|
|
|
| Returns a value that indicates the name of the table that is the original source of the data for a Field object. Read-only String.
|
|
|
| Sets or returns a value that indicates the operational type or data type of an object. Read/write Integer.
|
|
|
| Sets or returns a value that specifies whether or not the value of a Field object is immediately validated when the object's Value property is set (Microsoft Access workspaces only). |
|
|
| Sets or returns a value that validates the data in a field as it's changed or added to a table (Microsoft Access workspaces only). Read/write String.
|
|
|
| Sets or returns a value that specifies the text of the message that your application displays if the value of a Field object doesn't satisfy the validation rule specified by the ValidationRule property setting (Microsoft Access workspaces only). Read/write String.
|
|
|
| Sets or returns the value of an object. Read/write Variant.
|
|
|
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Returns a value currently in the database that is newer than the OriginalValue property as determined by a batch update conflict (ODBCDirect workspaces only).
|
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