471,109 Members | 1,533 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,109 developers and data experts.

Referring to Items on a Sub-Form

32,348 Expert Mod 16PB

The first thing to understand about Sub-Forms is that, to add a form onto another form takes a special Subform control. This Subform control acts as a container for the form that you want to act as a Sub-Form of the main one. That is to say, if you wanted frmB to act as a Sub-Form of frmA, then you would create a Subform control on frmA (in this example we'll call it sfmB). Subforms have a .Form property which contains a reference to the form required (frmB in this case). It is very important when talking about Sub-Forms, to realise that the actual Subform is a control on the main form which contains a Sub-Form. It is not, in itself, the Sub-Form. In this article we refer to Subform as indicating the control, and Sub-Form as indicating the form on that control.

NB. A very common confusion when referring to items on a form is caused by the assumption that any Tab controls used are included in the reference. That is simply not the case. Whether an item (This includes, but is not limited to, Subforms.) is shown directly on the main form itself or on a Tab control doesn't effect the reference address at all.

The Internal syntax (code running within the form's module itself or one of its Sub-Forms) for this is :
Expand|Select|Wrap|Line Numbers
  1. Me.[SubformName].Form.[ControlName]
The External syntax (code running from outside the form's module itself or one of its Sub-Forms) for this is :
Expand|Select|Wrap|Line Numbers
  1. Forms![FormName].[SubformName].Form.[ControlName]
  2. Forms("FormName").[SubformName].Form.[ControlName]
  3. Form_FormName.[SubformName].Form.[ControlName]
While theoretically the .Form object of the Subform should be included in the reference, there is a shorthand version available that leaves this off (It's the dafault property for a Subform and is assumed.), so all the above versions can be shortened to :
Expand|Select|Wrap|Line Numbers
  1. Me.[SubformName]![ControlName]
  2. Forms![FormName].[SubformName]![ControlName]
  3. Forms("FormName").[SubformName]![ControlName]
  4. Form_FormName.[SubformName]![ControlName]
Where :
  1. FormName is the name of the main form.
  2. SubformName is the name of the Subform control on the main form in which the actual Sub-Form is contained.
  3. ControlName is the name of the control that you want to refer to.

You will often see the Exclamation Mark, or Bang, (!) used instead of the Dot (.) as well as vice versa. In most cases it's not a problem as Access will interpret it in the correct way. In general though, a dot (.) is used to denote a predefined property of the object, whereas an exclamation mark (!) is used as shorthand to refer to a named item of a collection. Controls on a form, for instance, are members of the Controls collection of a Form. Fields in a Recordset are actually members of the Recordset's Fields collection. It's important to understand though, that each form in a database is a specific class in its own right. EG. Form_frmA is a defined class and, although the Form class contains no controls as specific properties per se, Form_frmA probably will do. Hence, it is quite acceptable and correct to refer to controls on a form using the dot (.) syntax (EG, frmA.txtBlah).

The Me object can be used within a Form or Report to refer to the associated Form or Report object itself.
EG. Within the module of a Form whose name is frmTest, Me.txtJobNumber is equivalent to Form_frmTest.txtJobNumber.
The Parent object is actually a reference to the object that this module is a child of. Me.Parent from the module of a form used as a Sub-Form would therefore refer to the main form in the partnership. This can be used in conjunction with the Subform references to access sibling forms. EG. If [Form A] has two Subform controls, [sForm B] & [sForm C], then referencing a control [ControlD] on [sForm C] from [sForm B] would be done as Me.Parent.[sForm C]!ControlD.

Examples to refer to the Sub-Form's txtJobNumber control where the Form is called frmFieldServiceDatabase and the Subform is called [qryReviewJobs Subform] (NB. Brackets [] are only necessary if, without them, the reference may have a different meaning. In these examples [txtJobNumber] uses them interchangeably, yet for [qryReviewJobs Subform] they are necessary, and without them the reference would fail.) :
Expand|Select|Wrap|Line Numbers
  1. Me.[qryReviewJobs Subform]![txtJobNumber]
  2. Me.[qryReviewJobs Subform].Form.txtJobNumber
  3. Forms![frmFieldServiceDatabase].[qryReviewJobs Subform]![txtJobNumber]
  4. Forms("frmFieldServiceDatabase").[qryReviewJobs Subform]![txtJobNumber]
  5. Form_frmFieldServiceDatabase.[qryReviewJobs Subform]![txtJobNumber]
Feb 8 '07 #1
6 93850
158 100+
thank you very much this is very helpful
Feb 8 '11 #2
904 Expert 512MB
Nice article Neo.

I would like to point out to everyone using Access 2007 and above that you should always use the full reference to subforms and not the short handed "Me." version.

Expand|Select|Wrap|Line Numbers
  1. Forms![FormName]![SubFormName]![ControlName] 'acceptable
  2. Me![SubFormName]![ControlName] ' not acceptable
The new versions of Access do not recogonize the shortened version when referring to the subform controls in all circumstances. (not 100% sure with AC2010)

Feb 9 '11 #3
32,348 Expert Mod 16PB
Can you say exactly what is and isn't working. it's very dangerous to put such non-specific advice in an article. I very much doubt this is as it may seem to you Mshmyob, but as you're not very specific it's hard to check (not having and never wanting to touch Access 2007 doesn't help either of course).

That said, I very much doubt that 2007 doesn't support the Me object any more, and certainly that is the best reference to use when dealing with code in a form or report object module.

I will check this in 2010 when I get some time, but I'm sure we would have heard much, much (and a few more muches) more about this issue by now if what you say were true.
Feb 9 '11 #4
3,653 Expert Mod 2GB
All, MS Acess 2007 and 2010 both accept the Me object. I have had no problems in either version so far.
Feb 24 '12 #5
904 Expert 512MB
I have been ignoring this thread for quite awhile but since both Neo and twinnyfo are not familiar with all the workings of Access 2007 (who is) I will give more clarification.

As you know split forms were introduced in AC2007. They are simply a form with a "special" type of subform that is always set to datasheet mode and moves the main form data to match the selected "subform" data.

The "Me" reference does not work in this type of subform and you must always fully reference any control in the subform or Access pops up an error.

As far as I know M$ has not fixed this in 2007 (but since I don't use split form subforms I don't know).

Normal subforms do not have the problem with the ME identifier.

Mar 19 '12 #6
3,653 Expert Mod 2GB

Thanks for the clarification! Since I have not used split forms, I was unaware of their limitations. I will keep this info in my tool kit, just in case it pops up!

Thanks again!
Mar 19 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Luis Esteban Valencia | last post: by
5 posts views Thread by Wally | last post: by
1 post views Thread by thomasp | last post: by
1 post views Thread by =?Utf-8?B?QW5kcmV3?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.