Cart    Contact Us    Tutorials    Downloads    About Us    News

MDE to MDB Conversion Service
(plus ACCDE to ACCDB Conversion)

- convert compiled Access files back to their original formats including VBA code

Database Repair Service

- a trusted service for repairing corrupt Microsoft Access files

vbWatchdog   [Addin]   *SPECIAL OFFER NOW ON*

- global error handling library for VBA & VB6 projects

vbMAPI   [Addin]   *SPECIAL OFFER NOW ON*

- Outlook/MAPI library for VBA, .NET and C# projects

Code Protector   [Software]   *SPECIAL OFFER NOW ON*

- strengthens protection against reverse engineering of MDE/ACCDE files

*** SPECIAL OFFERS AVAILABLE UNTIL 31st JULY 2014 - DETAILS HERE ***

Limiting text input length of an unbound text box

        16 votes: ***       16,252 views      1 comments
by Allen Browne, 20 April 2005    (Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, allen@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

Home Index of tips Top

<< Back to tutorials index




Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.



Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

What did you think of 'Limiting text input length of an unbound text box'?


1.

Jordan says...

18 March 2013

 
Awesome validation code. works well and is efficient and clean. thanks a million govena!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code
Please note: It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.   All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.


Site tools