Link to home
Start Free TrialLog in
Avatar of Rick Danger
Rick DangerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Set row heights on Access report using VBA

I have an Access report in which I want the rows to have variable heights. For example, in one row I might display an image,  and in the next row a text box. I would like to set the row height to a fairly big number for the image, but for the text box, quite small. Is it possible to dynamically set the height of each row, depending on a certain value?
I have seen that I can set the row height in VBA for the Format event but it doesn't always seem to work. 
Avatar of ste5an
ste5an
Flag of Germany image

Did you use auto size and auto shrink? I normally works like a charm.
Here is way to adjust Weight of the range. Please also use RowHeight property to do the adjustment for .Height of the cell range.
        With Range("A1:D50").Borders(xlEdgeLeft)
            .Weight = xlThin
        End With



Open in new window

Avatar of Daniel Pineault
Daniel Pineault

@Peter the question is regarding Access, not Excel.
Avatar of Rick Danger

ASKER

ste5an
Do you mean "Auto Height" and "Can Shrink" on the form detail section? If so, yes I am using those but I'm not seeing the row change height. Is there something I should to the actual image being displayed?
leftAmount = 4000
topAmount = 100
normalHeight = 566
ImageHeight = 2500
Select Case Me.FK_SIGNOFF_FORM_ELEMENT_TYPE_ID
Case 1 ' Y/N
    Me.Detail.Height = normalHeight
    Me.answer_chk.Visible = True
    Me.answer_txt.Visible = False
    Me.answer_image.Visible = False
    Me.answer_geo.Visible = False
    Me.answer_long.Visible = False
    Me.answer_chk.Left = leftAmount
    Me.answer_image.Height = 0
Case 2 ' Text / Number
    Me.Detail.Height = normalHeight
    Me.answer_chk.Visible = False
    Me.answer_txt.Visible = True
    Me.answer_image.Visible = False
    Me.answer_geo.Visible = False
    Me.answer_long.Visible = False
    Me.answer_txt.Left = leftAmount
    Me.answer_image.Height = 0
Case 3 ' Photo
    Me.Detail.Height = ImageHeight + 200
    Me.answer_chk.Visible = False
    Me.answer_txt.Visible = False
    Me.answer_image.Visible = True
    Me.answer_geo.Visible = False
    Me.answer_long.Visible = False
    Me.answer_image.Left = leftAmount
    Me.answer_image.Top = topAmount
    Me.answer_image.Height = ImageHeight
Case 4 ' GeoLocation
    Me.Detail.Height = normalHeight
    Me.answer_chk.Visible = False
    Me.answer_txt.Visible = False
    Me.answer_image.Visible = False
    Me.answer_geo.Visible = True
    Me.answer_long.Visible = False
    Me.answer_geo.Left = leftAmount
    Me.answer_image.Height = 0
Case 5 ' Long Text
    Me.Detail.Height = normalHeight
    Me.answer_chk.Visible = False
    Me.answer_txt.Visible = False
    Me.answer_image.Visible = False
    Me.answer_geo.Visible = False
    Me.answer_long.Visible = True
    Me.answer_long.Left = leftAmount
    Me.answer_image.Height = 0
End Select
End Sub


Open in new window

This is a snippet of the code I am using. It checks whether the field to be displayed is text, long text, boolean or an image. If it's an image, it makes the row taller and then the image size taller too. If it's not it should shrink the image back down to normal size but although it seems to be partially working the row after the image always seems too tall
I may have answered my own question. As long as I leave
Me.Detail.Height = normalHeight
as the last thing I do, it seems to work. Presumably, if I set the detail.height too early it could not shrink because the field height was still too tall, so the row height could only go as low as the field height. Changing the order allowed the row height to be shrunk.
ASKER CERTIFIED SOLUTION
Avatar of Rick Danger
Rick Danger
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial