Dynamically resize a report Detail area

Question

Is it possible to resize the Detail area of a report dynamically, in MS Access?

I have a report and the Detail region has 2 rows, I'd like one of them to be "optional" - when there is no data it should not display and the Detail region should only be as tall as the top row of data.

I have code like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim reduceHeight As Integer
    reduceHeight = Me.Label83.Height


    If IsNull(Me.data_1) Then
        Me.data_1.Visible = False
        Me.Label83.Visible = False
    Else
        Me.data_1.Visible = True
        Me.Label83.Visible = True
    End If

    If IsNull(Me.data_1) 
        Detail.Height = Detail.Height - reduceHeight
    End If

End Sub

And it works as far as makign the label and text box conditionally visible, but I can't get the Detail region to shrink when the bottom line is hidden. I did set the CanShrink property for the Detail to True, but it doesn't shrink.

score 4 · Answer 1 · edited Mar 20 '19 at 18:46

Hi: I understand that you have labels. Since labels cannot shrink, you'll need to write some code.

Dynamically playing with reports is a tricky task. When you change a section's height or width all the controls must keep inside the new area, otherwise you'll have problems. When you move a control, it must keep inside the section's area, otherwise you'll have problems. Also, you should disable Autoshrink for the section.

Now, this is an example. You should modify it to meet your requirements. Here goes the code for the report:

Option Compare Database
Option Explicit

Private twipsPerLine As Integer     ' The height of a line in your report
Private detailHeight As Integer     ' The height of your detail section
Private vPos As Integer             ' The vertical position of the control
                                'following the one you want to hide

Private Sub Report_Open(Cancel As Integer)
    ' Set the values
    vPos = data_2.Top
    twipsPerLine = data_2.Top - data_1.Top
    detailHeight = Me.Detail.Height
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If (IsNull(Me.data_1.Value)) Then
        ' First, you hide the controls
        Me.data_1.Visible = False
        Me.data_1_label.Visible = False
        ' Then, you set the position of the rest of the controls (up)
        data_2_label.Move data_2_label.Left, vPos - twipsPerLine
        data_2.Move data_2.Left, vPos - twipsPerLine
        ' Finally, you shrink the detail section height
        Me.Detail.Height = detailHeight - twipsPerLine
    Else
        ' First, you show the controls
        Me.data_1.Visible = True
        Me.data_1_label.Visible = True
        ' Then, you reset the section height
        Me.Detail.Height = detailHeight
        ' Finally, you reset the position of the rest of the controls
        data_2_label.Move data_2_label.Left, vPos
        data_2.Move data_2.Left, vPos
    End If

End Sub

This approximation gives you complete control over your report and works even if you have labels, images or whatever on it.

enter image description here

Another option is to change the labels to textboxes, set visible to false on the textbox properties, and set their canShrink properties to true. — thecoolmacdude, Apr 11 '16 at 19:59

score 4 · Accepted Answer · answered Jul 29 '11 at 18:57

4

Set CanShrink for your controls too (e.g. textboxes):

enter image description here

answered Jul 29 '11 at 18:57

Igor Turman

2,165
1
22
25

I thought I had already done that but it seems I had missed a couple. Looks like it works now! – FrustratedWithFormsDesigner Jul 29 '11 at 19:03
This works unless you are using labels (or have a mix of label and textbox fields). If you have labels, you can either: 1) change the labels to textboxes, set visible to false on the textbox properties, and set their canShrink properties to true OR 2) See Clon's answer. – thecoolmacdude Apr 11 '16 at 20:03

Dynamically resize a report Detail area

2 Answers2