Count characters

Normally, it’s not an issue if an End User tries to type in more characters than the Control will allow… they just can’t type anymore. BUT, if you are using an UNBOUND Form for data entry this is a big problem, they will not be able to Save and with a not so nice message.  Let’s fix that…

Step 1

Add a TEXT BOX under the Control on your Form named txtCharactersAllowed. In the Properties window for this Control set Enabled to False and Locked to True.  This will make it so the TEXT BOX will not react when the User mouses over it and will display a *### characters left* message.

Step 2

In the On_Change Event Procedure for the Control on your Form place…

     Me.txtCharactersAllowed = 150 - Len(Me.txtYourControlName.Text) & " characters left"
        
        If Len(Me.txtYourControlName.Text) = 150 Then
           MsgBox "No more characters allowed - Entry will not save!", vbCritical + vbOKOnly, "Character Limit"
        End If

Note, in this example the maximum length of the field is 150, be sure to change to reflect the maximum characters allowed for your field.

Step 3

Just in case they ignore that message, I add this to the Save Command Button right at the top.

     Me.txtYourControlName.SetFocus
        
        If Len(Me.txtYourControlName.Text) = 150 Then
           MsgBox "You have exceeded the character limit allowed, adjust and try again!", vbCritical + vbOKOnly, "Save"
        Exit Sub
        End If

Stops the Save until they adjust and no ugly message and, oops, all their hard work just gone.