Unanswered: Adjust report section height for any font
How do you get the twips height of a text box on a report for a particular font and font size?
I have a report that allows the User to choose the font and size for the various controls in the header/footer sections. My text boxes are placed on the report with a minimum height (about 1 mm), with CanGrow=Yes, but this does not expand the section height, and an error occurs.
So I need to resize the height of the header/footer sections dependent on the name of the font chosen by the user.
There are 1440 Twips to the Inch (you can do the coversion and round that off for the metric unit). So...to get the number of Twips in the height of a TextBox (if in inch units):
Dim lngTwips As Long
lngTwips = Me.myTextBox.Height * 1440
If the TextBox you are sizing is located within either a Report Header/Footer, the Details Section and those sections have their Can Grow properties set to Yes then you shouldn't have any problems. However...if you are trying to size the TextBox in a reports Page Header/Footer then the font will size up or down but the TextBox will not size up (grow) any further that the set Page Header or Footer. There is no Can Grow Property in these two sections.
You can set this section progmaticly but first you will need to set the Can Grow and Can Shrink properties for your TextBox to NO. There's No sense having your code and MS Access fighting for control as to who is gonna size what and when. You might think that the TextBox control will resize on its own when the Page Header/Footer is opened up a bit if the Can Grow Property is set but it won't if your code is telling it to do something else by way of Font Size changes etc.
To expand and retract Headers & Footers in general, should be done in either the OnFormat or OnPrint events. The OnOpen event wont work with the code below:
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
'Note: A Access Report default ScaleMode is Twips.
'In this example the desired TextBox Font Size is passed along _
in the Reports' OpenArgs property.
Dim intTH As Integer
'Set the TextBox Font Size to what is contained _
in the OpenArgs property.
'Does the OpenArgs property contain anything...
If Me.OpenArgs = Null Then
'Nope...It's Null so set the TextBox _
Font Size to 9
Me.FirstName.FontSize = 9
'Yup...It does contain something so lets _
set the TextBox to the size supplied in _
the OpenArgs Property.
Me.FirstName.FontSize = Me.OpenArgs
'Set the Reports' Font Sise to the Same _
as that of the TextBox
Me.FontSize = Me.FirstName.FontSize
'Now get the Actual Text Height (in twips) _
based on the Supplied character. The intTH _
variable will hold this value. The TextHeight _
method also adds that small space above and _
below a Character.
intTH = Me.TextHeight("Z")
'Set The Report section to match the height _
of our desired Font Size. In this case, the _
Page Footer will be sized. See MS-Access Help _
with regards to other Constants for the various _
Headers and Footers (highlight the word: "Section" _
and hit the F1 key).
Me.Section(acPageFooter).Height = intTH + 200
'Now set the TextBox height to match exactly _
that of the Page Footer.
Me.FirstName.Height = intTH
Self Taught In ALL Environments.....And It Shows!