Microsoft Access Tips for Casual Users

Provided by Allen Browne, allenbrowne.com

Referring to Controls on a Subform

Sooner or later, you will need to refer to information in a control on another form - a subform, the parent form, or some other form altogether. Say for example we have a form called "Students" that displays student names and addresses. In this form is a subform called "Grades" that displays the classes passed and credit points earned for the student in the main form. How can the Students form refer to the Credits control in the Grades subform?

Access refers to open forms as "Forms". Using the dot as a separator, the Surname control on the Students form can be referenced as:

    Forms.Students.Surname

If there are spaces in the names of your objects, use square brackets around the names, e.g.:

    Forms.[Students Form].[First Name]

Now, the area on a form that contains a subform is actually a control too: just have a look at it in design view. This control has a ".form" property which refers to the form that it holds. This ".form" must be included if you wish to refer to controls in the subform. For example:

    Forms.Students.Grades.Form.Credits

where "Students" is the name of the parent form, "Grades" is the name of the control that holds the subform, and "Credits" is a control on the subform.

Once you get the hang of referring to things this way, you will use it incessantly, to SetValue in macros, to print a report limited to a certain financial period (the WHERE clause in the OpenReport action) etc.

In code, you can use Me and Parent to shorten the references. See Help in the VBA window for details.