Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Unbound text box: limiting entry length

Access automatically prevents you entering too much text if a control is bound to a field. Unbound controls can be limited with the Input Mask - one "C" for each possible character. However, the input mask has side effects such as appending underscores to the Text property and making it difficult to insert text into the middle of an entry.

For a cleaner result, use a combination of the control's KeyPress and Change events.

Here's how.

1. Paste the two "subs" from the end of this article into a module. Save.
2. Call LimitKeyPress() in your text box's KeyPress event. For example, to limit a control named "City" to 40 characters, its KeyPress event procedure is:
       Call LimitKeyPress(Me.City, 40, KeyAscii)
3. Call LimitChange() in your text box's Change event. For the same example, the Change event procedure is:
       Call LimitChange(Me.City, 40)
4. Repeat steps 2 and 3 for other unbound text/combo boxes in your application.

Why Both Events?

When the Change event fires, the text is already in the control. There is no way to retrieve the last acceptable entry if it's too long. You could create a variable for each control, store its last known good entry, and restore that value if the Change event finds the text is too long. However, maintaining such a variable would be a nightmare: can you guarantee to initialize every variable to the control's DefaultValue in the form's Load event, update its variable on every occasion that a control is written to programmatically, effectively document this to ensure no other programmer writes to the control without updating the variable, etc.?

The KeyPress event does not have these problems. You can simply discard unacceptable keystrokes, leaving the text in the control as it was. However, this event alone is inadequate: a user can paste text into the control without firing the KeyPress, KeyDown, or KeyUp events.

We need both events. Block unacceptable keystrokes in the KeyPress event before they reach the control, and truncate entries in the Change event if the user pastes in too much text.

How Do These Procedures Work?

LimitKeyPress()

When a user types a character into the control, KeyPress is triggered. The value of KeyAscii tells you the character typed. Setting KeyAscii to zero destroys the keystroke before it reaches the text box. The middle line of this procedure does this, after checking two conditions.

The first "If ..." reads the number of characters already in the text box (its Text property). If any characters are selected, they are replaced when a character is typed, so we subtract the length of the selection (its SelLength property). If Access happens to be in Over-Type mode and the cursor is in the middle of the text, a character is automatically selected so over-type still works.

Non-text keystrokes (such as Tab, Enter, PgDn, Home, Del, Alt, Esc) do not trigger the KeyPress event. The KeyDown and KeyUp events let you manage those. However, BackSpace does trigger KeyPress. The second "If ..." block allows BackSpace to be processed normally.

LimitChange()

This procedure cleans up the case where the user changes the text in the control without firing the KeyPress event, such as by pasting. It compares the length of the text in the control (ctl.Text) to the maximum allowed ( iMaxLen). If it is too great, the procedure does 3 things: it notifies the user (MsgBox), truncates the text (Left()), and moves the cursor to the end of the text (SelStart).

The Code

Paste these into a module. If you do not wish to use the LogError() function, replace the third last line of both procedures with:

    MsgBox "Error " & Err.Number & ": " & Err.Description

Sub LimitKeyPress(ctl As Control, iMaxLen As Integer, KeyAscii As Integer)
On Error GoTo Err_LimitKeyPress
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's KeyPress event procedure:
    '             Call LimitKeyPress(Me.MyTextBox, 12, KeyAscii)
    ' Note:     Requires LimitChange() in control's Change event also.

    If Len(ctl.Text) - ctl.SelLength >= iMaxLen Then
        If KeyAscii <> vbKeyBack Then
            KeyAscii = 0
            Beep
        End If
    End If

Exit_LimitKeyPress:
    Exit Sub

Err_LimitKeyPress:
    Call LogError(Err.Number, Err.Description, "LimitKeyPress()")
    Resume Exit_LimitKeyPress
End Sub

Sub LimitChange(ctl As Control, iMaxLen As Integer)
On Error GoTo Err_LimitChange
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's Change event procedure:
    '               Call LimitChange(Me.MyTextBox, 12)
    ' Note:     Requires LimitKeyPress() in control's KeyPress event also.

    If Len(ctl.Text) > iMaxLen Then
        MsgBox "Truncated to " & iMaxLen & " characters.", vbExclamation, "Too long"
        ctl.Text = Left(ctl.Text, iMaxLen)
        ctl.SelStart = iMaxLen
    End If

Exit_LimitChange:
    Exit Sub

Err_LimitChange:
    Call LogError(Err.Number, Err.Description, "LimitChange()")
    Resume Exit_LimitChange
End Sub