Hiding/showing groups of controls based on combobox value (1 Viewer)

[Chumpalot]

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

 

[Gasman]

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?

 

[gemma-the-husky]

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.

 

[Chumpalot]

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

 

[Chumpalot]

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.

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.

 

[Uncle Gizmo]

list of countries (around 300 values).

But there are only around 200 countries?

 

[Gasman]

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?

 

[Chumpalot]

But there are only around 200 countries?

There are 190 odd countries + around 100 organisations and companies.

 

[Chumpalot]

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?

I understand all that but have no idea on how to actually construct the IF statement on the Combobox.

 

[Gasman]

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]

How about this;-

 

[Uncle Gizmo]

How about this;-

Update:-

Change the name of the command button to:- btnEditGrps

and Add this code to the On Click Event:-

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

 

[Chumpalot]

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?

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.

 

[Chumpalot]

Update:-

Change the name of the command button to:- btnEditGrps

and Add this code to the On Click Event:-

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

Thanks, I'll give this a go also.

 

[MarkK]

You can also make groups of controls directly in your code, like...

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

Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)

and now you can set the group visibility by index...

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

 

[Chumpalot]

You can also make groups of controls directly in your code, like...

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

Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)

and now you can set the group visibility by index...

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

Thank you for this. Would this go in the On Load event for the form?

 

[NauticalGent]

You can also make groups of controls directly in your code, like...

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

Const CTRL_GROUPS = Array(PSN_CTRLS, ORG_CTRLS)

and now you can set the group visibility by index...

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

I was looking for something like this only yesterday. Perfect for what I need - simply brilliant.

 

[MarkK]

I would declare the constants as global to the form, so outside of any sub routine, like...

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

 

[MarkK]

...simply brilliant.

ha ha ha, somewhat clever at best.
Cheers,