Handle Long Text in Field

[Guest]

I have 2 types of "text" fields: text and memo.

For the memo fields, I have a nice handler that, on Exit, checks the length
of data and if it is too long, puts up a message AND selects the portion of
the text that exceeds the allowable length.

For the text fields, I just get a rude message that the text is too long,
and it becomes a guessing game as to how much my user can put in. I would
like to use the same handler (see below), but cannot figure out which handler
to put it in to trap the error. On Exit does not work.

Handler for checking length of text in memo field:
Private Sub Comments_Exit(Cancel As Integer)
Dim MaxLength As Long, txtLength As Long
MaxLength = 500
txtLength = Len(Comments.Text)
If txtLength > MaxLength Then
MsgBox "You can only enter up to " & MaxLength _
& " characters for Comments. Please reduce your text by " _
& Len(Me.Comments) - MaxLength & " characters."
Comments.SetFocus
Comments.SelStart = MaxLength + 1
Comments.SelLength = txtLength - MaxLength
Cancel = True
End If
End Sub

 

[Ken Snell [MVP]]

The error that you get about "Text" too long is occurring in the form's
internal workings.

You can trap for this error using the form's Error event. How you do it
depends upon what you want the form to do or not do.

If you don't want the form to display any errors at all (the assumption then
is that your code will do all error handling), you could use this code in
the form's Error event procedure:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
End Sub

This will prevent the form from displaying the Text error (and all other
data-related errors).

However, if you want to use your code as an "error handler" for long Text,
then identify the Err number that corresponds to the error that you see, and
use this code (example assumes that the error number is 10000):

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 10000 Then Response = acDataErrContinue
End Sub