Extract table name through form based on query

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP PRO.

I have a form based on a query that pulls the records from several tables.
So the DATA SOURCE for the Form is based on MyQuery which is based on
MyTable1, MyTable2, and MyTable3.

I want to use VBA to extract the name of the table that piece of data comes
from.

So if MyFormField is bound to MyQueryField which is selected from
MyTableField in MyTableX. I want to do something that would looklike the
following

bob = me.MyFormField.TableName

and bob spits out say, "MyTable2"


I'm not sure this is possible but if you an idea for a workaround I'd love
to hear it.

 

[Allen Browne]

The Field in the Recordset has a SourceTable property that indicates which
table it came from, so you can use:
Me.Recordset.Fields("MyQueryField").SourceTable

There are cases where it's more complicated than that, e.g.
a) Table may be aliased (typically multiple instances of same table)
b) Control's Name may not be the same Control Source.
c) Calculated fields yield a zero-length string for SourceTable.