By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

Retrieve the table name behind a control on a subform

P: n/a
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

Aug 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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" <su**************@maine.govwrote in message
news:11**********************@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

Aug 11 '06 #2

P: n/a
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" <su**************@maine.govwrote in message
news:11**********************@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
Aug 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.