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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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.

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
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 FyeOwner, Developing Solutions LLCCommented:
"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.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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.