How to reference deeply nested forms in VBA

See this link for the ultimate reference in form/subform ... report/subreport systax

http://www.mvps.org/access/forms/frm0031.htm

Basically - from the Main form:

Me.Subform1.Form.Subform2.Form.Subform3.Form.Subform4.AllowEdits = True ' or false

mx

02.06.2008 at 02:09PM PST, ID: 20836263

Rank: Genius

Actually ... one more .Form

Me.Subform1.Form.Subform2.Form.Subform3.Form.Subform4.Form.AllowEdits = True ' or false

mx

02.06.2008 at 02:10PM PST, ID: 20836276

Rank: Genius

My post assumes that each subsequent subform is embedded in the subform above, ie in another subform

mx

02.06.2008 at 02:12PM PST, ID: 20836291

Rank: Genius

You refer to a subform like this:

Me.NameOfYourSubformCONTROL.Form.NameOfSubformControl2.Form.NameOfSubformControl3.Form.AllowEdits

Note that you MUST get the "NameOfSubformControl" correct ... this is not necessarily the name of the form that you're using, but instead is the name of the Subform CONTROL that you've placed on that form ...

In the attached picture, the FORM that I'm using as a subform is named "frmManagers", but the name of the Subform Control is "sfrmManagers" ... so I'd use Me.sfrmManagers.Form to refer to the FORM that is contained in sfrmManagers.

SubForm.png (39 KB) (File Type Details)

Subform Control

02.07.2008 at 01:40AM PST, ID: 20839398

Rank: Genius

boag2000:

Faulkenator,

Always remeber that in "most" cases the Expression builder can build these types of "Experssions" for you.
(as the name implies)
;-)

For example:
If you are in design view of your main form, right-click on an empty area of the detail section.
Select "Build Event".
Then Select "Experssion Builder"
On the left hand side you will see the name of your Main form in a "folder style" icon, with a plus "+" sign next to it.
When you double click it, it will open up to the first subform.
You can "drill down" to whatever form you like.
Then in the middle screen you will see all the Objects in/on your selected form.
If you double-click on an object, it will build the "nested" text string for you.

Just FYI

JeffCoachman

02.07.2008 at 05:26AM PST, ID: 20840477

Wow, so many responses. THANKS ALL!
As for: DatabaseMX:
Me.Subform1.Form.Subform2.Form.Subform3.Form.Subform4.Form.AllowEdits = True ' or false
mx
I have tried that on numerous occasions, and I still get a reference error.
And, yes, I am aware of the issue of the name of the embedded form taking on the caption of the embedded form instead of the name of the embedded form (a source of frustration for me - I alsways have to go back and copy the SourceObject property to the Name property. Grrr).

And I have used the expression builder too, and, yet I still get the bad reference error.

In my next post, my friend suggested a great solution that works reall nice (read on):

02.07.2008 at 05:33AM PST, ID: 20840528

I define a global Form object for any form that I want to set the AllowEdits property. IE:
Global frmMyDeepForm4 as Form

In the Form_Open event:
Set frmMyDeepForm4 =Me.Form (In the Close Event: Set frmMyDeepForm4 = Nothing)

In the top level Form's Button Click event:
frmMyDeepForm4.AllowEdits=True or False

That's it. Dead simple. Works like a champ! I still don't know why all the above syntaxes fail me.

Thanks for the reply. I'll leave this line open for a day or so and then split up the goodies.

02.07.2008 at 05:45AM PST, ID: 20840617

Hmmmm I wonder if the length of the form name is killing it:

frm_MCGCS_TGUTOE_SubForm_4_Enrollments

(And all the form names above it in the nesting are just as long.)
(The length is greater than 32 - isn't that a "magic numer"? And, if so, how could Microsoft allow me to get it that long.)

Hmmmmm Maybe I have tripped over, yet another, bug. . . .Hmmmmm...

02.07.2008 at 07:48AM PST, ID: 20841729

By the way, TGUTOE refers to "The Grand Unified Theory Of Everything" (Big Form) :)

And to clarify my earlier post: The "set frmMyDeepForm4" is set in the SUB FORM's Open/Close event.

The Button CLick event is in the top level Form's button.

02.07.2008 at 08:11AM PST, ID: 20842000

Rank: Genius

The max length of an object name is 64 characters ... you're probably using Unicode, which means that you would be limited to 32 characters. This is a documented specification thus it is NOT a bug.

Regardless, you can certainly refer to a 4 deep subform IF you refer to it correctly. To verify this, I just nested 4 subforms and was able to change a label caption on the most deeply nested subform. The only caveat is this: you MUST correctly refer to the Subform controls ... a Subform control contains a form, but to the "parent" form it's just another control. Thus, if you have a Subform control named "sfcNest1" that contains a form named "frmBalance", and you want to get to a control named txtDate, you'd refer to it like this:

Me.sfcNest1.Form.txtDate

Now, if frmBalance contains a subform control named "sfcNest2", you would get a handle on it like this:

Me.sfcNest1.Form.sfcNest2.Form

If the form contained in sfcNest2 contains a Subform control named sfcNest3, do this:

Me.sfcNest1.Form.sfcNest2.Form.sfcNest3.Form

and so on ... in other words, you MUST REFER TO THE SUBFORM CONTROL ... as I mentioned earlier, I just did this with on a form with 4 nested subforms (5 levels deep) and it worked ... if you'd like, I can upload a copy of this to illustrate how to do this correctly.

Finally: The use of Global Variables is generally not a good idea. Global variables can lose their scope if you have an unhandled error (and sometimes even with an On Error Resume Next statement). You would be much, much better off to properly refer to your subform and deal with it directly.

02.07.2008 at 08:52AM PST, ID: 20842443

Rank: Genius

The original syntax I posted should have worked - so, it seems something else is going on here ??

mx

02.07.2008 at 10:19AM PST, ID: 20843333

Unicode: An interesting idea, I'll have to see where that is set. That could be part of it.
Good point about Global Objects. If I can get the long form to work, I will do that - Thanks. (However, since the declared object only exists for the life of the -open- form, that should not be too much of a problem, but your point is duely noted.)

As for the Name.Form.Name2.Form.Name3.Form.AllowEdits syntax:
Strange. Here is the exact line of test code that fails due to bad reference:

MsgBox Me.frm_MCGCS_TGUTOE_SubForm_3_Issuers.Form.frm_MCGCS_TGUTOE_SubForm_4_ItemsIssued.Form.frm_MCGCS_TGUTOE_SubForm_5_Bins.Form.frm_MCGCS_TGUTOE_SubForm_6_Enrollments.Form.AllowEdits

I've verified these names several times (by copying the Source Object into the Name property).

I verified that the first level works:
MsgBox Me.frm_MCGCS_TGUTOE_SubForm_3_Issuers.Form.AllowEdits

But
MsgBox Me.frm_MCGCS_TGUTOE_SubForm_3_Issuers.Form.frm_MCGCS_TGUTOE_SubForm_4_ItemsIssued.Form.AllowEdits
Does not

(Ignore the fact that I start at "3". We pulled Forms "1" and "2")

02.07.2008 at 10:43AM PST, ID: 20843628

Is there a property Setting for determining the the Unicode property? When I try to read a text file created by the SaveAsText command, I need to know which method was used to create it. And, I'll then know what to look out for when using a database.

02.07.2008 at 01:13PM PST, ID: 20845266

Rank: Genius

Can you .zip and post your database to www.ee-stuff.com, or perhaps just the forms needed to recreate this?

02.07.2008 at 04:26PM PST, ID: 20846820

Sorry, confidentiality would be an issue. I think the problem is related to the number of charatcers in the name of the forms. I think Unicode is enabled in the database and that then knocks down the character count to 32. My form names fall within the 35 to 45 character count. For other, somewhat related reasons, I am trying to find what property setting I can read to determine the unicode setting for the database, or, perhaps for the Access installation on the PC. That information will "seal the deal"

Back to the forms: They are fairly simple and straight forward. The top level is unbound. The subforms are "daisy chained" just as the data that they are bound to.

02.07.2008 at 04:33PM PST, ID: 20846856

Rank: Genius

Are we sure that Unicode applies to object names?

mx

02.07.2008 at 05:12PM PST, ID: 20847020

Rank: Genius

<Are we sure that Unicode applies to object names?>

I just named a form with Frm1111 repeated until it hit 64 chars, and Access accepted it so no, this wouldn't acutally apply to object names ... although there is some discussion on the newsgroups regarding this issue when calling forms ...

Assisted Solution

02.08.2008 at 03:15AM PST, ID: 20849179

My thought is that Access will accept it due to the statements of an earlier posting in this thread, but that when it comes to referring to it in code, then it gets messed up. The forms launch no problem, but they don't seem to be able to be referred to in VBA beyond the 32 characters, although I haven't yet fully tested that. (It is on my agenda for later today).

02.08.2008 at 03:25AM PST, ID: 20849208

Forgot to mention: It is also possible that the cause could be due to one other issue: Unless the user hits the plus key alongside the record, the subform does not open. It's possible that my code was referencing a non-existent (at the time) sub form. When a user hits the plus key, the sub form's Open Event executes. I'll be testing this today also.

02.08.2008 at 05:57AM PST, ID: 20850057

OK. Here's the definitive answer! The problem wasn't with the length of the form names, it was that the sub forms had not been opened at the time they were being referenced, so the error appeared. Once I (aka the user) opened the sub forms by clicking on the plus key to the left of the records, then the references worked! Gee, shouldn't MS have at least called for the Form Load Event to instantiate the sub form when the master form opens (in Datasheet/SubDatasheet mode)?

I'll hold this question open for a bit more, and then close it and award points to the highest bidder! :)

I'll also create a new thread for the Unicode questions I still have.

Accepted Solution

02.08.2008 at 09:16AM PST, ID: 20852101

Rank: Genius