resizing Access report ctrl dynamically with vba

[Carlit007]

Hi I Have a Access report that is used to populate a form that has 16 rows of data each roughly line is .32 in height

the report is based on a query of serial numbers and grouped by equipment type
If the query returns enough serial number to fill out 2 lines of data there are no issues with the formatting & space

however if there's enough serials to go over 2 lines of data then everything is thrown out of sync

note: I have the grouping & bound textbox set to can grow
however when anything goes past 2 rows of data per item then the formatting gets all out of order
when the query moves to the next equipment category

is there a way with vba to set a condition that if more than 2 lines of data to reset the size to stay within the .32 margin


I came across the following just not sure how to implement on my report

Private Sub Ctl2062equiparea_Paint()

Dim dblTextBoxHeight As Long
Dim intTextBoxHeightMultiplier As Long

dblTextBoxHeight = Me.SerialNumberctrl.Height
intTextBoxHeightMultiplier = 1

'loop control starts

       'code here

        'intTextBoxHeightMultiplier = intTextBoxHeightMultiplier + 1

'loop control ends

'Me.SerialNumberctrl.Height = dblTextBoxHeight * intTextBoxHeightMultiplier

'Me.SerialNumberctrl.Height = 0.32



End Sub

for reference this is what the report look like currently

 

[Micron]

Your main problem is that each one of those serials should be a record. You could try using the report Format event, but it only fires in Print Preview IIRC. I think you could resize the control height as long as your section is allowed to grow. However, you'd need some sort of test to decide when - perhaps if the character length of the value in that field of the record exceeds some value that you decide on.

 

[Carlit007]

Your main problem is that each one of those serials should be a record. You could try using the report Format event, but it only fires in Print Preview IIRC. I think you could resize the control height as long as your section is allowed to grow. However, you'd need some sort of test to decide when - perhaps if the character length of the value in that field of the record exceeds some value that you decide on.

Hi @Micron I Forgot to mention I am using a function that Concatenates the Related field that's how I got the Serials to be separated by comas this allows me to group all the liked items together otherwise every single serial would create a new line as opposed to being grouped by equipment type