Microsoft Access 2010 Bible

Chapter 19: Advanced Access Form Techniques

687

FIGURE 19.3

Aggregate data from a subform can be displayed on the main form.

ControlSource is set to =”(” & [subfPurchases].[Form]!

[txtItemCount] & “ items)”

Before you can put aggregate data in the master form, its value must be found in the subform.

Place a text box wherever you want in the subform, and set its Visible property to No (False)

so that it’s hidden. Put an aggregate expression, such as = Count([ProductID]), into the

ControlSource property of the hidden control.

In the main form, insert a new text box with ControlSource set to the following value:

=[Subform1].Form![Name-of-Aggregate-Control]

where Subform1 is the name of the control on the main form that contains the embedded subform

and Name-of-Aggregate-Control is the name of the control on the subform that contains the aggre-

gate data.

The control on the main form updates each time you change its value in the subform.

Access treats a subform control in the same manner as other controls on the main form. You can

set a subform control’s properties, refer to it with a GoToControl command, and use code to set

and read the values of controls on the subform. Use the following syntax versions to refer to sub-

form properties, subform controls, and subform control properties, respectively:

Forms![FormName]![SubformControlName].Form.SubFormProperty

Forms![FormName]![SubformControlName].Form![ControlName]

Forms![FormName]![SubformControlName].Form![ControlName].ControlProperty

where SubformControlName refers to the name you’ve given the subform control, which is not nec-

essarily the same as the name of the form as it appears in the Navigation Pane.