Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Exclamation Unanswered: Resizing a Textbox on a Report

    Does anyone know how and if you can resize a textbox on a report dynamically so that it is only the size necessary to fit the information for the current record in it--for each record. I can visualize that there might be a way using the on print or on format events to set the size of the textbox and to set its Left property to keep the right side of the box in the same place. The problem is that I have no idea how to set the text box to be the horizontal only size of the text. I guess what I'm searching for is a textbox that will grow and shrink horizontally rather than vertically. I'm trying to do something fancy with a label only one space from the textbox which is based on a field in a table--and the client wants the stuff right justified. Left justified is a piece of cake but right justified has me stymied. Btw, the font size of the label and the textbox are different for dramatic effect and it would be nice to keep it so--I can easily accomplish what I am asking with equal font sizes, and I am trying to avoid rich text boxes in any way. I suppose that if I could figure out how to compute width of text from the length of the text that I could just move the label around to accomodate it but I confess that I don't have a clue as how to do so. Any help would be greatly appreciated. Thank you in advance for your time and your replies.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    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
    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).

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

    Hope this helps.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Nov 2002
    Posts
    154

    Talking Thanks!

    Thanks loads, CyberLynx, that worked great, though I had to change the Type from private to public for some unknown reason. Below is the code in the On Format event that I put in to make sure the right side of the text box is aligned with other elements on the report and to also Align Right the label with the textbox. Basically the right side of the box is very close to being at five inches.

    Me.OrderJobNo.Width = GetStringWidthInTwips(Me.OrderJobNo)
    sglLeft = 7940 - Me.OrderJobNo.Width
    Me.OrderJobNo.Left = sglLeft
    Me.lblCompany.Left = sglLeft - Me.lblCompany.Width

    Thanks again for your help it works perfectly.

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    I actually have a post asking about this too. Anyway I used CyberLynx's code, but wasn't quite getting what I needed.

    I figured it was the '1.3' hardcoded value. So I found how to convert pixels to twips using the current screen res and another API function, GetDeviceCaps. But it still didn't quite work.

    Turns out the font used by the DC was not the same as the control so what I had to do was create a font using the API CreateFont and select it into the DC. First I used the control fontSize and fontName to create the font for the DC, but the fontSize was too small when used with the DC. So I hard coded it. I believe the control fontSize was 8 and I hard coded 14 into the DC. After that everything worked fine. I then added a few more pixels for a padding at the end of the string.

    Below is my code. I used CyberLynx's base code and changed some names and added the needed declarations and code. I only did the width function.

    Code:
    Option Compare Database
    Option Explicit
    
    ' Type Declaration for the GetTextExtentPoint32 function.
    Type SIZE
        cx As Long
        cy As Long
    End Type
    
    'For screen resolution
    Private Const LOGPIXELSX = 88
    Private Const LOGPIXELSY = 90
    
    'API for creating a font
    Declare Function CreateFont Lib "gdi32.dll" Alias "CreateFontA" ( _
         ByVal H As Long, _
         ByVal W As Long, _
         ByVal E As Long, _
         ByVal O As Long, _
         ByVal W As Long, _
         ByVal I As Long, _
         ByVal u As Long, _
         ByVal S As Long, _
         ByVal C As Long, _
         ByVal OP As Long, _
         ByVal CP As Long, _
         ByVal Q As Long, _
         ByVal PAF As Long, _
         ByVal F As String) As Long
    
    ' The API function to get screen resolution
    Private Declare Function GetDeviceCaps Lib "gdi32" _
                                    (ByVal hDC As Long, _
                                     ByVal nIndex As Long) As Long
                                     
    ' The API function for selecting an object into a DC, a font in our case
    Declare Function SelectObject Lib "gdi32.dll" ( _
         ByVal hDC As Long, _
         ByVal hObject As Long) As Long
         
    ' The API function for deleting an object. The created font in our case
    Declare Function DeleteObject Lib "gdi32.dll" ( _
         ByVal hObject As Long) As Long
    
    ' API function to release a DC
    Declare Function ReleaseDC Lib "user32.dll" ( _
         ByVal hWnd As Long, _
         ByVal hDC As Long) As Long
     
    ' 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 SIZE) 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 GetFocus Lib "user32" () As Long
     
    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 = GetFocus
        End If
        On Error GoTo 0
    End Function
     
     
    Public Function GetStringTwipWidth(Ctrl As Control) As Long
        
        Dim szTextSize As SIZE
        Dim hDC As Long
        Dim hWnd As Long
        Dim lngHorPixPerInch As Long
        Dim FONT As Long 'this will hold the font info we create
        
        'Get the Handle (hWnd) of the Control
        hWnd = GethWnd(Ctrl)
        'Get the Control's device context
        hDC = GetWindowDC(hWnd)
        
        'create font matching the control font
        FONT = CreateFont(14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, Ctrl.FontName)
        Call SelectObject(hDC, FONT)
        
        'get pixels per inch for screen resolution
        lngHorPixPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
       
       'Get the width of our Text String in logical units, which should be pixels
       'unless otherwise specified, I think.
       GetTextExtentPoint32 hDC, Ctrl.Text, Len(Ctrl.Text), szTextSize
       
       'Release the DC. Some say it is necessary, some say not.
       Call ReleaseDC(hWnd, hDC)
       'I don't know if this is needed
       Call DeleteObject(FONT)
       
       'divide string length(in pixels) by pixels/inch to get length of string in
       'inches, then multiply by 1440 to convert to twips. I added 5 pixels for padding
       GetStringTwipWidth = ((szTextSize.cx + 5) / lngHorPixPerInch) * CLng(1440)
       
    End Function
    The only drawback is I had to hard code the font size for the DC, but there may be a ratio that could be used. I also found a nice tool for working with VB/VBA and the API which is the API Viewer. It has been around, but was new to me. I believe there a several versions, but I did a google to find the one I used.

    One last note. If you can put your report/form in design mode you can do the same thing by using sizeToFit on the control, but if you make a MDE that option doesn't work. The code probably needs some more error checking, but it should be a good base if anyone needs it.

    I hope this helps someone. Thanks CyberLynx for the base code.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Good show...It's nice to see someone actually attack this particular item and alternatively accomplish another solution.

    Although not perfect...I think I can safely speak for everyone when I say that gwgeller has made a significant stride towards the definitive solution and we all thank you for it. Others will benefit from it.

    If more people would take a just a moment to dabble with this problem the ultimate solution will be born.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •