Determine Field's Table 1

[TimTDP]

In Access 2000, I have a form based on a query.

How can I programmatically determine the table of a field on the form?

 

[AlexCuse]

Do you mean how can you tell which table the data from the query came from? It's not 100% clear what you want to do.

Good luck,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[TimTDP]

I have a query, consisting of tblA and tblB
tblA has fields fldX and fldY
tblB has field flbC

I have a form based on this query
If I double click a field I want to return a msg string telling me te name of the field and the table that it belongs to.

I want to make a generic module that I can use in other databases.

hope this makes sense!

 

[PHV]

A starting point is the ControlSource property of the control.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Not really tested throughly but

Private Sub CL_Name_DblClick(Cancel As Integer)
    MsgBox TableForField("CL_Name")
End Sub

Private Function TableForField(FieldName As String) As String
Dim fd As DAO.Field
With Me.Recordset
    For Each fd In .Fields
        If UCase(fd.Name) = UCase(FieldName) Then
            If Len(fd.SourceTable & "") > 0 Then
                TableForField = fd.SourceTable
            Else
                TableForField = "No Source Table"
            End If
            Exit For
        End If
    Next
End With
End Function

 

[TimTDP]

Thanks Golom

how would I use your code if the field was on a sub-form?

 

[Golom]

To generalize it, you probably want to put it in a module as a [red]Public[/red] function and include a recordset argument in the calling list

[red]Public[/red] Function TableForField(FieldName As String[red],rs As DAO.Recordset[/red]) As String
Dim fd As DAO.Field
With [red]rs[/red]
    For Each fd In .Fields
        If UCase(fd.Name) = UCase(FieldName) Then
            If Len(fd.SourceTable & "") > 0 Then
                TableForField = fd.SourceTable
            Else
                TableForField = "No Source Table"
            End If
            Exit For
        End If
    Next
End With
End Function

Then you can call it from anywhere as

Private Sub SomeField_DblClick(Cancel As Integer)
    MsgBox TableForField("SomeField"[red], Me.Recordset[/red])
End Sub

[/Code]

 

[TimTDP]

Brilliant.
Thank you