This article was previously published under Q210047
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
This article shows you two techniques that you can use to limit the numberof characters that can be typed in an unbound text box on a form.
You can limit the number of characters that can be typed in an unboundtext box control in one of the following ways.
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 limits the number of characters typed in a text box to five:
Input Mask: CCCCC
You can use a Visual Basic for Applications function 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, such as 50 characters.
To limit the number of characters typed in a text box to 50, follow these steps:
Open any database and create a new form.
Add an unbound text box to your form.
Press ALT+F11 to open the Visual Basic Editor and insert a new module.
Type or copy and paste the following subroutine:
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 IfEnd Sub
For each unbound text box control where you want to control input, add the following code:
LimitFieldSize KeyAscii, 50
For example, for Text0 text box, use the following code:
Private Sub Text0_KeyPress(KeyAscii As Integer) LimitFieldSize KeyAscii, 50End Sub
Close the Visual Basic Editor and return to Microsoft Access. Enter a string of characters in Text0 text box and note that your input is limited to 50.
For more information about KeyPress event, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type KeyPress Event in the Office Assistant or the Answer Wizard, and then click Search to view the topic.