ACC: How to Limit Number of Characters Typed into Unbound Text Box Control (152050)

SUMMARY

This article shows you two techniques that you can use to limit the number of characters that can be typed into an unbound text box on a form.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

You can limit the number of characters that can be typed into an unbound text box control in one of the following ways.

Method 1

You can specify an InputMask property setting. This is by far the simplest method. For example, a text box with the following InputMask property setting will limit the number of characters entered into a text box to 5:

Input Mask: CCCCC

Method 2

You can use a Visual Basic for Applications function (or an Access Basic function in Microsoft Access version 2.0) called from the KeyPress event.

Although an InputMask is easy to implement, it may be tedious and error prone to use if you need to limit the number of characters to a much larger value, like 50 characters. To limit the number of characters typed into a text box to 50 characters, create the following function in a global module:

Sub LimitFieldSize (KeyAscii, MAXLENGTH)
      Dim C As Control
      Dim CLen As Integer

      Set C = Screen.ActiveControl

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

      ' Exit if typing replaces a selection.
      If C.SelLength > 0 Then Exit Sub

      ' Fetch length of current contents + 1 for the character typed.
      CLen = Len(C.Text & "") + 1

      ' Are there trailing spaces to contend with?
      If C.SelStart + 1 > CLen Then CLen = C.SelStart + 1

      ' Is length of string greater than max?
      If CLen > MAXLENGTH Then
        Beep
        KeyAscii = 0
      End If

   End Sub

Call the LimitFieldSize procedure from the KeyPress event of each unbound text box control that you want to limit. You must pass the KeyAscii parameter supplied by the KeyPress event and the number of characters you want to limit typing to:

   Sub MyUnboundTextBox_KeyPress (KeyAscii As Integer)
      LimitFieldSize KeyAscii, 50
   End Sub