Field Object, Fields Collection 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