Need to make subform not visible until needed

[patrickmcdiver]

I have a subform in a form and I want it to remain closed or not visible until opened by a command button. How do I do this.

 

[missinglinq]

In Form Design View select the subform control (not the form on it, the control itself) and goto Properties - Format and set the Visible property to No.

Then in the code behind your command button use

Me.SubFormControlName.Visible = True.

 

[patrickmcdiver]

Thanks for the help!

 

[missinglinq]

That's why we're here!

 

[SirStevie3]

Then in the code behind your command button use

Me.SubFormControlName.Visible = True.

sorry for the necro bump, but can someone explain what "in the code behind your command button" means?

I'm trying to accomplish the same thing as the OP but im getting an error message saying Microsoft Office Access can't find the macro 'me.'

i should probably throw this out there - i've never made/used a macro.

Thanks!

 

[Roku]

The code supplied by missinglinq is VBA. Your reply indicates you are using the Macro editor, which is not the same thing.

In you command button event list, for the Click event, select [Event Procedure] from the drop-down menu. This will open the VBA code window, with the procedure block added. Put the code line inside that.

In my opinion, one should stick to either VBA or macros. Mixing the two is possible, but can make maintenance difficult. VBA gives much better control overall in Access projects. I haven't come across anything in the macro editor which can't be done in VBA (... now I await the cacophony from members telling me I'm wrong!)

 

[SirStevie3]

Thanks so much Roku!!

One more question for anyone out there - can i also use this button to "refresh" the subform?

my subform is based on a query which uses some combo boxes found on the main form as criteria. so i'd like to make my button be both a "make visible" and "refresh" button. can i do that?

 

[IndigoStickman]

I believe simply adding a line below the first, but like this:

[B]Me.SubFormControlName.Refresh[/B]

Should do it? May even be better to put that line above, so it refreshes the form prior to popping it up. Someone will no doubt correct me if I've made a schoolboy error as I'm still green with VB myself but getting there now.

I use a lot of Me.Refresh commands, but not normally with a subform involved so forgive me if I misinformed you.

 

[David R]

In my opinion, one should stick to either VBA or macros. Mixing the two is possible, but can make maintenance difficult. VBA gives much better control overall in Access projects. I haven't come across anything in the macro editor which can't be done in VBA (... now I await the cacophony from members telling me I'm wrong!)

No argument from me - the fact that you can convert Macros to VBA but not vice versa should tell you something!

 

[missinglinq]

...In my opinion, one should stick to either VBA or macros...VBA gives much better control overall in Access projects. I haven't come across anything in the macro editor which can't be done in VBA

None of the experienced developers. here, are going to argue with that statement! Actually, just the opposite is true; VBA code is much, much more flexible, and allows you to easily do things that Macros make either difficult or impossible.

As for maintenance, with VBA code you have the majority of things that are driving a given Form right there in front of you, in a single place. Using Macros, you have to visit each and every Macro, for each event of each and every Control, as well as the Form, itself, to see what's going on! Not a very efficient way of doing things!

Linq ;0)>

 

[Petrol]

I realise this is a pretty old thread, but I have a similar problem to Patrick's original post except that I have about four small subforms on the form and I want the user to be able to switch any or all of them on and off. So instead of a command button I used a series of check boxes bound to the properties Me.[subformControlName].[Visible]. This went over like a lead balloon. When I just used the above as the control source I got "Control can't be edited; it's bound to unknown field [[Me].[subformControlName]]" and the system removed the Visible property from the control source. When I prefixed the source with an= sign I got "Control can't be edited; it's bound to the expression [subformControlName].[Visible].

Is it possible to set and unset the Visible property using checkboxes?

 

[The_Doc_Man]

Checkboxes can be bound to data in a recordset or can be unbound. The [Visible] property does not reside in a recordset so a checkbox cannot be bound to it.

You COULD leave the checkboxes in place but unbound. Then, when someone checks one of the boxes, you COULD have an OnChange event (one separate event for each such box) and have that code set the [xxxx].[Visible] property corresponding to that checkbox.

Now... to a different aspect of the same question. This is a bad design idea, in my opinion. Users should not control controls; they should only control data. YOU should control the controls based on the context of the situation. Set tab order according to how you want the controls visited and if the user enters some particular data in control [A], maybe you want an On_LostFocus event for [A] to turn off based on the content of [A]. Or maybe you want a row of function buttons representing what the user could do, but have the user click a function-select button BEFORE showing any controls at all. Then set up the controls for the selected function and make the other controls stay invisible (including the other function-select buttons). Then, when the user finishes the function, reset ALL of the function buttons and make the other controls invisible again until another function is selected.

Based on the fact that Access doesn't like focus-diddling under certain cases (leading to "You can't hide a control that has focus" errors), the idea of selecting a function first - and then having THAT function do control manipulation ending with a [xxxx].SetFocus to the first data entry point - might make the form look more professional.

 

[Petrol]

Thanks, Doc Man, for taking the time to reply and for explaining why binding the check box to the Visible property didn't work.

Can you clarify what is the philosphical objection to having users control controls? I would have thought that if the user wants to make a control visible then he needs some way to set the Visible property of that control. Being something of a greenhorn at VBA I didn't fully understand your proposed solution, but it looks rather more complex than I wanted. All I want is a row of buttons, or check boxes, which the user can click to make selected subforms visible or invisible.