@RobDuggan
You can make references to any open form, regardless of its embedded depth inside a form or subform without any guesswork by using the locals window. If you want to find out how to use that method, let me know.
For now here's a test you can perform right now:
1. Open the form you want to reference in design mode
2. Go to properties pane
3. Other tab
4. Make sure Has Module is Yes
5. Go to the VBA window and see what name it has in the explorer, for example, if you had named this form "frmMyName", it will appear as "Form_frmMyName" there.
6. Now just go to the code where you want to reference this form and directly use "Form_frmMyName". For example, if you wanted the value of a textbox named "txtMyTextbox" inside "frmMyName", then do this: MsgBox Form_frmMyName.txtMyTextbox
7. Done.
Now, of course, an experienced developer can skip steps 1 to 5 and simply add "Form_" as a prefix to their references, as they will know whether their form includes a module.
what if Form_FormName is open both as subform and single form?
or Form_FormName is a multiple subform as in the case of "Calendar Appointment/Schedule")?
In those cases, Form_FormName would be one of the instances. If you have that kind of instancing, then use other methods to have the applicable level of control. For your examples:
Case 1a: you use a variation of Forms("ParentForm").Form.Controls("SubformControlName").Form for a simple Form/Subform
Case 1b: you use Forms("FormName").
Case 2: you use a variation of Forms("ParentForm").Form.Controls("SubformControlName").Form.
However, let's say you had nested forms like main > sf1 > sf2 > sf3 > sf4 > sf5 or similar, and in sf5 you had your case 2, well, use the code name of the form that sf5 is holding, if it's called "Form5", then you can use things like Form_Form5("SubformControlName").Form
or Form_Form5.Controls("SubformControlName").Form
or Form_Form5.Form.Controls("SubformControlName").Form
or Form_Form5.Form.Controls.Item("SubformControlName").Form
or Form_Form5!SubformControlName.Form
or other variations
Instead of Forms("ParentForm").Form.Controls("sf1").Form.Controls("sf2").Form.Controls("sf3").Form.Controls("sf4").Form.Controls("sf5").Form.Controls("SubformControlName").Form
Which needs some line breaks to be readable, or even this, which is also hard to read. Forms!ParentForm.Form!sf1.Form!sf2.Form!sf3.Form!sf4.Form!sf5.Form!SubformControlName.Form
For single instance, which is the most common scenario ever, this method is fine. I invite anyone to test it and stop suffering with this.
Sure, my method basically requires the locals window open, the immediate window open, a procedure to test and the Stop command. If you need to reference a form from a module, for example, then make a procedure, declare a form variable, set it to the form you want and add the Stop command at the end.
Code:
Sub test()
Dim f As Form
Set f = Forms("TestForm")
Stop
End Sub
Open the form, run the Sub and you should see the variable you declared in the Locals window with a plus sign to the left.
Now click the plus sign to the left and you'll see the form's properties and the objects embedded within it. The Locals window is handy enough to tell you the name of the member, its value and its type. How do you test these? I recommend the immediate window. Do this, for example: ?f.Width
If you use the question mark notation, you'll get the value of the member as presented in the Locals window. You will notice that some values appear between angle brackets, if you attempt to access those members, you'll get the error between said angle brackets. I hope that you can anticipate why this tool is such a time saver.
I recommend that you familiarize yourself with the fact that the form variable has an embedded reference to its controls, both at the bottom of the tree and within the Controls member. Another thing, since we declared the f variable as form, we will not have intellisense for its members, if we want intellisense for that, declare the f variable as its code name, like Form_FormName.
Now, referencing other forms should not be difficult with this preamble. Let's say we want to reference a control inside a subform from a module using the test() routine from above.
Open the form and run the routine from the module.
Expand the Controls member, write the following in the immediate window: ?f.Controls (do not Enter yet)
Now look at the contents of that member.
You'll see item1, item2, ... etc. Not very helpful. Now look at the Type column, you'll see a Subform type there, that's your item. Expand that and check its Name property. Now that you have the name of that control, you can now add it to the question mark notation from before, because now we have the control name we're looking for in order to use the Controls member: ?f.Controls.Item("SubformControlName") or ?f.Controls("SubformControlName") (do not enter yet)
Now that you have a reference, you can verify it by seeing if a property like .Name returns something. In fact, keep looking inside the Subform control members, the locals window has a lot of things you can use to verify your reference, I like .SourceObject, because it returns the name of the form that the subform control is holding, so I do this ?f.Controls("SubformControlName").SourceObject (Enter now). What did it return? If you keep looking around the Locals window in its Type column, you will notice the .Form property is of type Form_FormName. Expand that, repeat the same, expand controls and find the control type you want, check its name, write it down, verify. ?f.Controls("SubformControlName").Form.Controls("SomeTextbox").
If I didn't lose you, this is meant to make you look at ALL your options at once, the Locals window has the answers you're looking for, just inspect it and see the wealth of undocumented data it presents you under the hood. To summarize:
1. Expand form variable .Form
2. Expand Controls member .Form.Controls("______")
3. Locate the control using the Type column
4. Get its name and set it .Form.Controls("ControlName")
5. Verify with the immediate window .Form.Controls("ControlName").Name or .Form.Controls("ControlName").SourceObject
6. Repeat if you have a nested subform
If you want to do the inspection from a form, then use some event, like Load. The rest is the same. Always keep the Locals window open, there is a lot of stuff to still talk about it, like the other members of the form object. This does not apply only to forms, but to any other object.
