UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Edit Discussion
> Referring To Properties And Controls On Subforms    
Referring To Properties And Controls On Subforms

Getting the proper syntax when referring to properties and controls on a subform from the main form, or another subform can be tricky.

See this handy guide for help. MVPS Website

Collections Vs. Properties ("!" vs ".")

While reviewing the above link, you might notice the mixed use of the Bang operator(!) and the Dot operator(.) - There is a distinct difference in how each of these works, and a correct place for each. In short, the bang operator is used to indicate that the following name is part of a collection, and the dot operator is used to indicate the following name belongs to a property. For example:

Forms!frmMain.Recordsource
Forms!frmMain!ctlID.Value

You can see from the above that frmMain is a member of the Forms collection, and the Recordsource of frmMain is a property. And, you can see that ctlID is a member of the Controls collection of frmMain, and Value is a property of that member.

Referencing a Subform

Often, much confusion lies in trying to reference properties on a subform. The syntax for doing so is as follows:

Forms!FormName!SubformControlName.Form!CollectionItem
Forms!FormName!SubformControlName.Form.Property

Note that in order to reference the subform, you do so by referencing the name of the control that contains the subform (rather than the name of the form itself), followed by the Form property. After the Form property of the control has been referenced, you are effectively 'inside' the subform, and can continue as normal.

Expression Service (ES) Vs. VBA Collections Referencing

There are two ways you can reference the above information... through the Expression Service, as exampled above, or directly through the collections via VBA. The ES has the major advantage of being able to be referenced through SQL statements and properties of objects in the Access interface. The minor advantage is shortened code. The major disadvantage to using the ES is that you cannot dynamically provide names of the properties or collections you wish to reference: e.i. - you cannot supply these as a variable, which may be desired to achieve certain 'global' functionality. When using the ES, items must be hardcoded.

By use of the VBA Collections for these same items, we can replace these hardcoded names with variables, thus opening a great many doors for global functionality. The downfall here is that we can only do this through VBA, and not through the Access interface. Here are some examples:

CODE

Dim strForm As String, strControl As String, strSubformControl As String

strForm = "frmMain"
strSubformControl = "ctlSubform1"
strControl = "ctlID"

Debug.Print Forms(strForm).Controls(strControl).BackColor

Debug.Print Forms(strForm).Controls(strSubformControl).Form.Controls(strControl).BackColor
Edit Discussion
Thank you for your support!
This page has been accessed 8,241 times.  This page was last modified 11:39, 14 February 2012 by Jack Leach. Contributions by Alan Greenwood, Glenn Lloyd and TheDBguy  Disclaimers