Dynamically resize a report Detail area

ms-access vba ms-access-2003
FrustratedWithFormsDesigner picture FrustratedWithFormsDesigner · Jul 29, 2011 · Viewed 17k times · Source

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.

Answer

Igor Turman picture Igor Turman · Jul 29, 2011

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

enter image description here enter image description here

Related questions

Now() function with time trim

So the function =Now()....is there a way I can use this and only get the date, not the time? or is there just a function for this idea?

Read More
VBA Code to Close an Access Database Without Leaving a Shell of the application open

I'm currently using Application.Quit which leaves a shell of the MS Access Application open. After processing the calling function against the target application, I am left with every instance of the application remaining open still; even though the specific …

Read More
how can i populate textbox through VBA in MS Access

I have a table RR_info which hold following fields RR_ID, HR_ID, No_of_Beds, Room_Category. Now i want that through VBA code with Form_load event I should populate textboxes for all these table fields. For …

Read More