Avatar of Rick Danger
Rick Danger
Flag 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. 
* msaccess reportsVBA* Access

Avatar of undefined
Last Comment
Rick Danger

8/22/2022 - Mon
ste5an

Did you use auto size and auto shrink? I normally works like a charm.
Peter Chan

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

Daniel Pineault

@Peter the question is regarding Access, not Excel.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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?
Rick Danger

ASKER
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
Rick Danger

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rick Danger

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question