How to group controls on form

[iworkonline]

I have 5 items in a combo box placed on my form. When the user selects diffrerent items, I want to dislplay different controls, like. text boxes and one command button.

I want to combine these controls in one group. So I can have 5 groups for 5 different items in a combo box. I tried to put my controls in a box control and programmed the form to hide or display the box, on the form load method. but it did not hide the controls inside the box.

Is it possible to do in MS Access 2003. Thanks.

 

[vbaInet]

A box is not a container, it's just a control. A container-like control is a tab for example.

To get what you want, just create a sub and set the visible property by calling the funtion:

Private sub HideControls(hideMe as boolean)
    textbox1.visible = hideme
    textbox2.visible = hideme
    checkbox1.visible = hideme
    ... so on and so forth
end sub

 

[boblarson]

A simple way would be to use the control's TAG. You put the number in the TAG property and then you can display them very easily.

So, for example, if you have put the tag in each of them(perhaps some controls show up with more than one type and in that case you can place the number in there too).

If I have 5 controls with a tag of 1 and then I have 3 controls each with tag of 2 and 3 with a tag of 3 and 2 of them are for both 2 and 3 then I can use 23 in that tag.

With code I can then pass which number I want displayed by using:

Function ShowHideControls(frm As Form, strTAGtoUse As String)
Dim ctl As Control

For Each ctl In frm.Controls
     ctl.Visible = (Instr(1, ctl.Tag, strTAGtoUse) > 0)
Next ctl
End Function

And then you would call it from whatever event you needed by using something like

ShowOrHideControls Me, Me.ComboBoxName

And if the combo box returned a value of 2 then all controls with a 2 in the tag would show while all others would be set to invisible.

 

[vbaInet]

Adding to Bob's solution, if you know what section your buttons are placed then you can cut down on the amount of times the for each loop executes by pointing to that section. For example if all your buttons are in the Details section and it's called DetailsSection, you do this:

For Each ctl In frm.Section("DetailsSection").Controls

NB: I find the "for each in FORM.Controls" slow though because it goes through ALL controls on the form (including labels). If you don't have many controls on your form then you wouldn't notice the difference.

 

[boblarson]

Adding to Bob's solution, if you know what section your buttons are placed then you can cut down on the amount of times the for each loop executes by pointing to that section. For example if all your buttons are in the Details section and it's called DetailsSection, you do this:

For Each ctl In frm.Section("DetailsSection").Controls

NB: I find the "for each in FORM.Controls" slow though because it goes through ALL controls on the form (including labels). If you don't have many controls on your form then you wouldn't notice the difference.

Good point.

 

[boblarson]

Adding to Bob's solution, if you know what section your buttons are placed then you can cut down on the amount of times the for each loop executes by pointing to that section. For example if all your buttons are in the Details section and it's called DetailsSection, you do this:

For Each ctl In frm.Section("DetailsSection").Controls

NB: I find the "for each in FORM.Controls" slow though because it goes through ALL controls on the form (including labels). If you don't have many controls on your form then you wouldn't notice the difference.

Although it can't be that slow going through them (it is the comparisons which can be slow) as there can only be a MAXIMUM of 754 controls on a form over the lifetime of the form.

 

[vbaInet]

Not significantly slow but you can tell the difference in speed between the implicit call and the for all call. I tested this sort of thing when I was once implementing a Stack to hide/unhide tabs.

Yeah, the comparisons can be slow unless InstrB would run slightly faster. I still think the looping process too is a contributing factor because of the labels that is has to go through as well considering that each control has a label, double execution, and let's not forget the box/line controls one may use as well. It's almost like comparing "looping through an array to find an item" and "using Exists in a Dictionary". Well, not quite but you get the picture lol.

Oh, another thing for the OP, if you still want to free up your Tag property for other use, you can postfix the "2" to the name of your form and use ctl.Name and Right() instead. So the control name could be textbox_ and you do:

ctl.Visible = (Instr(1, Right$(ctl.Name, 1), strTAGtoUse) > 0)

 

[iworkonline]

Hi Bob

In post #3 I don't understand the following highligheted section in the following line

If I have 5 controls with a tag of 1 and then I have 3 controls each with
tag of 2 and 3 with a tag of 3 and 2 of them are for both 2 and 3 then I can use 23 in that tag.

What does 2 & 3 referring to? Do they refer to control number 2 and 3

ctl.Visible = (Instr(1, ctl.Tag, strTAGtoUse) > 0)

I am not sure about the values in ctl.Tag and strTAGtoUse.

Thanks.

 

[dkinley]

I am not sure about the values in ctl.Tag and strTAGtoUse.

ctl.Tag don't do anything with it - it is a reference for VBA to get the information out of the Tag property of the control.

strTAGtoUse is a place holder for a string or string variable (str).

So if you have set the Tag of a control to "Choose Me" and you want it to appear then on the AfterUpdate event of a control, or OnCurrent of the Form, or like Bob said, anywhere an event occurs, you would use ...

ShowHideControls(Me, "Choose Me")

And the subsequent function would show those controls with the "Choose Me" tag. The 'Me' bit is the form information so you could reuse the function for an infinite number of forms.


-dK

 

[dkinley]

What does 2 & 3 referring to? Do they refer to control number 2 and 3

It could ... it could mean anything, although in this context I think it meant Group 1, Group 2, etc. I used the example of "Choose Me" in the previous post instead of "Group 1".

What both posters were saying though is to get some reuse, suppose you had a control that needed to be visible for Group 1 and Group 3, you could use 13 in the Tag property of a control.

I've used this method but used the comma as a separator because of the myriad of things going on - but once you get this down and completely understand, you can move into the advanced usage.

-dK

 

[boblarson]

Yep, that is what I was trying to get across.

In a real life scenario, I had forms that had controls for admin users as well as read only users as well as edit users. I would use their security level which was stored in a SQL Server table as Admin, RO, or RE.

And if all three needed a control to be active the tag on that control was Admin,RO,RE
and if an admin was only supposed to see it only Admin would be in the tag.

 

[iworkonline]

I am getting the error msg: "You cannot hide the control that has focus"

Private Sub Cust_Scen_AfterUpdate()
If Me![Cust_Scen] = "3" Then
    r = ShowHideControls(Me, "lbl15")
    End If
End Sub

[Cust_Scen] is the combobox that has 5 options and I have set the label15.tag = lbl15.
I want label15 to be dispalyed only when combo box value is 3.

Thanks for your help.

 

[vbaInet]

The message is quite self-explanatory so all you do is move the focus to a control (that isn't one of the ones you want to hide) before disabling it.