CODEWITHSUNDEEP
vbams-access
Costas
Costas

Reputation: 27

How to limit number of characters of form field

I have a form with an unbound comment field that we want to limit the number of characters in it. To make things as clean as possible, and to confirm it wasn't something else, I created a new form with two unbound text boxes. "Text0" is where you enter the data and "Text1" is the counter.

Private Sub Text0_Change()
    If Not IsNull(Me.Text0.Text) Then
        If Len(Me.Text0.Text) > 20 Then
            Dim resp As Integer
            resp = MsgBox("Exceeded the number of characters allowed.", vbOKOnly, "Too long")
            Me.Text0 = Left(Me.Text0, 20)
        End If
        Me.Text1 = Len(Me.Text0.Text) & _
        " /20 characters used."
    Else
        Me.Text1 = "0 /20 characters used."
    End If

End Sub

To the issue. If you start entering data all is fine, it counts the length. However, when it hits the limit the box comes up but as soon as you click OK it blanks the field.

I also tried to put in a validation rule but it doesn't fire until after loss of focus.

How do you prevent it from killing the data already entered? The actual field limit is much longer and re-typing the comments won't fly.

Upvotes: 2

Views: 2972

Answers (2)

Albert D. Kallal
Albert D. Kallal

Reputation: 49319

You don't need any code. Simply use a input mask setting in the text box.

In the input mask, just type in AAAAA (20 of them).

this way, you need no code at all, and the user will only be able to enter as many characters as you want.

Upvotes: 1

HansUp
HansUp

Reputation: 97131

... when it hits the limit the box comes up but as soon as you click OK it blanks the field. How do you prevent it from killing the data already entered?

Change this line ...

Me.Text0 = Left(Me.Text0, 20)

... to this ...

Me.Text0.Text = Left(Me.Text0.Text, 20)

That change preserves the first 20 characters of Me.Text0.Text when it is altered by the Left() assignment expression. The reason your original code was "killing the data already entered" is because Me.Text0 means Me.Text0.Value, not Me.Text0.Text

Upvotes: 3

Related Questions