[AccessD] $ Aligment in Combo Boxes

Julie,

    Sample subroutine named P_Align(), as given below, will ensure right alignment of number type contents of target column (Column number supplied as argument) of the combo box look-up list in currency format. The subroutine is called from form's Load event.

    For a two column combo box named CboMyCombo, sample code to be put in form's module so as to ensure that the first column is right aligned in currency format, is given below. Constants FontFactor and ColWidthFactor are based upon Times New Roman font (normal weight) and can be fine tuned for optimum results at your end.

    T_MyTable is the name of table having fields Amount (number or currency type) and Product (text type).

Best wishes,
A.D.Tejpal
---------------

Code in form's module
=======================================
Private Sub Form_Load()
    P_Align 1
End Sub
-------------------------------------------------------------------------
Private Sub P_Align(ByVal ColNum As Long)
    ' Modifies the row source of combo box so as to
    ' format the target column ColNum as right aligned
    ' currency type display (This is applicable to Times 
    ' New Roman font, Normal size)
    ' Note - ColNum is one based i.e. 1 for first column

    Dim Qst As String, ColWdString As String
    Dim TxtWd As Long, ColWd As Long
    Dim TxtRatio As Double, FntSz As Long
    Const FontFactor As Double = 0.15
    Const ColWidthFactor As Double = 0.2
    ' Note - (a) The value of 0.15 for FontFactor is found
    '                 to suit font size from 8 to 18 for Times
    '                 New Roman font, Normal size
    '            (b) ColWidthFactor is meant to compensate
    '                  for varying column widths.
    
    ' Get Font size
    FntSz = Me.CboMyCombo.FontSize
    TxtRatio = FontFactor / FntSz
    ' Get the string representing Column Widths
    ColWdString = Me.CboMyCombo.ColumnWidths
    ' Split ColWdString into array elements and get
    ' the width of target column represented by ColNum
    ' (ColWd is in twips)
    ColWd = Split(ColWdString, ";")(ColNum - 1)
    ' Get the number of characters that can be
    ' accomodated in this column
    TxtWd = Int((ColWd + ColWidthFactor * _
                            (ColWd - 2000)) * TxtRatio)
    
    ' Build SQL formatting the number field as desired
    Qst = "SELECT Right(Space(" & TxtWd & ") & " & _
            "Format(Amount, 'Currency'), " & TxtWd & ") " & _
            "AS Amt, Product FROM T_MyTable;"

    Me.CboMyCombo.RowSource = Qst
End Sub
=======================================

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Tuesday, August 01, 2006 14:07
  Subject: Re: [AccessD] $ Aligment in Combo Boxes


  Hi Julie

  It's not a bug. It is just that numbers are converted to strings as a combo only understands strings. A limitation you may call it.

  A simple work-around is to format your numbers to strings and use a font that displays any digit with the same width like MS Sans Serif. This font displays dot and comma at half the width of a digit, and the width of a digit equals two spaces. Now, decide for a maximum number to display, say 1 mio., and count the spaces this will fill with your chosen format. For the number to be displayed, count the digits, commas and dots and calculate how much it fills and deduct that from your maximum length. Convert that to spaces - use Space(n) - and prefix your formatted number string with this string of spaces to create a pseudo right align view. Use the full string as the entry in your combo.

  /gustav

  >>> prosoft6 at hotmail.com 31-07-2006 22:21:04 >>>
  This is driving me crazy..............my first item in a two column combo box is formatted in currency and the second is simply text.
  The currency will not right align!   I have googled the topic and find many other questions about this problem with no answers that are simple and quick.  Do I reall have to write a module to correct this?

  Sounds like a microsoft bug to me!

  Julie Reardon-Taylor
  PRO-SOFT OF NY, INC.
  44 Public Square Suite #5
  Watertown, NY 13601
  Phone: 315.785.0319
  Fax: 315.785.0323
  www.pro-soft.net 
  NYS IT Services Contract CMT026A
  NYS Certified Woman-Owned Business