Adjusting the height of a report entry in VBA

[Petrol]

I have an Access report whose records in the Detail section usually require two lines, but occassionally a message needs to be displayed for a particular record. This message is in the form of a label in the third line, which is made either visible or invisible, depending on a flag within the record. To avoid wasting space, I want to adjust the height of the entry to accommodate the third line only when required. The flag is checked, the label.Visible property set, and the section height adjusted in an On Format event procedure.

At least, that's the theory. In practice, I am having difficulty getting the height adjustment to work. According to Microsoft Learn, the Format eevnt is triggered for each record, and the syntax to adjust the height is Me.Detail.Heigh t, but when I try this each record occupies three lines, the third being generally blank. I have also tried Me.section(acDe tail).Height, whic I saw suggested in an AI answer somewhere, to no avail.

I note that if I type Me.detail.heigh t, VBA corrects it to Me.Detail.heigh t but doesn't capitalise the last word, suggesting that it is not being recognised as valid.
The code I am using in the On Format eevnt proceduer is

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'
'    If the pilgrim withdrew during the event, add an extra line to say so.
'
Dim tpcm As Variant
tpcm = 1440 / 2.54           ' twips per centimetre
On Error GoTo ErrorProc

If [Response] = "W" Then
    lblWithdrawalNote.Caption = "NB: " & txtFirst_name & " attended initially, but withdrew during the event."
    lblWithdrawalNote.Visible = True
    Me.Detail.height = 1.8 * tpcm       ' Set section height to 1.8cm for this record only
Else
    lblWithdrawalNote.Visible = False
    Me.Detail.height = 1.2 * tpcm       ' Set section height back to 1.2cm for other records
End If

ByeBye:
Exit Sub

ErrorProc:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "Error closing F52"
    Resume ByeBye
End Sub

[NeoPa]

Ah. Well. As usual, the devil is in the details ;-)
The .Height property can never be set to a value that doesn't allow all existing Controls to be covered. Before you ask - This is regardless of any .Visible settings of course ;-)

Thus, to get around this when not showing lblWithdrawalNo te, you need to position it such that it won't restrict your setting of .Height (or .Width). In some scenarios (I suspect not this one.), you may also need to reduce its size if the original object on its own at the top-left of the Section would still cover too much space. In this case though, I suspect simply setting the .Top & .Left both to zero (0) would allow for changing the height. Clearly you would have to reverse this in your code when you DO want it to be shown/included.

[twinnyfo]

Petrol,

This label that you want to show or not show must be a text box. Set its height to 0” and then set the CanGrow to Yes. This should adjust its height accordingly without messing with any of the OnFormat events of that section of the report.

Hope that hepps!!!!!

[NeoPa]

Ah. Well. As usual, the devil is in the details ;-)
The .Height property can never be set to a value that doesn't allow all existing Controls to be covered. Before you ask - This is regardless of any .Visible settings of course ;-)

Thus, to get around this when not showing lblWithdrawalNo te, you need to position it such that it won't restrict your setting of .Height (or .Width). In some scenarios (I suspect not this one.), you may also need to reduce its size if the original object on its own at the top-left of the Section would still cover too much space. In this case though, I suspect simply setting the .Top & .Left both to zero (0) would allow for changing the height. Clearly you would have to reverse this in your code when you DO want it to be shown/included.

[Petrol]

Wow, that's fascinating! I would never have thought of doing that. I'll try it tomorrow, but I presume that will solve the problem. Thank you.
(Of course, it makes the ability to change the detail.Height setting fairly useless! If all you can do is add or remove empty space, I think you would probaly usually do it in the report design, not dynamically)

[NeoPa]

I can't understand how that could be your understanding of the situation Peter, but I certainly don't see it that way.

As always, if you understand the restrictions, then you can do more & better with what you have available. Many can, & do, use this with great flexibility. The fact that no-one - even those who are unaware of what complications may be waiting for the unwary - can get themselves into trouble (because of this restriction) with settings that could otherwise cause them problems, is something I believe is sensible & robust.

Consider how to handle a situation where the Section had been reduced after a Control had been made invisible and then - later - the Control were made visible. The extra hoops Access would be constrained to handle would make it exponentially more complicated - with exponentially more problems to discover & maintenance required to the product. Making the illogical impossible seems to me to be a great idea.