IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org
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.
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.
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.
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).
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|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Limiting text input length of an unbound text box'?
18 March 2013
Awesome validation code. works well and is efficient and clean. thanks a million govena!
Have your say...