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.
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
Last Comment
Rick Danger
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
@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
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?
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?
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
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.
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
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
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