Hiding/showing groups of controls based on combobox value

C

Chumpalot

Registered User.
Local time
Today, 02:19
Joined
Mar 12, 2015
Messages
76
Morning everyone,

Despite a fair few searches I've not been able to find the answer to a problem I'm having.

I have a combobox with a list of countries (around 300 values). These countries all have a value between 1 and 10 depending on what status they have. On the same form is a subform with 10 groups of controls. I have grouped them together using the Tag property (we can call these 'A', 'B','C' etc...)

I'd like these groups of controls to be shown or hidden based on the value selected in the combobox. For example, if "Afghanistan" is selected then control groups A and C are shown but B is hidden, if 'The Bahamas' is selected then all groups are shown etc.

I'm hoping this is possible. I'm familiar with doing this with fewer controls but there are around 80 in total on the form and I'm not sure the method I know of is the most efficient (referencing each one by name individually).

I hope all of that makes sense. Thanks for any help in advance.

Cheers
David
 
Most people would use the Tag property, as you have.
However you need a way of identifying that Afghanistan allows A & C ? etc
Or Bahamas is all?

A table perhaps with Country and code, one record for each?, so Afghanistan would have two records?
 
I think I would store a display flag in another column in the country table, and add it as a column in the combo box.
That way, you can manage the data without having to rebuild the database every time you want to add a new country, or even just change what you do.

You then need code in the after update event for the combo box, to change the form layout depending on the "tag" value for the country.
I would only have a single value in the country column, not a subtable.

If you have no more than 8 classes of action, you could store a setting based on bit positions. So bits 01001001, a numeric value of 73 (64+8+1) would indicate this country is in categories A,D and G. (bits 1,4 and 7).

This would give you 255 combined settings from 8 choices - you mention 10, so I am not sure if that is 10 different flags, or 10 combinations of a smaller number of flags. If the former, then you need a value bigger than a single byte. I am not sure offhand if bit testing can be used with a two byte value, but I assume it can. If not, you could still achieve the same effect, but it would take a little more code.

Personally I wouldn't want to do this by using a tag on form control.
 
Last edited:
Thanks for the reply.

Yes, I have this table already although each country/organisation has only have one value assigned to it (between 1 and 10). These values would then dictate which groups to show/hide.

My problem is I have no idea how to code it! I guess I'm looking at the AfterUpdate Event on the Combobox but how do I build in the Tag references into the IF statement?

I think I'm looking at something like this:

AfterUpdate Event

IF me.cbocountryselect.Column(1).Value = 1 Then
This is where I have no clue > Controls Where Tag = "A" and C" .Visible = True (I'd like them to visually change too) And Controls Where Tag = "C".Visible = False
 
gemma-the-husky said:
I think I would store a display flag in another column in the country table, and add it as a column in the combo box.
That way, you can manage the data without having to rebuild the database every time you want to add a new country, or even just change what you do.

You then need code in the after update event for the combo box, to change the form layout depending on the "tag" value for the country.
I would only have a single value in the country column, not a subtable.

If you have fewer than 8 classes of action, you could store a setting based on bit positions. So bits 01001001, a numeric value of 73 (64+8+1) would indicate this country is in categories A,D and G. (bits 1,4 and 7).

Personally I wouldn't want to do this by using a tag on form control.
Click to expand...
Thank you for this. This all made sense until you started talking about bit positions! I think I can probably just about get away with using the Tag property but I will definitely look this up. Cheers.
 
So you have a CountryTag table?

CTID
CTCountry
CTTag

1,Afghanistan,A
2,Afghanistan,C
3,Bahamas,A
4,Bahamas,B
5,Bahamas,C

Then you get a recordset for the country and process the tags?

If a country with a value of 1 always has A & C, then you could do it by those values.?

Either way, you need to relate the two?

Why can't you use the number for the tags?
 
Gasman said:
So you have a CountryTag table?

CTID
CTCountry
CTTag

1,Afghanistan,A
2,Afghanistan,C
3,Bahamas,A
4,Bahamas,B
5,Bahamas,C

Then you get a recordset for the country and process the tags?

If a country with a value of 1 always has A & C, then you could do it by those values.?

Either way, you need to relate the two?

Why can't you use the number for the tags?
Click to expand...

I understand all that but have no idea on how to actually construct the IF statement on the Combobox.
 
Once you work out how you want to implement it, then I would write a function to loop through the controls and show/hide them.

You have not answered my last question?
 
Uncle Gizmo said:
How about this;-
Click to expand...

Update:-

Change the name of the command button to:- btnEditGrps

and Add this code to the On Click Event:-

Code: 
Private Sub btnEditGrps_Click()

    If IsNull(Me.cboSelectCountry) Then
    
    MsgBox " Select a Country"
            With Me.cboSelectCountry
                .SetFocus
                .Dropdown
            End With
        Exit Sub
    End If


    DoCmd.OpenForm "frmCountryAttributes", acNormal, "", "[ID]=" & cboSelectCountry, , acNormal


End Sub     'btnEditGrps
 
Gasman said:
Once you work out how you want to implement it, then I would write a function to loop through the controls and show/hide them.

You have not answered my last question?
Click to expand...

Apologies, I missed that. You're right, I could. I'm going to have a play around and see what I can come up with. I'm very much a dabbler when it comes to Access and so some of the basic concepts are alien to me. I usually get by by tinkering and some trial and error.
 
Uncle Gizmo said:
Update:-

Change the name of the command button to:- btnEditGrps

and Add this code to the On Click Event:-

Code: 
Private Sub btnEditGrps_Click()

    If IsNull(Me.cboSelectCountry) Then
   
    MsgBox " Select a Country"
            With Me.cboSelectCountry
                .SetFocus
                .Dropdown
            End With
        Exit Sub
    End If


    DoCmd.OpenForm "frmCountryAttributes", acNormal, "", "[ID]=" & cboSelectCountry, , acNormal


End Sub     'btnEditGrps
Click to expand...

Thanks, I'll give this a go also.
 
You can also make groups of controls directly in your code, like...
Code: 
Const PSN_CTRLS = Array(Me.FirstName, Me.LastName, Me.DateOfBirth)
Const ORG_CTRLS = Array(Me.Company, Me.DBA, Me.DateFounded)
and then you can group those groups in another group
Code: 
Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)
and now you can set the group visibility by index...
Code: 
Private Sub SetVisible(GroupIndex as integer, Optional State as Boolean = True)
   dim ctrl as control
   For Each ctrl in CTRL_GROUPS(GroupIndex)
      ctrl.Visible = State
   Next
End Sub
And it's pretty easy to return an index from a combo...
 
MarkK said:
You can also make groups of controls directly in your code, like...
Code: 
Const PSN_CTRLS = Array(Me.FirstName, Me.LastName, Me.DateOfBirth)
Const ORG_CTRLS = Array(Me.Company, Me.DBA, Me.DateFounded)
and then you can group those groups in another group
Code: 
Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)
and now you can set the group visibility by index...
Code: 
Private Sub SetVisible(GroupIndex as integer, Optional State as Boolean = True)
   dim ctrl as control
   For Each ctrl in CTRL_GROUPS(GroupIndex)
      ctrl.Visible = State
   Next
End Sub
And it's pretty easy to return an index from a combo...
Click to expand...

Thank you for this. Would this go in the On Load event for the form?
 
MarkK said:
You can also make groups of controls directly in your code, like...
Code: 
Const PSN_CTRLS = Array(Me.FirstName, Me.LastName, Me.DateOfBirth)
Const ORG_CTRLS = Array(Me.Company, Me.DBA, Me.DateFounded)
and then you can group those groups in another group
Code: 
Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)
and now you can set the group visibility by index...
Code: 
Private Sub SetVisible(GroupIndex as integer, Optional State as Boolean = True)
   dim ctrl as control
   For Each ctrl in CTRL_GROUPS(GroupIndex)
      ctrl.Visible = State
   Next
End Sub
And it's pretty easy to return an index from a combo...
Click to expand...
I was looking for something like this only yesterday. Perfect for what I need - simply brilliant.
 
I would declare the constants as global to the form, so outside of any sub routine, like...
Code: 
Option Compare Database
Option Explicit

Private Const PSN_CTRLS = Array(Me.FirstName, Me.LastName, Me.DateOfBirth)
Private Const ORG_CTRLS = Array(Me.Company, Me.DBA, Me.DateFounded)
Private Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)

...so they are available to all code in all routines in the module
 
You must log in or register to reply here.

Similar threads

O
Conditional Formatting Based on Previous Group Value
Replies
10
Views
207
arnelgp
arnelgp
brucemc777
Show/Hide a control based on whether or not 4 other controls have contents
Replies
4
Views
142
brucemc777
brucemc777
K
Web Browser control : Opening the link in the same tab
Replies
7
Views
345
theDBguy
theDBguy
R
Open form based on field value
Replies
6
Views
245
azhar2006
A
amorosik
Combobox behaving strangely
Replies
18
Views
497
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom