How do I reference properties of sub-sub form from main form...

[jpang]

Hello,

I'm using Access 2003 and I need to revise properties (e.g. RecordSource) of
a sub-sub form from a button on the parent form via code. When I tried to
write the code, I could only see the 1st level sub form but not the 2nd level
subform as an available object of the parent form. I tried to write a module
outside of the parent form to manipulate the properties of the 2nd level
subform and then call that module from the parent form. Although the code
excuted successfully, the properties of the nested subform were not revised.

Any help will be appreciated.

 

[John W. Vinson]

Hello,

I'm using Access 2003 and I need to revise properties (e.g. RecordSource) of
a sub-sub form from a button on the parent form via code. When I tried to
write the code, I could only see the 1st level sub form but not the 2nd level
subform as an available object of the parent form. I tried to write a module
outside of the parent form to manipulate the properties of the 2nd level
subform and then call that module from the parent form. Although the code
excuted successfully, the properties of the nested subform were not revised.

Any help will be appreciated.

Referencing subforms is indeed tricky. You need to use the Name property *of
the Subform control*, the box on the form which contains a form object; the
Name property of that form object is irrelevant. To affect the property of a
form object in a subsubform you would use code like

Me!Subformname.Form!SubSubformname.Form.Recordsource = ...

Breaking it down:

Me!Subformname

refers to the Subform control named Subformname on the current form (Me!);

..Form

refers to the Form object within that subform;

!SubSubformname

refers to a subform control on that form object;

..Form

refers to the form object within that sub-subform;

..Recordsource

refers to a property of that form object.

 

[jpang]

Hello John,

Thanks for your reply. The code below did not work. Basically, Access did
not recognize

Me!Subformname.Form!SubSubformname

as a valid object. It only recognized as far as Me!Subformname

Any ideas? Thanks for your help.

 

[DaveT]

Me!Subformname.Form.Propertyname

From example app:

Debug.Print Me!TestTypeSUB.Form.RecordSource

prints qryTestTypeSUB (the recordsource for the subform)

Also see:

ACC: How to Refer to a Control on a Subform or Subreport
http://support.microsoft.com/kb/113352

 

[jpang]

Thanks Dave for the info. What happen if I want to do the following:

Me!Subformname.Sub-Subformname.Form.Propertyname?

It seems that Access cannot recognize a 2nd level subform nested in the 1st
level subform as an object. I cannot access the properties and controls of
the 2nd level subform.

Thanks for any help.

 

[Michel Walsh]

You can take a look at http://www.mvps.org/access/forms/frm0031.htm


You were close, try something like:


Me!Subform1.Form!Subform2.Form!ControlName.Enabled


Vanderghast, Access MVP

 

[jpang]

noop, doesn't work.

Basically,

Me!Subform1.Form!Subform2.Form!ControlName

is not recognized as an object from the main form.

 

[Tony Toews [MVP]]

Thanks Dave for the info. What happen if I want to do the following:

Me!Subformname.Sub-Subformname.Form.Propertyname?

It seems that Access cannot recognize a 2nd level subform nested in the 1st
level subform as an object. I cannot access the properties and controls of
the 2nd level subform.

I just created a web page on how to make this very easy to do. See
http://www.granite.ab.ca/access/referencing_a_control_on_a_subform.htm.
This same process should work for 2nd level subforms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Michel Walsh]

Works fine with Northwind.

Create a new form.

In design view, drag and drop existing form Categories on it.

On the main form you are creating, add a button, command1, and add the
onClick event subroutine handler:


------------------------------------------
Private Sub Command1_Click()
Debug.Print Me!Categories.Form![Product List].Form!ProductName.Name
End Sub
---------------------------------------------



Save the form. Open it, click on the button Command1. Observe, in the
immediate debug window, the desired result.




If it does not work in you case, you are probably not in the main form, or
use the object source name rather than the subform control name, or use
illegal name not enclosed with [ ], or something else, but the syntax is the
right one, as it can be observed with the preceding example.




Vanderghast, Access MVP

 

[DaveT]

Sorry, if I could read, I would have noticed that you said sub-sub in your
original posting.

This is not a common construction especially since we can't drop subforms
onto continuous forms..

Anyway, I'l look at this again today. Meanwhile, looks like you're getting
other responses.

Regards,

 

[jpang]

Thanks Tony. Your webpage helps!

I just created a web page on how to make this very easy to do. See
http://www.granite.ab.ca/access/referencing_a_control_on_a_subform.htm.
This same process should work for 2nd level subforms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[DaveT]

John's original code works for me. I tested with an actual app using top form
(single form view), embedded a subform (single form view) that in turn has
two subforms (continuous forms view).

My code of:

z = Me!CompaniesSUB.Form!CompanyDealTypeSUB.Form.RecordSource
Debug.Print z

Gives the correct answer: qryCompanyDealType

Notice that from the top down is single form view, singlel form view, and
then continuosu forms.