Microsoft KB Archive/324584

= HOW TO: Limit the Number of Characters to the Size of the Text Box in Access 2002 =

Article ID: 324584

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q324584



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 198649.

For a Microsoft Access 97 version of this article, see 152475.

IN THIS TASK
SUMMARY
 * How to Create the LimitTextToControlWidth Procedure
 * How to Use the LimitTextToControlWidth Procedure

REFERENCES



SUMMARY
This article shows you how to limit the text a user can type into a text box so that the width of the text is not greater than the width of the text box. back to the top

How to Create the LimitTextToControlWidth Procedure
What follows is a Microsoft Visual Basic for Applications (VBA) procedure that you can use to limit the text that a user can type into a text box. The procedure is named LimitTextToControlWidth, and it is called from the KeyPress event of a text box.

This procedure is ideal for developers who want to use a nonproportionally spaced font to print text into a fixed location on a preprinted form. The LimitTextToControlWidth procedure makes sure that a user cannot type more text than what will fit in the defined area on the form.

A number of factors affect the width of text. The larger the font size, the fewer the characters that you can type. The font that you select also affects the width of characters in the text. Characters in a nonproportionally spaced font, such as Arial or Times New Roman, have different widths. The letter &quot;i&quot; is narrower in width than the letter &quot;X.&quot; Additionally, the letter &quot;i&quot; in one font may have a different width in another font. For example, the letter &quot;i&quot; in Arial font is much narrower than the letter &quot;i&quot; in Courier New font.

NOTE: The following code uses the GetTextExtentPoint Microsoft Windows application programming interface (API) function. You can call this API function only from the control that has focus on an Access form.

  Create a new module, and then type the following in the Declarations section: Option Explicit

Const LOGPIXELSX = 88 Const LOGPIXELSY = 90 Const TWIPSPERINCH = 1440

Type Size cx As Long cy As Long End Type

Declare Function GetDC Lib &quot;user32&quot; (ByVal hWnd As Long) As Long Declare Function GetDeviceCaps Lib &quot;gdi32&quot; (ByVal hDC As Long, _  ByVal nIndex As Long) As Long Declare Function GetFocus Lib &quot;user32&quot; As Long Declare Function GetTextExtentPoint Lib &quot;gdi32&quot; Alias _ &quot;GetTextExtentPointA&quot; (ByVal hDC As Long, ByVal lpsz As String, _  ByVal cbString As Long, lpSIZE As Size) As Long Declare Function ReleaseDC Lib &quot;user32&quot; (ByVal hWnd As Long, _  ByVal hDC As Long) As Long   Type the following procedures: Sub ConvertPixelsToTwips(X As Long, Y As Long) Dim hDC As Long, hWnd As Long, RetVal As Long Dim XPIXELSPERINCH As Long, YPIXELSPERINCH As Long

 Retrieve the current number of pixels per inch, which is   resolution-dependent. hDC = GetDC(0) XPIXELSPERINCH = GetDeviceCaps(hDC, LOGPIXELSX) YPIXELSPERINCH = GetDeviceCaps(hDC, LOGPIXELSY) RetVal = ReleaseDC(0, hDC)

'' Compute and return the measurements in twips. X = (X / XPIXELSPERINCH) * TWIPSPERINCH Y = (Y / YPIXELSPERINCH) * TWIPSPERINCH End Sub

Sub LimitTextToControlWidth(KeyAscii As Integer) Dim AC As Control Dim Txt As String Dim TxtWidth As Long, SpaceWidth As Long Dim hWnd As Long, hDC As Long Dim lpSIZE As Size Dim RetVal As Long

'' Exit if a non-printable character is typed. If KeyAscii < 32 Then Exit Sub

'' Record active control. Set AC = Screen.ActiveControl

'' Get the control text. Txt = AC.Text & &quot;&quot;

'' Append typed character into the text. If KeyAscii > 32 Then Txt = Left(Txt, AC.SelStart) Txt = Txt & Chr$(KeyAscii) Txt = Txt & Mid(Txt, AC.SelStart + 1 + AC.SelLength) End If

hWnd = GetFocus hDC = GetDC(hWnd)

'' Compute the width of the text. RetVal = GetTextExtentPoint(hDC, Txt, Len(Txt), lpSIZE) ConvertPixelsToTwips lpSIZE.cx, lpSIZE.cy  TxtWidth = lpSIZE.cx

'' Compute width of a space. RetVal = GetTextExtentPoint(hDC, &quot; &quot;, 1, lpSIZE) ConvertPixelsToTwips lpSIZE.cx, lpSIZE.cy  SpaceWidth = lpSIZE.cx   '' Are there trailing spaces to contend with? If AC.SelStart + 1 > Len(Txt) Then '' Add number of spaces * SpaceWidth to width of string. TxtWidth = TxtWidth + ((AC.SelStart + 1 - Len(Txt)) * _         SpaceWidth) End If

If TxtWidth + (SpaceWidth / 2) > AC.Width Then Beep KeyAscii = 0 End If

End Sub 

back to the top

How to Use the LimitTextToControlWidth Procedure
  Create a new form that is not based on any table or query, and then set the following properties:   Form: Test1 -  Caption: TestForm

Text box ---  Name: One Left: 1&quot; Top: .5&quot; Width: 1&quot; Height: 0.2&quot; FontName: Arial FontSize: 12

Text box ---  Name: Two Left: 1&quot; Top: 1&quot; Width: 1&quot; Height: 0.1667&quot; FontName: Arial FontSize: 8

Text box ---  Name: Three Left: 1&quot; Top: 1.5&quot; Width: 1&quot; Height: 0.3743&quot; FontName: Arial FontSize: 24   Call the LimitTextToControlWidth procedure from the KeyPress event of each text box that is listed in step 1. For example: Sub One_KeyPress(KeyAscii As Integer) LimitTextToControlWidth KeyAscii End Sub

  View the form, and then try to type the following text in each text box:

This is a test of the emergency broadcast system

Notice that the result appears as follows: <pre class="fixed_text">  Text box     Text permitted to type ---  One          This is a test Two         This is a test of th   Three        This i                    </li></ol>

back to the top

<div class="references_section">