ACC: Limiting Number of Characters Typed into Unbound Text Box

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

The following information describes 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

The number of characters you can type into an unbound text box control can be limited in one of the following ways.

Method 1

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

   Input Mask: CCCCC

Method 2

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

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

REFERENCES

For more information about KeyPress event, search the Help Index for "KeyPress Event," or ask the Microsoft Access 97 Office Assistant.