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.
LVL 1
FaulkenatorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Actually ... one more .Form

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

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
My post assumes that each subsequent subform is embedded in the subform above, ie in another subform

mx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
FaulkenatorAuthor Commented:
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):
0
FaulkenatorAuthor Commented:
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.

0
FaulkenatorAuthor Commented:
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...
0
FaulkenatorAuthor Commented:
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.

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The original syntax I posted should have worked - so, it seems something else is going on here ??

mx
0
FaulkenatorAuthor Commented:
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")
0
FaulkenatorAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you .zip and post your database to www.ee-stuff.com, or perhaps just the forms needed to recreate this?

0
FaulkenatorAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are we sure that Unicode applies to object names?

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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 ...
0
FaulkenatorAuthor Commented:
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).
0
FaulkenatorAuthor Commented:
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.
0
FaulkenatorAuthor Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
There is no guarantee as to when a or the order of a form /  subform object(s) are being built in memory.

Once all all objects are 'visible', then original syntax I posted should work.

mx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.