[TIP] Expand/Collapse a Subform or a Section of your form/page (1 Viewer)

Zak14

Registered User.
Local time
Today, 19:31
Joined
Jun 27, 2014
Messages
166
This is a neat way to expand/collapse a subform or a section of your page (or any page control for that matter), whilst moving everything below it down/up respectively.

NOTE: Example/Preview database is attached to this post (below).
or Click Here


STEP 1)
Open your form in design view.

STEP 2)
Create the button/label that you want to click for everything to happen.
My example will be called cmdShutter.

STEP 3)
Create a label/button that says "-".
(You can make this the click button if you want by referencing this instead of cmdShutter OR you can have as many expand/collapse buttons as you want by copying the same code of cmdShutter into those buttons).
This label is for deciding whether to expand or collapse - if you don't want users seeing this label, you can set its visible property to "no".
My example of this label/button will be called lblIndicator

STEP 4)
Select every item in the form (can be 1 or can be many) that you want to expand/collapse (show/hide).
Then, open the property sheet and enter/add a piece of text (for use later) into the 'Tag' property (which is under the 'Other' tab).
Our example tag will be "ExpandCollapseSection1" [without the quotes]

STEP 5)
Select all the items in the form that you want affected (moved down/up) as a result of expanding/collapsing.
Again, enter/add a piece of relevant text (for use later) into the tag property.
Our example will be "ItemBelowECSection1"

STEP 6)
If you're expanding/collapsing multiple items (controls) in your form (like a section of your page), rather than just 1 control (like a subform), you need a box frame (or any control) that is the height of everything you want to expand/collapse, such as a rectangle frame around your expand/collaspe section - think like, this is so that you can tell the code to show/hide everything within the box frame.
If you don't want a frame to show around the expand/collapse section, just set it's visible property to "no".
My example of this frame is called frameECSection1.
If, however, you're expanding/collapsing just 1 item, whenever this reference "frameECSection1" comes up, you should be referring to that 1 item.

STEP 7)
Select cmdShutter and in the 'Event' tab of the property sheet, click the "..." next to the 'On Click' property.
Select the 'Code builder' if it asks.

STEP 8)
Visual Basic should now open and you should be presented with the following code:
Code:
Private Sub [COLOR="red"]cmdShutter[/COLOR]_Click()

End Sub
Between these two lines is where all the action will take place for your button (again, in our case, called 'cmdShutter')

STEP 9)
Put the following code in between the two lines. Remember to change the names (in red) to your own control/tag names.
Code:
    Dim ctl As Control      [COLOR="blue"]'// This sets the variable 'ctl' for use below[/COLOR]

    For Each ctl In Me.Controls
    [COLOR="blue"]'// This 'For' statement repeats the following code (until its corresponding 'Next') for each
    '   control in the current form individually.[/COLOR]
    
        [COLOR="blue"]'// SECTION 1 - EXPANDING/COLLAPSING THE SECTION/SUBFORM
        '[/COLOR]
        If InStr(1, ctl.Tag, "[COLOR="Red"]ExpandCollapseSection1[/COLOR]") <> 0 Then
        [COLOR="blue"]'// This checks to see if the control has an "ExpandCollapseSection1" anywhere within
        '   its tag property.
        '// For every control that has, the following (until its corresponding 'End If') happens.[/COLOR]
        
            If Me.ActiveControl Is ctl Then
            [COLOR="blue"]'// Before we get to expanding/collapsing, any 'to be collapsed' control must be
            '   inactive, since you can't hide an active control.
            '// This checks to see if any control in the collapse section is active.
            '// If it is, the following happens.[/COLOR]
                Me.[COLOR="red"]txtAddress[/COLOR].SetFocus
                [COLOR="blue"]'// The next control after the expand/collapse section of my form is made active.
                '// In this case, it's txtAddress. Set it to anything you want.[/COLOR]
            End If
            
            If [COLOR="red"]lblIndicator[/COLOR].Caption = "-" Then
                ctl.Visible = False
            Else
                ctl.Visible = True
            End If
            [COLOR="blue"]'// This 'If' statement checks whether the indicator is -.
            '// If it is a -, then control(s) (subform/section) hides.
            '// However, if it is not a - (but a +), the control(s) is shown.[/COLOR]
            
        End If      [COLOR="blue"]'// Ends the 'If InStr' statement[/COLOR]
        
        [COLOR="blue"]'// SECTION 2 - MOVING ITEMS BELOW DOWN/UP
        '[/COLOR]
        If InStr(1, ctl.Tag, "[COLOR="red"]ItemBelowECSection1[/COLOR]") <> 0 Then
        [COLOR="blue"]'// This checks to see if the control has an "ItemBelowECSection1" anywhere within
        '   its tag property. If it does, the following happens for it.[/COLOR]
        
            If [COLOR="red"]lblIndicator.Caption[/COLOR] = "-" Then
                ctl.Top = ctl.Top - [COLOR="red"]frameECSection1[/COLOR].Height
            Else
                ctl.Top = ctl.Top + [COLOR="red"]frameECSection1[/COLOR].Height
            End If
            [COLOR="blue"]'// If the Indicator is -, the control moves up the height of the section's frame
            '   or the subform. Otherwise, it moves down the height of the frame/subform.[/COLOR]
            
        End If
        
    Next        [COLOR="blue"]'// Ends the 'For' Statement[/COLOR]
    
    [COLOR="blue"]'
    '// SECTION 3 - INDICATOR CHANGE
    '[/COLOR]
    If [COLOR="red"]lblIndicator[/COLOR].Caption = "-" Then
        [COLOR="red"]lblIndicator[/COLOR].Caption = "+"
    Else
        [COLOR="red"]lblIndicator[/COLOR].Caption = "-"
    End If
    [COLOR="blue"]'// If the indicator is a -, it's changed to a +; otherwise it's changed to a -.[/COLOR]
    
    Set ctl = Nothing      '//  Resets the variable 'ctl'
[COLOR="Blue"]'
'
'// You can simply delete these ' comments if you want after you're done and it's working.[/COLOR]

NOTE:
The above keeps the expand/collapse subform/section open when the form is opened.
If you'd like it collapsed as default, you can simply copy the above code into the load event of the form.

(To do this, select 'Form' in the drop-down box on the top of the property sheet.
Go to the 'Event' tab and open the code builder for the 'On Load' property.)

Copying the above code into the form's 'on load' event makes the section collapse as the form loads.

Below is a simplified version, just for the 'on load' event.
Code:
    Dim ctl As Control
    
    For Each ctl In Me.Controls
    
        If InStr(1, ctl.Tag, "[COLOR="red"]ExpandCollapseSection1[/COLOR]") Then
            ctl.Visible = False
        End If

        If InStr(1, ctl.Tag, "[COLOR="red"]ItemBelowECSection1[/COLOR]") Then
                ctl.Top = ctl.Top - [COLOR="red"]frameECSection1[/COLOR].Height
        End If
        
    Next
    
    [COLOR="red"]lblIndicator[/COLOR].Caption = "+"
    
    Set ctl = Nothing
 

Attachments

  • Expand-Collapse System.accdb
    776 KB · Views: 2,145
Last edited:

eq2home

New member
Local time
Today, 14:31
Joined
May 25, 2016
Messages
3
You are a Guru. Thanks very much for this very helpful and well documented explanation and example.:)

FIVE StarS!
 
Last edited:

eq2home

New member
Local time
Today, 14:31
Joined
May 25, 2016
Messages
3
A quick question...I have a Tab Control with a subform on it. The subform container moves but not the individual controls on the subform. Do you happen to have a trick to make the form controls follow the form container move? Thanks Jim
 

araffa040872

New member
Local time
Today, 11:31
Joined
Mar 23, 2019
Messages
1
Good day,

I know this is an old topic but I wanted to make a contribution especially when I found something that works.

These 2 lines of code will basically expand or collapse all. If you have nested subforms, you simply have to add 1 line of code per subform. Just make sure the syntax is correct.

In my example, I made 2 buttons (cmdCollapse and cmdExpand)



Private Sub cmdCollapse_Click()

Me.1stSubform.Form.SubdatasheetExpanded = False

Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = False

End Sub

Private Sub cmdExpand_Click()

Me.1stSubform.Form.SubdatasheetExpanded = True

Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = True

End Sub
 

isladogs

CID VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
14,777
Hi araffa

Welcome to the forum and thank you for your contribution
In fact, you could combine your code into one button:

Code:
Private Sub cmdCollapseExpand_Click()

If cmdCollapseExpand.Caption = "Expand" Then
   Me.1stSubform.Form.SubdatasheetExpanded = True 
   Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = True
    cmdCollapseExpand.Caption = "Collapse"
Else
    Me.1stSubform.Form.SubdatasheetExpanded =False
    Me.1stSubform!2ndSubform.Form.SubdatasheetExpanded = False
    cmdCollapseExpand.Caption = "Expand"
End If

End Sub

Your code will of course only work with subdatasheets.

The attached app was done in answer to two separate forum questions about altering subform size using code
It shows how you can collapse expand a subform completel and move other controls up/down accordingly
It also has a separate form showing how the width of the subform can be reduced/increased

BTW:
You may be aware that subdatasheets have to be loaded whether or not they are expanded. This causes a performance hit on the application.
For that reason, subdatasheets are normally best avoided
 

Attachments

  • SubformShrink&Grow_v3A.zip
    65.5 KB · Views: 186

Users who are viewing this thread

Top Bottom