Code Samples for Businesses, Schools & Developers

Last Updated 26 Mar 2022


This follows on from a previous article on this website: Prevent Copy & Paste in Access forms

This article explains how the number of characters in long text fields can be limited using VBA code.
It also provides code to check the spelling of entered text.
This is particularly useful if the dictation tool is used for entering text. Dictation can work well with long text fields.

For short text fields, the field size can be set in table design (up to 255 characters MAX)

However, long text (AKA memo) fields can be up to 65,536 characters through manual text entry or up to 1 GB if entered programmatically.
See the Microsoft support article: Access specifications

It is NOT possible to limit the number of allowed characters in the table design. However, it can be done using VBA code.

In the form shown below, Notes is a long text field and code is used to limit the number of characters to 500.

Image1
The form includes the following code

CODE:


Dim SelText As String
Dim ControlName As String

#If VBA7 Then
      Dim WindowHandle As LongPtr
#Else
      Dim WindowHandle As Long
#End If

Const MaxNotesLength As Long = 500 'normally set this in a table
Const ConstSetLimitText = &HC5&
Const WM_COPY = &H301
Const WM_GETTEXTLENGTH = &HE

' Modified from sources by Litwin, Getz, and Gilbert, Stephen Lebans
'###############################################

'SendMessage API
'Sends the specified message to a window or windows.
'The function calls the window procedure for the specified window
'... and does not return until the window procedure has processed the message.

'GetFocus API
'Retrieves the handle to the window that has the keyboard focus
'... if the window is attached to the calling thread's message queue.

#If VBA7 Then       'Office 2010 or later (32/64-bit)
      Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
            (ByVal hwnd As LongPtr, ByVal wMsg As Long, _
            ByVal wParam As LongPtr, lParam As Any) As LongPtr

      Private Declare PtrSafe Function GetFocus Lib "user32" () As LongPtr       'used to find window handle
#Else       'Office 2007 or earlier (32-bit)
      Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
            (ByVal hwnd As Long, ByVal wMsg As Long, _
      ByVal wParam As Long, lParam As Any) As Long

      Private Declare Function GetFocus Lib "user32" () As Long      'used to find window handle
#End If
'###############################################

Private Sub SetNotesLength()

      On Error GoTo Err_Handler
     'called by Change event for Notes

     'Get the window handle for this window.
      WindowHandle = GetFocus()

     'Limit the number of characters in the Notes text box.
      SendMessage WindowHandle, ConstSetLimitText, MaxNotesLength, 0

      'get text length
      Me.txtCharCount = "( Characters remaining: " & MaxNotesLength - SendMessage(WindowHandle, WM_GETTEXTLENGTH, 0, 0) & " )"
      ' Debug.Print Me.txtCharCount

      If MaxNotesLength - SendMessage(WindowHandle, WM_GETTEXTLENGTH, 0, 0) = 0 Then
            FormattedMsgBox "You have entered the MAXIMUM allowed characters (" & MaxNotesLength & ") " & _
                  "@No more text can be entered @", vbCritical, "Character limit = " & MaxNotesLength
      End If

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err & " in line " & Erl & " of SetNotesLength procedure: " & Err.Description
      Resume Exit_Handler

End Sub

-----------------------------------------------------

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

      'Set the form's Key Preview property on the Event tab to YES for this to take effect

      Dim intCtrlDown As Integer

      intCtrlDown = (Shift And acCtrlMask) > 0 'value = -1 (true) if Ctrl clicked

      If KeyCode = vbKeyV Then
            If intCtrlDown Then 'Ctrl+V pressed
                  MsgBox "Pasting text is not allowed on the form", vbCritical, "NOT ALLOWED"
                  KeyCode = 0
            End If
      End If

      If KeyCode = vbKeyC Then
            If intCtrlDown Then 'Ctrl+C pressed
                  MsgBox "Copying text is not allowed on the form", vbCritical, "NOT ALLOWED"
                  KeyCode = 0
            End If
      End If

End Sub

-----------------------------------------------------

Private Sub Form_Load()

      Me.LblCharLimit.Caption = "The Notes field is limited to " & MaxNotesLength & " characters"

End Sub

-----------------------------------------------------

Private Sub Notes_Change()

On Error GoTo Err_Handler

      SelText = SendMessage(hwnd, WM_COPY, 0, 0)

      SetNotesLength

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err & " " & Err.Description & " in Notes_Change procedure"
      Resume Exit_Handler

End Sub

-----------------------------------------------------

Private Sub Notes_GotFocus()

      Me.txtCharCount = "( Characters remaining: " & MaxNotesLength - Len(Me.Notes & "") & " )"

End Sub

-----------------------------------------------------

Private Sub Notes_LostFocus()

On Error GoTo Err_Handler

      'If the Notes field contains data, run the Spell Checker after data is entered.
      If Len(Me.Notes & "") > 0 Then
            SpellCheckControl Me.Notes
      End If

Exit_Handler:
      Exit Sub

Err_Handler:
      MsgBox "Error " & Err & " " & Err.Description & " in Notes_LostFocus procedure"
      Resume Exit_Handler

End Sub



As text is entered, the number of characters remaining is reduced:

Image2
Once the limit is reached, no more text can be entered (unless some of the existing text is first deleted)

Image3
Code is also used to check the spelling once text entry is complete:

Image4

The code for the spell checker is placed in a standard module:

Option Compare Database
Option Explicit

'########################################################################
'# Allows spell check of a control (it must be a text box)
'########################################################################

Public Sub SpellCheckControl(ctlSpell As Control)
' Adaptation by Terry Wickenden of code from Microsoft Knowledge Base

If TypeOf ctlSpell Is TextBox Then
      If ctlSpell.Locked = False And ctlSpell.Visible = True Then
            If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
                  'MsgBox "There is nothing to spell check."
                  ctlSpell.SetFocus
                  Exit Sub
            End If

            With ctlSpell
                 .SetFocus
                 .SelStart = 0
                 .SelLength = Len(ctlSpell)
            End With

            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdSpelling
            DoCmd.SetWarnings True
      End If

ElseIf TypeOf ctlSpell Is SubForm Then
      If ctlSpell.Locked = False And ctlSpell.Visible = True Then
            If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
                  'MsgBox "There is nothing to spell check."
                  ctlSpell.SetFocus
                  Exit Sub
            End If

           With ctlSpell
                  .SetFocus
                  .SelStart = 0
                  .SelLength = Len(ctlSpell)
            End With

            DoCmd.SetWarnings False
           DoCmd.RunCommand acCmdSpelling
            DoCmd.SetWarnings True
      End If

Else
      MsgBox "Spell check is not available for this item."
End If

ctlSpell.SetFocus

End Sub





YouTube Video - 18 Nov 2021

The above information is included in my Set Character Limit in Long Text Fields video on YouTube.

The video demonstrates how to do the following:
1.   Limit the number of characters in code
2.   Display the number of characters remaining
3.   Use of dictation mode in long text fields
4.   Use of spell checker

The video is available at: https://youtu.be/k55twtNBmH4 or you can click on the video below

       



An example app is provided containing all the above code

Click to download:     Set Character Limit              Approx 0.7 MB (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 26 Mar 2022

Return to Code Samples Page Return to Top