Get the Source Table of a Bound Control

This probably doesn't come up all that often, but it was asked in the Microsoft Access newsgroups -- always a good source for help on how to get the most out of Access -- and it was an interesting question.

Suppose you have a form whose recordsource is a query that selects fields from multiple tables. On that form, for any control that is bound to a field, how could you programmatically determine what table that field comes from? For example, suppose you have two tables, "People" and "Genders"; a query named "qryPeopleGender" that joins these tables to return records containing information about each person, including the field FirstName from table People and the field GenderDesc from table Genders; and a form "frmPeople" bound to qryPeopleGender, with bound text boxes "txtFirstName" and "txtGenderDesc". How then could you determine via VBA code that txtFirstName is bound to a field that comes from the table People, while txtGenderDesc is bound to a field that comes from table Genders?

The answer, if the form has a DAO recordset (as is normal in MDB and ACCDB files), is to use the DAO SourceTable property, which applies not to a control, but to a DAO Field object in the form's recordset. Thus, to get at this property, you have to first identify the name of the field to which the control is bound, and then use that name to get a reference to that field in the form's recordset, and inspect that field's SourceTable property. Assuming the form is open, you can do it with code like this:

code: click in the frame, Select All, then Paste into your code editor

Note the following restrictions:

1. The form must be open for this to work.
2. The form must have a DAO recordset. It can't be in an ADP (Access Data Project), which binds forms to data using ADO instead of DAO. If the form is in an MDB/ACCDB, it can't have been explicitly bound to an ADO recordset instead of the default.
3. The controls you are inspecting must be bound to fields. They can't be unbound or calculated controls.