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.
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:
Once the limit is reached, no more text can be entered (unless some of the existing text is first deleted)
Code is also used to check the spelling once text entry is complete:
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