473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,322 developers and data experts.

Referring to Items on a Sub-Form

NeoPa
32,556 Expert Mod 16PB
Introduction

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 95839
jaad
158 100+
thank you very much this is very helpful
Feb 8 '11 #2
mshmyob
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
  3.  
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)

cheers,
Feb 9 '11 #3
NeoPa
32,556 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
twinnyfo
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
mshmyob
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.

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

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

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

Similar topics

3
by: Luis Esteban Valencia | last post by:
hello quite a simple one if you understand regular expressions vbscript and ..net, probably quite hard if you don't i have a single line input which offers classic search functionality, so if...
1
by: sneha123 | last post by:
There will be some 20 questions and for each question there will be 4 choices.what i want to do is to select multiple answers by clicking the checkbox. i m using asp.net,vb.net pls help me we...
10
by: tmaster | last post by:
When I try to dynamically add a second sub menu item to this ContextMenu item, I get an error 'Specified argument was out of the range of valid values'. Private Sub mnuTopics_Show_Select(ByVal...
5
by: Wally | last post by:
I need to browse all ITEMS of a ListBox and all ITEMS of a ComboBox. I wrote 2 distinct Sub that are almost identical. The difference is only the object type. How could I browsing ITEMS using only...
1
by: thomasp | last post by:
Has anyone got some sample code to do drag and drop from one listbox to another listbox using VB.Net 2005. The below code works for draging and droping one at a time, but not for multiselected...
0
by: Brian Henry | last post by:
Since no one else knew how to do this I sat here all morning experimenting with this and this is what I came up with... Its an example of how to get a list of items back from a virtual mode list...
4
by: MLH | last post by:
I have a report and on it, a subreport control Main Report Name: rptInvoiceMain Sub Report Name: rptInvoiceSubReport SubReport Control Name: rptInvoiceSubReportCtl The...
1
by: =?Utf-8?B?QW5kcmV3?= | last post by:
Hi, friends, I am using C#.net 2005 to create a windows application. It has menu items, such as File, etc. Under File, there are more menu items, such as New Files, Working Files, etc. Under...
0
by: auspackuk | last post by:
I writing a program that lets me enter the name of an item to swap and the adds it to an array list when a button Add is pressed. Once the item is added to the array list a sub procedure should be...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.