Conditional Sub Sub from Visible = No

I have a Form with Sub Form and Sub Sub Form.  I would like the Visible Property of the sub sub form to be conditional on a field on the main form.

QCInspect is a Yes/ No field on the main form.  If QCInspect = yes, then Visible Property of Sub Sub Form = yes, otherwise  Visible Property of Sub Sub Form = No.

Thanks in advance for the assist.
Ken MilamEngineerAsked:
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To refer to a Sub form from your main form:


If you have a subform on THAT form (i.e. your sub-subform), do this:


Note that NameOfYourSubformCONTROL is the name of the Subform Control on those forms, and may or may not be the name of the Form you're using as a Subform.

To toggle visibility based on a Control on your form:

Me.NameOfYourSubformCONTROL.Form.NameOfYourSubSubFormCONTROL.Visible = (Me.Somcontrol = True)

In your case, you don't need a handle on that second Form object. You just want to make the Subform Control visible or not.

I'd also caution you against showing/hiding controls. Users typically don't like "magic" controls that seem to appear and disappear at random. Most UI experts would tell you that you should only hide controls if a user is never supposed to see them - like a Salary field that only Managers should see.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Ken,

in this particular case, it is reasonable to hide an inspection form if an inspection does not need to be done (and show if it does) -- Scott gave you what you need to change that.  

The trick to referencing a control on a sub-sub form is realizing that the first reference to a subform control is the container -- what it contains (Source Object is form or report name), where it is (Top, Left), how big it is (Height, Width), etc. To get to form properties or a control IN a form, .form must be added.  

Therefore, to get to a subform control inside another subform control, .form must be used after the first subform reference, but not after the second because it is the control itself you want to hide or show.


me.SubformControlname1.form.SubformControlname2.visible = True 'or False

to trigger the code, use the Current event of the mainform, which happens when mainform record changes, AND the AfterUpdate event of the relevant control (QCInspect), --- also consider the situation that the subform is already filled and then the mainform control is changed to No (false) ... what happens to data that shouldn't have been entered there ... or perhaps the mainform checkbox was incorrectly changed? consider different situations.

however, I want to add on to what Scott said about showing and hiding alternatives. Even though in this case, visible seems to be what you may want ... for other readers, and for other situations, it is good to also consider Enabled and Locked.

Enabled - Yes (true) if user can click in the control and select its contents; No (false) if they cannot
Locked - Yes (true) if user can modify contents of the control; No (false) if they cannot.

To select data, Enabled needs to be true. Sometimes you want the user to be able to select but not change. Sometimes you want a disabled control to not appear as gray. Consider your desired results. Here are combinations of Enabled and Locked, and how they behave:combinations of Locked and Enabled
Enabled can be set with conditional formatting

re: "field on the main form"

hope you don't mind, but the correct terminology is a control on a form; a control can contain a field. A control is bound if it has a Control Source that is the name of a field in the Record Source of the form/report. It is calculated if the Control Source contains an expression (formula starting with =), and unbound if the Control Source is blank.

have an awesome day,
Dale FyeCommented:
"Locked - Yes (true) if user can modify contents of the control; No (false) if they cannot. "

I think Crystal meant:  Yes (true) if user cannot modify the contents, No (false) if they can.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Dale -- yes!

thank you

Ken, -- correction:
Locked - Yes (true) if user cannot modify contents of the control; No (false) if they still can ... of course, in order to be able to change anything (in the user interface; code can still do it), Enabled has to also be true .. otherwise,  Locked cannot even be evaluated if the user can't get into the control ...

... tangential discussion bears consideration for other situations ...
Ken MilamEngineerAuthor Commented:
Thanks to you both - Experts Exchange rocks!  What a great resource.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Ken ~ happy to help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.