SourceField, SourceTable Properties

Applies To Field object.

Description

SourceField — returns a value that indicates the name of the field that is the original source of the data for a Field object.
SourceTable — returns a value that indicates the name of the table that is the original source of the data for a Field object.

Return Values

The return value is a String specifying the name of the field or table that is the source of data.

Remarks

For a Field object, use of the SourceField and SourceTable properties depends on the object that contains the Fields collection that the Field object is appended to, as shown in the following table.

Object appended to	Usage

Index	Not supported
QueryDef	Read-only
Recordset	Read-only
Relation	Not supported
TableDef	Read-only

These properties indicate the original field and table names associated with a Field object. For example, you could use these properties to determine the original source of the data in a query field whose name is unrelated to the name of the field in the underlying table.

Note The SourceTable property will not return a meaningful table name if used on a Field object in the Fields collection of a table-type Recordset object.

See Also SourceTableName property.

Example

This example demonstrates the SourceField and SourceTable properties by opening a Recordset made up of fields from two tables.

Sub SourceFieldX()

    Dim dbsNorthwind As Database
    Dim rstProductCategory As Recordset
    Dim fldLoop As Field
    Dim strSQL As String
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' Open a Recordset from an SQL statement that uses fields
    ' from two different tables.
    strSQL = "SELECT ProductID AS ProdID, " & _
        "ProductName AS ProdName, " & _
        "Categories.CategoryID AS CatID, " & _
        "CategoryName AS CatName " & _
        "FROM Categories INNER JOIN Products ON " & _
        "Categories.CategoryID = Products.CategoryID " & _
        "ORDER BY ProductName"
    Set rstProductCategory = _
        dbsNorthwind.OpenRecordset(strSQL)

    Debug.Print "Field - SourceTable - SourceField"
    ' Enumerate Fields collection of Recordset, printing
    ' name, original table, and original name.
    For Each fldLoop In rstProductCategory.Fields
        Debug.Print "    " & fldLoop.Name & " - " & _
            fldLoop.SourceTable & " - " & fldLoop.SourceField
    Next fldLoop

    rstProductCategory.Close
    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

The following example creates a Recordset object by using an SQL statement that creates aliases for fields in two different tables in the database. The example then prints the name of the field, the original table, and the original field.

Sub SourceInfo()
    Dim dbs As Database, rst As Recordset, fld As Field
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Construct SQL statement.
    strSQL = "SELECT ProductID As ProductCode, " _
        & "CategoryName As TypeOfProduct FROM Categories " _
        & "INNER JOIN Products ON Categories.CategoryID " _
        & " = Products.CategoryID;"
    ' Open dynaset-type Recordset object.
    Set rst = dbs.OpenRecordset(strSQL)
    For Each fld In rst.Fields
        ' Print field name.
        Debug.Print "Field Name: "; fld.Name
        ' Print original table name.
        Debug.Print "Source Table: "; fld.SourceTable
        ' Print original field name.
        Debug.Print "Source Field: "; fld.SourceField
        Debug.Print
    Next fld
    rst.Close
    Set dbs = Nothing
End Sub