Expand/shrink width of text field on a report

I found some information for you here:
http://www.dbforums.com/microsoft-access/1213941-resizing-textbox-report.html

Personally, I have never found any real definitive solution to retrieving the width of a String in Twips dependant upon the font size and the Control it may reside in. But I have come up with something that is relatively close and may do the job.


It requires the use of a few Windows API functions, in particular the apiGetFocus function so as to acquire the Handle (hWnd) of the MS-Access Control which contains the text String, the GetWindowDC function so as to acquire the Device Context (DC) of the Control window, and finally the GetTextExtentPoint32 function so as to determine the length of the String located within the Control in Points.


All these API functions are put to work within another function I call GetStringWidthInTwips and delivers the Twip String Width which can be used to either make wider or narrower the Control which contains the String value.


To begin, Place the following code into a Database Code Module:


Code:

Option Explicit
' Type Declaration for the GetTextExtentPoint32 function.
Private Type POINTAPI
   X As Long
   Y As Long
End Type
 
' The API function to retreive the width and Height of 
' a String (in Points).
Private Declare Function GetTextExtentPoint32 Lib "gdi32" Alias _
                                 "GetTextExtentPoint32A" (ByVal hdc As Long, _
                                 ByVal lpsz As String, ByVal cbString As Long, _
                                 lpSize As POINTAPI) As Long
 
' The API function used to acguire the Device Context
' (DC) of a Window (or Control).
Private Declare Function GetWindowDC Lib "user32" (ByVal hwnd As Long) As Long
 
' The API function used to acquire the Handle (hWnd)
' of a MS-Access Control. MS-Access Controls don't
' have a Handle because they are drawn to screen as
' when required. Once it's drawn, we can then get a
' Handle.
Private Declare Function apiGetFocus Lib "user32" _
                                 Alias "GetFocus" () As Long
 
 
Public Function GetStringWidthInTwips(Ctrl As Control) As Long
   Dim TextSize As POINTAPI
   Dim nDC As Long
   Dim mWnd As Long
   Dim Strg As String
 
   ' Fill the Value of the Control into the Strg String variable.
   Strg = CStr(Ctrl.Text)
   'Get the Handle (hWnd) of the Control
   mWnd = GethWnd(Ctrl)
   'Get the Control's device context
   nDC = GetWindowDC(mWnd)
   'Get the width of our Text String
   GetTextExtentPoint32 nDC, Strg, Len(Strg), TextSize
   ' Convert the the determined text length (in points) to Twips.
   ' You may need to play with the value of 1.358 to suit your
   ' needs.
   GetStringWidthInTwips = (TextSize.X * (Ctrl.FontSize * 1.36))
End Function

 
Public Function GetStringHeightInTwips(Ctrl As Control) As Long
   Dim TextSize As POINTAPI
   Dim nDC As Long
   Dim mWnd As Long
   Dim Strg As String
   ' Fill the Value of the Control into the Strg String variable.
   Strg = CStr(Ctrl.Text)
   'Get the Handle (hWnd) of the Control
   mWnd = GethWnd(Ctrl)
   'Get the Control's Device Context
   nDC = GetWindowDC(mWnd)
   'Get the Height of our Text String
   GetTextExtentPoint32 nDC, Strg, Len(Strg), TextSize
   ' Convert the the determined text Height (in points) to Twips.
   ' You may need to play with the value of 1.3 to suit your
   ' needs.
   GetStringHeightInTwips = (TextSize.Y * (Ctrl.FontSize * 1.3))
End Function
 
Private Function GethWnd(ctl As Control) As Long
    ' Acquire the Handle (hWnd) of the passed
    ' MS -Access Control.
    On Error Resume Next
    ' Make sure the passed Control has Focus.
    ctl.SetFocus
    If Err Then  ' if focus can not be achieved then return 0
        GethWnd = 0
    Else         ' otherwise, get the Handle
        GethWnd = apiGetFocus
    End If
    On Error GoTo 0
End Function

Select allOpen in new window

Notice that I've also tossed the function named GetStringHeightInTwips into the mix. Do with it as you will.

Now...to use all this mess simple do this:

Code:

Me.myControlName.Width = GetStringWidthInTwips(Me.myControlName)
If your going to use this in a Report, then place this line of code into the OnFormat event of the Report Section which i contains the Control (usually the Details Section).

Select allOpen in new window

If someone has found a better way to do this then please....SHARE IT.

Hope this helps.

control.width = Len(control) * 100
Play with numbers till it works?

566 twips = 10mm

You can do this but it is never perfect.

For example this is 25 characters:
iiiiiiiiiiiiiiiiiiiiiiiii

...but so is this:
WWWWWWWWWWWWWWWWWWWWWWWWW

...so how wide should the textbox be?
;-)

You can use a "0" as the "Average Character" (This is what MS uses when setting column widths), but again, this will never be perfect.

If this is a report, then it might be easier to simply set the "Can Grow" property of the textbox to "Yes", ...and simply let the text wrap.
Then the text will *always* fit
Then no code or experimentation would be needed...

To be clear, I am not trying to knock the other Expert's suggestions, it's that just for me, I like to use the built in functionality first, before reaching for a solution that needs all that code or any experimentation...

    "Keep It Simple"

But again, to be fair, their posts do address your question as posted...

;-)

JeffCoachman

To all:

This isn't working but I may be doing something wrong.  I'm sending a sample database.  If you run the report you'll see two records.  What I need to have happen is have the 2nd field adjust itself to the number of characters in the field.  Then I need the last label field, (Label1),  to move over next to the adjusted text field.  Make sense?
TestFieldWidth.accdb

change the CAN GROW property to true

it will grow downwards but it will do what you need done

see how it works with the attached database
TestFieldWidth.accdb

esskayb2d,
<change the CAN GROW property to true>

That is what I suggested in:
https://www.experts-exchange.com/questions/27596187/Expand-shrink-width-of-text-field-on-a-report.html?anchorAnswerId=37624480#a37624480

JeffCoachman

This is working out nicely...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Text4 = Me.Label0.Caption & " " & Me.Text2 & " " & Me.Label1.Caption
End Sub

But now I have one more question...  What if I need Label0 and Label1 to be italic but Text 2 to not be italic?

I tried
Me.Text4 = Me.Label0.Caption.fontitalic & " " & Me.Text2 & " " & Me.Label1.Caption.fontitalic

But that doesn't work.

<But now I have one more question.>
Remember, only one question per post...
;-)

You can create a new question for this new issue, as it is a bit tricky to accomplish...

JeffCoachman

...if you still need the formatting, I think I may have something for you...
I'll wait to see if you post a new Q...