Expand/shrink width of text field on a report

SteveL13
SteveL13 used Ask the Experts™
on
Is there a way to expand (or shrink) the WIDTH of a text field on a report based on the width of the total characters in the field?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Open 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).

Open 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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
esskayb2d,
<change the CAN GROW property to true>

That is what I suggested in:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27596187.html#a37624480

JeffCoachman
MIS Liason
Most Valuable Expert 2012
Commented:
SteveL13,

To be clear, IMHO what you are trying to do will produce an inconsistent, and perhaps confusing, output.
(It will also wreak havoc if you ever need to Export this report to Excel...)

As always, you can do anything you want.
How much trouble you are willing to go through is another story...

But this "Dynamic" resizing  (again...) is, ...and never will be, ...perfect, ...so why bother?
...In addition to all the work/code you have to go through to get it to work. (and it's is still not perfect)
....In addition to the fact that this won't work if the report is opened in Report View.

This is why in the over 16 year history of MS Access, this feature has never been included...

If you really need something like this, then concatenate the fields/controls in code, then spit this out to a textbox...
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.Text4 = Me.Label0.Caption & " " & Me.Text2 & " " & Me.Label1.Caption
End Sub

See the attached DB, "Copy Of Report1"


JeffCoachman
TestFieldWidth.accdb

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial