Hiding/Un-hiding Form Controls with a Module

[Galaxiom]

A much simpler technique to conditionally hiding controls is by using a Tab Control. It requires hardly any code at all. I will describe an example where controls are conditionally displayed being triggered by a master control getting the focus. Similar principles can be applied for other purposes.

Place all the controls that are to be made visible for a particular master control on a page of the TabControl. Where a child control needs to be displayed on more the one (but not all) of the pages, simply copy and paste it onto the extra pages. It will need a different name on each page but it can have the same ControlSource.

Configure the properties of the TabControl (not the Pages) so that it isn't apparent by setting its Style to None (removes the little tabs at the top), the BorderStyle and BackStyle to Transparent. Now the TabControl can cover the whole form if you like without occluding the other controls on the form itself. (Of course you will have to make sure you don't locate the Page's controls in the same place of those on the form.)

The TabContrl Page to be displayed can be selected by changing the Value property of the TabControl to the required Pages index. Let's call the TabControl something like "ConditionalControls".

The appropriate ConditionalControls page can be configured in the Tag Property of the master controls. Use this one line function in the GotFocus Event of each of the master controls.

Private Function ShowConditionalControls()
     Me.ConditionalControls = Me.ActiveControl.Tag
End Function

That is all there is to it.

 

[Smokeeater]

Ridder,

Sorry, been offline for a little bit. I was wondering if I could send you my db so you could take a look at how I handled the use of the tag function.

 

[isladogs]

Agree with the previous comments.

If you strip down your database & post it here, I will have a look at it unless someone does so first.

Two benefits:
1. You may get a range of responses
2. Others can learn from the thread

 

[Smokeeater]

DB attached.

Recap.

frmMaster is the main form, with 2 subforms [frmSection_sub] and [frmGroup_sub]. I used the Tag control to identify all controls as A, for those to be available All the time, and then T2, T3, and T4 for the respective controls to be manipulated.

[frmSection_sub] has the controls, identified as T2, T3, and T4. that need to be hidden based on the following conditions:

1. New Records, all T2, T3, and T4 controls hidden.

2. Existing Records where the first field under the first assessment criteria (Safety Cross Up To Date) is null. Fields are [Saf_Cross_UTD2], [Saf_Cross_UTD3], [Saf_Cross_UTD4] respectively.

3. T1 fields are always visible.

So in several cases there will be data in this first field for T2, but not T3, and in T2, and T3, but not T4. For existing records, this will need to be check when the record is selected, so it does not hide existing data.

Also, the fields will always be needed based on the correct order. T3 would not be needed if T2 is not used.

Finally, 3 buttons on [frmSection_sub] to unhide T2, T3, and T4 as needed. I have one button there now that is currently set to hide T2 fields (just playing with the code) and it works fine if I just have [frmSection_sub] open by itself, but when it is opened as part of [frmMaster] the button does not work.

I know...a train wreck. And I have to admit, Galaxiom's idea of using tab controls in the subform interests me. I have other databases that do it. This was not my db to start with, I "inherited" it, and was asked if I could get it to function as described.

Thanks in advance!

 

[isladogs]

I know...a train wreck. And I have to admit, Galaxiom's idea of using tab controls in the subform interests me. I have other databases that do it. This was not my db to start with, I "inherited" it, and was asked if I could get it to function as described.

I have to agree with the train wreck comment.
TBH you've done well to create a layout that could function well despite being so cluttered

Anyway, I've done part of this for you & hopefully doing so will allow you to complete it.
The problem with the module code for your situation is that it acts on the active form
So even though your buttons are in the subform, the active form is the main form so it didn't do anything to your subform controls

I've solved this as follows
1. Copied ShowControls procedure to your subform & changed it to a private sub that will only affect the subform itself
Changes shown in RED

[COLOR="Red"]Private Sub ShowSubControls[/COLOR](State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to visible or not according to the control tag value
    'only affects controls in this subform
    For Each ctrl In [COLOR="red"]Me.[/COLOR]Controls
        Select Case ctrl.ControlType
        
        Case acPageBreak
            'no code here - these can't be locked
        
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Visible = State
        
        End Select
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume Exit_Handler
    
End Sub

I've copied & modified LockControls & EnableControls to the subform in the same way in case you need them

I've also modified the Hide button code so it can be used to show the controls again

Private Sub cmdTeam2_Click()

    If Me.cmdTeam2.Caption = "Team 2 Hide" Then
            Me.cmdTeam2.Caption = "Team 2 Show"
            ShowSubControls False, "T2"
    Else
            Me.cmdTeam2.Caption = "Team 2 Hide"
            ShowSubControls True, "T2"
    End If

End Sub

Finally I've added two more buttons for T3 & T4 controls

I also think it would be a good idea to change to a tabbed form to simplify this for users.
That might also make it easier to use the ShowControls procedure as you would just have one form rather than a form & 2 subforms

 

[Smokeeater]

Ridders,

I travel to London every year or so to see family. I would like to buy you a pint or two to thank you for your guidance!

I understand now what you did, and what I should have done from the start, however, I do have one question. In the first part of the code you define "Tg1 As String, Optional Tg2 As String, Optional Tg3 As String..." and then the Tg1, Tg2, etc. is used to determine whether the controls are visible or not. I assume Tg2 is what I am calling "T2", Tg3 = "T3", and so forth?

I know how to also have it look for a null control, so that it does not hide the T2, T3, and T4 control on existing records that have data.

And I agree about the tab controls....I actually prefer that method due to how clean it keeps everything, but as I mentioned, this was a "here, can you fix this?" project. I will talk to the end users about the tab function, or better yet, I will work up a model to show them.

Thanks again ridder!

 

[Galaxiom]

Private Sub ShowSubControls[/COLOR](State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

The optional parameters could be implemented with a parameter array which would allow for any reasonable number of optional tag strings.

 

[Smokeeater]

Galaxiom, okay thanks. Now I understand the purpose of that portion of the code.

 

[isladogs]

Glad you're happy with this.
Sounds like you visit London more often than I do.
Please feel free to drink to my health without me.

As Galaxiom said, the ShowControls code requires at least one value but can have up to 6 in one line of code
If you need to do more than that at once, put the extras in another ShowControls line or add more optional arguments tgl7 etc.

Good luck with your project from here

 

[Smokeeater]

Thanks Ridders. I don't get over as much as I like. Some years I was able to go 2 or 3 times, but now I am lucky if I can get over 1 time per year. This year it will be zero. Outside of the tragic consequences of 9/11, it really changed air travel, and the associated security costs tied to the airfares. I could usually fly roundtrip for about 200-250 pounds, now it is twice that, and a lot more hassle.

Thanks again! Looking forward to getting the inbound side of this db completed so I can get started on the reports.

 

[Galaxiom]

As Galaxiom said, the ShowControls code requires at least one value but can have up to 6 in one line of code
If you need to do more than that at once, put the extras in another ShowControls line or add more optional arguments tgl7 etc.

This is what I meant by a Parameter Array. It is a better alternative than defining a fixed number of optional arguments.

 

[isladogs]

This is what I meant by a Parameter Array. It is a better alternative than defining a fixed number of optional arguments.

In principle I agree with you but it was more for information to the OP.

I use parameter arrays regularly in other parts of my databases.
For example in code used to analyse downloaded JSON data

However, as I have only needed to modify the ShowControls items a couple of times in over 5 years of widespread use throughout all my databases, its never been a priority making the change. It's more a case of when I get round to it