Can anyone help me with the code to retrieve the name of the table that
a control on a form (that's built off a query) refers to?

For instance:

qryMfrsAndBrands is based on the tables: tblBrands and tblMfrs
formMfrsBrands is based on qryMfrsAndBrands

fieldBrandName is on the form and comes from tblBrands via
qryMfrsAndBrands.

I've been trying to retrieve the name of the table by using MsgBox with
assorted code.
So far I've been able to retrieve the query name, the field name, and
all of the table names in the database one after the other - but I
can't figure out how to retrieve just the one table name.

What I want to use this for is tracking changes in my database. It
seems to me if I can retrieve the field and table names during a
beforeUpdate event, I can use the same code for all fields and all
tables instead of having to code each one individually.

Any help at all would be greatly appreciated - I'm kind of new at this.

OK, I'm VERY new at this.

-Sue

If your form is based on a query that has a field named Field1, you can get
the source table name from:

Me.RecordsetClone.Fields("Field1").SourceTable

The Name of a control may not be the same as its ControlSource, so if you
need to use the name of the control, you will need to read its ControlSource
to get the field. Assuming the control is bound to a field (not unbound, nor
bound to an expression), you would use:

Me.RecordsetClone.Fields(Me.Controls("Text0").Cont rolSource).SourceTable

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SueA" <susan.a.alderson@maine.govwrote in message
news:1155269001.349007.121010@h48g2000cwc.googlegr oups.com...

Can anyone help me with the code to retrieve the name of the table that
a control on a form (that's built off a query) refers to?
>
For instance:
>
qryMfrsAndBrands is based on the tables: tblBrands and tblMfrs
formMfrsBrands is based on qryMfrsAndBrands
>
fieldBrandName is on the form and comes from tblBrands via
qryMfrsAndBrands.
>
I've been trying to retrieve the name of the table by using MsgBox with
assorted code.
So far I've been able to retrieve the query name, the field name, and
all of the table names in the database one after the other - but I
can't figure out how to retrieve just the one table name.
>
What I want to use this for is tracking changes in my database. It
seems to me if I can retrieve the field and table names during a
beforeUpdate event, I can use the same code for all fields and all
tables instead of having to code each one individually.
>
Any help at all would be greatly appreciated - I'm kind of new at this.
>
OK, I'm VERY new at this.
>
-Sue

It worked! Thank you SO much.


Allen Browne wrote:

If your form is based on a query that has a field named Field1, you can get
the source table name from:
>
Me.RecordsetClone.Fields("Field1").SourceTable
>
The Name of a control may not be the same as its ControlSource, so if you
need to use the name of the control, you will need to read its ControlSource
to get the field. Assuming the control is bound to a field (not unbound, nor
bound to an expression), you would use:
>
Me.RecordsetClone.Fields(Me.Controls("Text0").Cont rolSource).SourceTable
>
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
>
"SueA" <susan.a.alderson@maine.govwrote in message
news:1155269001.349007.121010@h48g2000cwc.googlegr oups.com...