Change form header height and subform height

[oxicottin]

Hello, I wanted to have a "+" and "-" on my form (frmVWI) and when pressed it expands my subform (frmSubformVWI)and makes my forms header smaller because the subforms in its detail section. I have some code that makes the subform visable ect. but not what im looking for. Any easy ideas or code samples I can use to acomplish this? Thanks!

Private Sub cmdVWIShutter_Click()

If cmdVWIShutter.Caption = "-" Then
        Form.Section(acHeader).Height = ?
        frmVWISubform.Height = ?
        cmdVWIShutter.Caption = "+"
    Else
         Form.Section(acHeader).Height = ?
         frmVWISubform.Height = ?
         cmdVWIShutter.Caption = "-"
    End If
End Sub

Thanks,
SoggyCashew.....

 

[dhookom]

Did you try the code keeping in mind the height is measured in twips? You can't shrink a section any smaller than what would be allowed by its controls.

You might be able to set a section to invisible. I'm not sure any of this will work so please try it and report back.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane, thanks for the reply... I don't want to make my header visible = false because I have my button in the header. What code would I use to collapse or set the height of the header also if I have controls in the header would it just get covered up by the detail section being larger?

Thanks,
SoggyCashew.....

 

[dhookom]

As I stated, the height of the form section can't be less than the bottom of the bottom control.

Private Sub cmdVWIShutter_Click()
    If cmdVWIShutter.Caption = "-" Then
        Me.Section(acHeader).Height = 1000
        Me.frmVWISubform.Height = 2000
        Me.cmdVWIShutter.Caption = "+"
    Else
         Me.Section(acHeader).Height = 2000
         Me.frmVWISubform.Height = 1000
         Me.cmdVWIShutter.Caption = "-"
    End If

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane, I tried the VBA and thats all its doing is changing the pluss and minus and the height of the subform I have in the detail section. It is not changing the height of the header section. thoughts?

Thanks,
SoggyCashew.....

 

[dhookom]

Feel free to tell me about your code, your header height, the controls in your header, and all other significant information that you can see and I can't.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

dhookem, I have 6 text boxes, 7 combo boxes and and two buttons which are at the top of the header. I would say my form header in twips is 4000. What I wanted to do is shorten the form header to like 3000 twips so my detail section would be larger so I can raise the height of a subform "frmVWISubform" to fill the height. Is this possible? I wanted to leave a little space in the forms header so it still showed the cmdVWIShutter button but cover the rest of the controls in that area.

Thanks,
Chad

Thanks,
SoggyCashew.....

 

[oxicottin]

Duane and dhookem, I found out whats causing it not to work. I guss if you have any control wether it be visable or not in the form header it will only move in the "free space" not over controls. How do I get around this? I eleminated all code in the form header except for my button at the top and got the sizes needed now I just have to figure out a work around for the rest....

Private Sub cmdVWIShutter_Click()
If cmdVWIShutter.Caption = "-" Then
        Me.Section(acHeader).Height = 3815 ' good
        Me.frmVWISubform.Height = 5465 'good
        Me.cmdVWIShutter.Caption = "+"
    Else
         Me.Section(acHeader).Height = 500 'good
         Me.frmVWISubform.Height = 8775 'good
         Me.cmdVWIShutter.Caption = "-"
    End If

End Sub

Thanks,
SoggyCashew.....

 

[dhookom]

I found out whats causing it not to work. I guss if you have any control wether it be visable or not in the form header

I mentioned twice

You can't shrink a section any smaller than what would be allowed by its controls.

You would need to move and hide the controls in the header section.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

So i would have to shrink and hide everything at the top of the header.... That would be time consuming to figure out the exact twips. Is there another way?

Thanks,
SoggyCashew.....

 

[dhookom]

I doubt there is any other method. I would store the original dimension and position information in the tag property so you could recall this information to have it display again.

It probably wouldn't take all that much code to loop through all the controls to hide and then display.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

dhookom could you give an example of what you mean? Im not a programmer just a tech user that messed up when signing up and chose the wrong title... Thanks!

Thanks,
SoggyCashew.....

 

[dhookom]

Here is the code that should work to collapse a section. It saves orginal height and top properties in the tag properties and hides the controls.

I don't have time now to add the code that would read the tag properties and expand the section.

Private Sub cmdColapseExpand_Click()
    Dim intCollapseTo As Integer
    intCollapseTo = Me.cmdColapseExpand.Top + Me.cmdColapseExpand.Height + 10
    If Me.cmdColapseExpand.Caption = "-" Then   'need to hide controls
        Me.Section("FormHeader").Tag = Me.Section("FormHeader").Height
        HideControls
        Me.Section("FormHeader").Height = intCollapseTo
      Else
    End If
End Sub
Public Sub HideControls()
    Dim ctl As Control
    For Each ctl In Me.Section("FormHeader").Controls
        If ctl.Name <> "cmdColapseExpand" Then
            ctl.Tag = "T=" & ctl.TopPadding & " H=" & ctl.Height
            ctl.Top = 1
            ctl.Height = 1
            ctl.Visible = False
        End If
    Next
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Actually this code seems to work for me

Private Sub cmdColapseExpand_Click()
    Dim intCollapseTo As Integer
    intCollapseTo = Me.cmdColapseExpand.Top + Me.cmdColapseExpand.Height + 10
    If Me.cmdColapseExpand.Caption = "-" Then   'need to hide controls
        Me.Section("FormHeader").Tag = Me.Section("FormHeader").Height
        HideControls
        Me.Section("FormHeader").Height = intCollapseTo
        Me.cmdColapseExpand.Caption = "+"
      Else
        Me.Section("FormHeader").Height = Me.Section("FormHeader").Tag
        Me.cmdColapseExpand.Caption = "-"
        ExpandControls
    End If
End Sub
Public Sub HideControls()
    Dim ctl As Control
    For Each ctl In Me.Section("FormHeader").Controls
        If ctl.Name <> "cmdColapseExpand" Then
            ctl.Tag = "T=" & ctl.Top & " H=" & ctl.Height
            ctl.Top = 1
            ctl.Height = 1
            ctl.Visible = False
        End If
    Next
End Sub
Public Sub ExpandControls()
    Dim ctl As Control
    
    For Each ctl In Me.Section("FormHeader").Controls
        If ctl.Name <> "cmdColapseExpand" Then
            ctl.Top = Val(Mid(ctl.Tag, 3))
            ctl.Height = Val(Mid(ctl.Tag, InStr(ctl.Tag, "H=") + 2))
            ctl.Visible = True
        End If
    Next
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Dhookom, I will try this in a few hours I dont have access 2007 on my home PC.... I will report back though.... Thanks!

Thanks,
SoggyCashew.....

 

[oxicottin]

Ok Duane im sure im doing this wrong and Im going to explain what im doing and why it isnt working for me. It looks like I have to enter (cmdColapseExpand) in each control tag in the forms header I cant get it to do anything? What am I doing wrong?

Thanks,
SoggyCashew.....

 

[oxicottin]

Duane I found out my mistake..... I had a "+" in the buttons caption instead of a "-" it wors great thank you!

Thanks,
SoggyCashew.....

 

[oxicottin]

Duane I have another question. What if my button was located not at the top of the forms header but at the bottom and I wanted it to raise to the top and back down when colapsed also what if I had two controls at the top of the forms header that I wanted to keep visable what would I do for that? Thanks!

Thanks,
SoggyCashew.....

 

[dhookom]

You should be able to modify the code add and Else clause to

        If ctl.Name <> "cmdColapseExpand" Then

The extra lines would set the top property of the command button. The code would also need to hard-code the intCollapseTo value.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane, Thanks for the help but this Colapse/Expand is way outa my leauge. I have been staring at your code since you posted it and messed with it to the best of my knowledge and I do understand some but most of it is another lauange. Im going to have to call it quits on this addition to my DB because im having brain fart.....

Thanks,
Chad

Thanks,
SoggyCashew.....