vba for hidden columns

[HansBades]

I have a form with check boxes representing field names on a different continuous subform of a different main form. I put a command button on the form with check boxes to hide/unhide fields on the second form depending on the selection. Every time I try to run the code I get an error message that the Access can't find the referenced form. I have tried editing the syntax and tried different forms, even had the other form open, and still I get the error message. Can you tell me what I am missing? Thanks.

Here is my code on the check box form

If me.form.checkboxfieldname=False


then Forms!MainForm!SubForms!FieldName.ColumnHidden=Tru e
End if

[Welshgasman]

That is the syntax, BUT what have you entered really?

Show name of mainform
Name of subform
Name of Control They are called Controls not Fields.

You would not use Me.Form.ControlName anyway, it would be Me.ControlName

EDit: If the control is on a subform then Me.SubformName.ControlName.
YOU have to get the names spelt correctly, or Access will just say it cannot be found.

[xps35]

You did not specify the name of the subform in your command.

[CJ_London]

And if referring to controls on a subform it is

subformname.form.controlname

[HansBades]

I am now using this syntax Forms!SubformName!ControlName and it is working but only if I have the subform independently open at the same time. If I open the header form that contains the subform it doesn't work. Any ideas how I can get this to work with just the header form open that contains the subform? Thanks.

[Welshgasman]

I am now using this syntax Forms!SubformName!ControlName and it is working but only if I have the subform independently open at the same time. If I open the header form that contains the subform it doesn't work. Any ideas how I can get this to work with just the header form open that contains the subform? Thanks.

That is just referencing the form as a main form, and a control on that form?
Look at the syntax again, better still understand it.

Here is something to review, which I hope helps.
If you did as I asked in #2, then someone might write it for you, not me, but you never know.

http://access.mvps.org/access/forms/frm0031.htm

[HansBades]

............................

[isladogs]

Check you are using the name of the control which is the subform container on the main form

For a datasheet subform, you can use

Me.SubformContainerName.Form.SubformFieldName.ColumnHidden=True

However, if the subform is single or continuous, then the above code won't work
Instead use:

Me.SubformContainerName.Form.SubformControlName.Visible=False

You may also need to hide the subform control label separately

[HansBades]

THANK YOU to all who posted! I got this to work. I can't believe how painful it was. I didn't realize that a subform is actually a control to the main form. When I realized that distinction based on many of your posts I searched for the name of that control in the main form. I could not find it in the property sheet. I could only find it by going into a subroutine of the main form and typing Me. and then letting the control selections come up. I found it but I was suprised by the name. It was the name of the subform with an "underscore" between two letters. Not sure if I did that or the system did that but once I figured what the name was I used the syntax that was provided in these posts and links and it worked. HOWEVER, the code I am running is in a different form to the main form. And it doesn't work unless the main form is open. If the main form isn't open I still get an error message that it can't find the form. I don't know if there is a way around the seeming requirement that you need the main form open but I was wondering if I could just program the subroutine to open the main form, run the code, and then close the main form, to be sure it runs correctly every time. Thank you all again. I learned alot today and obviously have a lot more to learn.

[Welshgasman]

A subform is not a control.
The subform sits inside a subform control.
If you click on the rectangle around the subform, that is the subform control.
I tended to name mine different to the subform so as to be able to identify which is which.
You cannot refer to any form controls for a form that is not open

[Micron]

Access often replaces spaces with underscores when left to its own to decide (IIRC when using a wizard) but if all you do is drag a subform control onto a form without giving it a meaningful name, you should get "Child0" or similar. When you click once on the subform control on the main form, you will in fact see it characterized as a subform/subreport in the "Selection Type" area, so it's arguable whether or not a subform container/control is a subform I guess. I prefer to call it a subform control or subform container to reduce ambiguity.

If you can't move the code to a form that you are trying to affect, then you will have to open the subject form before anything on it can be "found". You can open it hidden first if that helps, then show it when the changes are made.

[HansBades]

Thank you. Yes, that is indeed what happened with regard to the space in my subform name. Access put an underscore to fill the space and I didn't realize that at first.

[Welshgasman]

Thank you. Yes, that is indeed what happened with regard to the space in my subform name. Access put an underscore to fill the space and I didn't realize that at first.

Another reason not to have spaces in object/control names.

[Gicu]

I would suggest you move the code from the existing form (which I would call a "settings" form) where you select which fields to show (hopefully you have it bound to a local front-end settings table to allow each user to do their own selection) to the load or open event of the main form to in question. You would probably just need to change the code to use the settings table instead of the form controls:

'If me.form.checkboxfieldname=False then Forms!MainForm!SubForms!FieldName.ColumnHidden=True
If dlookup("checkboxfieldname","tblFieldSettings")=False then Forms!MainForm!SubForms!FieldName.ColumnHidden=True

Cheers,