Using Field Objects

The default collection of a Recordset object is its Fields collection. This collection includes a single Field object that corresponds to each field (or column) in the Recordset. Each Field object has a set of properties that uniquely identifies the field name, data type, and so on, as well as the value of the field in the current 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 by using a Field object and its methods and properties. For example, you can:

 Use the OrdinalPosition property to get or set the position of a Field object relative to other fields in a Fields collection.

 Use the FieldSize property, the GetChunk method, or the AppendChunk method to get or set a value in an OLE Object or Memo field of a Recordset object.

 Read or set the DAO Value property of a Recordset object.

 Read or set the DAO AllowZeroLength, Required, ValidationRule, ValidationText, or ValidateOnSet property setting to find or specify validation conditions.

 Read the SourceField and SourceTable property settings to determine the original source of the data.

Referring to Field Objects

You can identify a Field object by its DAO Name property, which corresponds to the column name in the table from which the data in the field was retrieved. The Fields collection is the default collection of a Recordset object. Therefore, you can refer to the LastName field in the rstEmployees Recordset in any of the following ways:

rstEmployees.Fields("LastName")
rstEmployees!LastName
rstEmployees![LastName]

When using the ! operator, you must include brackets around a field name when it contains spaces. For example, the statement:

strEmp = rstEmployees!Last Name

will not compile, but the statement:

strEmp = rstEmployees![Last Name]

will compile with no problems.

Within the Fields collection, each Field object can also be identified by its index:

rstEmployees.Fields(0)

The index enables you to walk through the collection in a loop, replacing the index with a variable that is incremented with each pass through the loop. Objects in a collection are numbered starting with zero, so the first Field object in the Fields collection is number 0, the second is 1, and so on. The field order is determined by the underlying table. Fields are usually numbered in the order retrieved when the Recordset object is opened. One drawback to this approach is that you can't be certain which field will be referred to, because the underlying table structure may change, fields may be added or deleted, and so on.

To help you determine the order of fields in a Fields collection, the Field object supports the OrdinalPosition property, which you can use to get or set a field's position relative to other fields in the collection. You can set the OrdinalPosition property to any positive integer to change the field order when data is displayed in a form, copied to an array or a Microsoft Excel worksheet, and so on.

See Also   For more information on the OrdinalPosition property, search the Help index for "OrdinalPosition property."

When writing code that refers to fields within a loop, it's more efficient to refer to Field objects rather than to refer to fields by their names. The following example shows a more efficient way of writing the ChangeTitle procedure discussed earlier in this chapter. Instead of referring to the Title field as rstEmp!Title, it refers to the field by its object variable, which doesn't require that the field be looked up in the Fields collection every time it's referred to.

Sub ChangeTitle()
   Dim dbsSales As Database
   Dim rstEmp As Recordset, fldTitle As Field
   Dim wrkCurrent As Workspace
   Set wrkCurrent = DBEngine.Workspaces(0)
   Set dbsSales = OpenDatabase("Northwind.mdb")
   Set rstEmp = dbsSales.OpenRecordset("Employees", dbOpenTable)
   Set fldTitle = rstEmp.Fields("Title")
   wrkCurrent.BeginTrans
   Do Until rstEmp.EOF
      If fldTitle = "Sales Representative" Then
         rstEmp.Edit
         fldTitle = "Sales Associate"
         rstEmp.Update
      End If

      rstEmp.MoveNext
   Loop
   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      wrkCurrent.CommitTrans
   Else
      wrkCurrent.Rollback
   End If
   rstEmp.Close
   dbsSales.Close
End Sub

Field Data Types

A Field object has a Type property that can be set to one of the following 12 DAO data types.

For a Field object on a Recordset, the Type property is read-only. However, you must be aware of the field type when copying data to or from a field in code or a "Type mismatch" error may occur. For example, you cannot copy Text data to an Integer field.

The Type property of a Field object on a Recordset is determined by the underlying table from which the record was retrieved. If you create the table and its fields by using DAO data-definition language (DDL) statements, you can easily determine the data type of the table's fields.

If you're accessing external data through an installable ISAM driver, the data types within external tables may be different from those defined within Microsoft Jet. The installable ISAM driver for the external data source converts external data types into their closest equivalent DAO data type.

See Also   For more information on data type conversion in external data sources, see Chapter 18, "Accessing External Data."

The GUID Data Type

The GUID data type is used to store a globally unique identifier, a unique string of digits that identifies OLE objects, Microsoft SQL Server remote procedure calls, and other entities that need a unique reference identification.

Note   The GUID data type is also used in the Database object's ReplicaID property to identify a replica. For information on replicas, see Chapter 20, "Using Replication in Your Application."

The Text Data Type

For Field objects declared as type Text, you must set the Size property, which indicates the length of the longest string that can be stored in the field. All other types of Field objects have their Size property set automatically.

The Currency Data Type

If you need to store monetary values, use fields of type Currency. Don't use any of the number data types (such as Single) for currency values, because numbers to the right of the decimal may be rounded during calculations. The Currency data type always maintains a fixed number of digits to the right of the decimal.

The Long Data Type

In some tables, you'll want to store a series of sequential numbers to uniquely identify records. For example, you may want to start customer order records at order number 1 and begin counting upward.

Microsoft Access can automatically insert unique numbers in a field, saving your application the effort of generating unique identifiers to be used within a primary key field. To take advantage of this capability, define a field with the Long data type and set the dbAutoIncrField constant in the Field object's Attributes property. Auto-incrementing fields start at 1 and increment sequentially. Fields of this type are also referred to as AutoNumber fields.

If you want to establish a primary key/foreign key relationship between two tables by using an AutoNumber field, make sure that the foreign key field is also defined as Long.

You can also set the DAO DefaultValue property of a Field object on a TableDef object to a special value called GenUniqueId( ). This causes a random number to be assigned to this field whenever a new record is added or created. The field's Type property must be Long.

Note   A Field object's data type is read/write before the field is appended to a table's Fields collection, and read-only after it's appended.

See Also   For more information on the DAO Attributes and DefaultValue properties, search the Help index for the name of the property.

The OLE Object and Memo Data Types

OLE Object and Memo fields are collectively referred to as large value fields because they are typically much larger than fields of other data types. OLE Object fields consist of binary data up to 1.2 gigabytes in size. This type is used to store pictures, files, or other raw binary data. Memo fields are used to store lengthy text and numbers, such as comments or explanations. The size of a Memo field is limited by the maximum size of the database.

Records within a Recordset object must fit on the 2K data pages supported by the Microsoft Jet database engine. Each Field object you include in the table definition counts toward this 2K total, including OLE Object and Memo fields. However, the amount stored for OLE Object and Memo fields is only 14 bytes per non-null field, and only 1 byte for null fields. The 14 bytes is used to store a pointer to the actual data for these fields, which is located on additional 2K pages. The amount of data committed to each text field isn't set until you actually store data in the field. You can overcommit a data page by defining more text fields than there would be room for, but no more than about 2K of actual data can be stored in a record. For example, you can define fifteen 250-byte text fields in a record, but the total number of characters stored must be less than 2K.

When you query tables containing large value fields, don't include those fields in the field list unless you need them, because returning large value fields takes time. Also, be aware that you can't index large value fields.

A snapshot- or forward-only-type Recordset object opened against large value fields in an .mdb file doesn't actually contain that data. Instead, the snapshot maintains references to the data in the original tables, the same way a dynaset refers to all data.

Handling Large Value Data

Sometimes you'll need to read or copy data from a large value field where you don't have sufficient memory to copy the entire field in a single statement. Instead, you have to break up the data into smaller units, or chunks, that will fit available memory. The FieldSize property tells you how large the field is, measured in bytes. Then you can use the GetChunk method to copy a specific number of bytes to a buffer, and use the AppendChunk method to copy the buffer to the final location. You then continue using GetChunk and AppendChunk until the entire field is copied.

See Also   For more information on DAO field data types, search the Help index for "data types, Microsoft Jet."

Reading and Writing Data

When you read or write data to a field, you're actually reading or setting the DAO Value property of a Field object. The DAO Value property is the default property of a Field object. Therefore, you can set the DAO Value property of the LastName field in the rstEmployees Recordset in any of the following ways:

rstEmployees!LastName.Value = strName
rstEmployees!LastName = strName
rstEmployees![LastName] = strName

Write Permission

The tables underlying a Recordset object may not permit you to modify data, even though the Recordset is of type dynaset or table, which are usually updatable. Check the Updatable property of the Recordset to determine whether its data can be changed. If the property is True, the Recordset object can be updated.

Individual fields within an updatable Recordset object may not be updatable, and trying to write to these fields generates a run-time error. To determine whether a given field is updatable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset. The following example returns True if all fields in the dynaset created by strQuery are updatable and returns False otherwise.

Function blnUpdatable(strQuery As String) As Boolean
   Dim dbs As Database, rstDynaset As Recordset, intI As Integer
   On Error GoTo ErrorHandler
   ' Initialize the function's return value to True.
   blnUpdatable = True
   Set dbs = CurrentDb
   Set rstDynaset = dbs.OpenRecordset(strQuery, dbOpenDynaset)
   ' If the entire dynaset isn't updatable, return False.
   If rstDynaset.Updatable = False Then
      blnUpdatable = False
   Else
      ' If the dynaset is updatable, check if all fields in the dynaset are updatable.
      ' If one of the fields isn't updatable, return False.
      For intI = 0 To rstDynaset.Fields.Count - 1
         If rstDynaset.Fields(intI).DataUpdatable = False Then
            blnUpdatable = False
            Exit For
         End If
      Next intI
   End If
ErrorHandler:
   Select Case Err
      Case 0
         Exit Function
      Case Else
         MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
         Exit Function
   End Select
End Function

Criteria

Any single field can impose a number of criteria on data in that field when records are added or updated. These criteria are defined by a handful of properties. The DAO AllowZeroLength property on a Text or Memo field indicates whether or not the field will accept a zero-length string (""). The DAO Required property indicates whether or not some value must be entered in the field, or if it instead can accept a Null value. For a Field object on a Recordset, these properties are read-only; their state is determined by the underlying table.

See Also   For more information on the DAO AllowZeroLength and Required properties, search the Help index for the name of the property.

Field-Level Data Validation

Validation is the process of determining whether data entered into a field's DAO Value property is within an acceptable range. A Field object on a Recordset may have the DAO ValidationRule and ValidationText properties set. The DAO ValidationRule property is simply a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The DAO ValidationText property is a string that Microsoft Access displays in an error message if you try to enter data in the field that's outside the limits of the DAO ValidationRule property. If you're using DAO in your code, then you can use the DAO ValidationText for a message that you want to display to the user.

Note   The DAO ValidationRule and ValidationText properties also exist at the Recordset level. These are read-only properties, reflecting the table-level validation scheme established on the table from which the current record is retrieved.

A Field object on a Recordset also features the ValidateOnSet property. When the ValidateOnSet property is set to True, Microsoft Access checks validation as soon as the field's DAO Value property is set. When it's set to False (the default), Microsoft Access checks validation only when the completed record is updated. For example, if you're adding data to a record that contains a large Memo or OLE Object field and that has the DAO ValidationRule property set, you should determine whether the new data violates the validation rule before trying to write the datayou should write the data when the field value is set. To do so, set the ValidateOnSet property to True. If you wait to check validation until the entire record is written to disk, you may waste time trying to write an invalid record to disk.

See Also   For more information on the DAO ValidationRule, ValidationText, and ValidateOnSet properties, search the Help index for the name of the property.

Tracing the Origin of Dynaset Fields

A dynaset-type Recordset object can include records from more than one source table. Also, within a single record, fields from different tables can be joined into new records. Sometimes it's useful to know the table from which a field originated. The SourceTable property of a Field object returns the name of the table from which the field's current data was retrieved.

Within a query, a field can be renamed for display purposes. For example, in an SQL SELECT query, the AS operator in the select field list can create an alias for one of the returned fields. In a Recordset based on an SQL query, a field that has been aliased is represented by a Field object whose DAO Name property reflects the alias, not the original field name. To find out the original field name, check the Field object's SourceField property.

© 1996 Microsoft Corporation. All rights reserved.