How to reference deeply nested forms in VBA
I have been pulling [what's left of] my hair out trying to reference a fourth-level-down Sub-Form in a Form using VBA and keep getting road-blocked. Can anyone give me the correct syntax? I am specifically trying to change the "AllowEdits" property setting on that lower Form. (In other cases, I am trying to invoke the Requery action.) Remember, We're talking four levels down. I am OK with one Sub-Form down, but it's beyond that where it all jams up.
Actually ... one more .Form
Me.Subform1.Form.Subform2.
mx
Me.Subform1.Form.Subform2.
mx
My post assumes that each subsequent subform is embedded in the subform above, ie in another subform
mx
mx
You refer to a subform like this:
Me.NameOfYourSubformCONTRO
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
Me.NameOfYourSubformCONTRO
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
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
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
ASKER
Wow, so many responses. THANKS ALL!
As for: DatabaseMX:
Me.Subform1.Form.Subform2.
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):
As for: DatabaseMX:
Me.Subform1.Form.Subform2.
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):
ASKER
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=
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.
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=
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.
ASKER
Hmmmm I wonder if the length of the form name is killing it:
frm_MCGCS_TGUTOE_SubForm_4
(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...
frm_MCGCS_TGUTOE_SubForm_4
(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...
ASKER
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.
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.
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.
If the form contained in sfcNest2 contains a Subform control named sfcNest3, do this:
Me.sfcNest1.Form.sfcNest2.
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.
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.
If the form contained in sfcNest2 contains a Subform control named sfcNest3, do this:
Me.sfcNest1.Form.sfcNest2.
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.
The original syntax I posted should have worked - so, it seems something else is going on here ??
mx
mx
ASKER
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
Strange. Here is the exact line of test code that fails due to bad reference:
MsgBox Me.frm_MCGCS_TGUTOE_SubFor
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_SubFor
But
MsgBox Me.frm_MCGCS_TGUTOE_SubFor
Does not
(Ignore the fact that I start at "3". We pulled Forms "1" and "2")
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
Strange. Here is the exact line of test code that fails due to bad reference:
MsgBox Me.frm_MCGCS_TGUTOE_SubFor
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_SubFor
But
MsgBox Me.frm_MCGCS_TGUTOE_SubFor
Does not
(Ignore the fact that I start at "3". We pulled Forms "1" and "2")
ASKER
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.
Can you .zip and post your database to www.ee-stuff.com, or perhaps just the forms needed to recreate this?
ASKER
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.
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.
Are we sure that Unicode applies to object names?
mx
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How did you accept your own answer as the solution? Wasn't aware you could do that ...
I wasn't aware that we were discussing Datasheet subforms ... in the future, please be very specific about those types of things. Datasheet subforms are never populated until you actually "view" one by clicking on the +, and had you mentioned that early on this would have been solved much more quickly.
I wasn't aware that we were discussing Datasheet subforms ... in the future, please be very specific about those types of things. Datasheet subforms are never populated until you actually "view" one by clicking on the +, and had you mentioned that early on this would have been solved much more quickly.
http://www.mvps.org/access/forms/frm0031.htm
Basically - from the Main form:
Me.Subform1.Form.Subform2.
mx