Restricting Long Text field to character limit of 500.

[JRodko]

The short text type is a little bit short for what I need and I don't believe you can control field size for long text in the table properties, thus I'm trying to do it in a form.



I am not doing it in data validation because I'd like the user to be notified when they exceed 500 characters so that they don't end up trying to save and then have to re-do a bunch of work.

In the "On Change" event I have the following code;

Sub DDH_Purpose_Change()
If Len(Me![DDH_Purpose].Text >= 500) Then
MsgBox "Please limit your entry to 500 characters"
End If
End Sub

but I notice the message box pops up every single time I change anything in that field.

Ideas?

Thank you.

[pbaldy]

I think your parentheses are off. Try:

If Len(Me![DDH_Purpose].Text) >= 500 Then

[isladogs]

That's the whole point of the Change event. It triggers after every change
You could use the After_Update event which will only trigger once.
However by that time, users could enter 5000 characters so will be somewhat annoyed to see the message.

What you need to do is count the characters as they are added and lock the textbox when it reaches 500 characters.
I normally use a visual display of characters remaining. Screenshot to follow when I'm at my PC.

The properties you need are SelText, SelLength and SelStart.
Try Access help for suitable code. If you get stuck I'll post code later

BTW did you manage to import the frx file from a week or so ago?

[JRodko]

pbaldy,

Thank you very much. This seems to be behaving the way I want now.

isladogs,

Thank you! I'd be curious to see how this works, if you have time. I have to decide if I want the 500 to be a hard or soft limit.

[pbaldy]

pbaldy,

Thank you very much. This seems to be behaving the way I want now.

No problem.

[isladogs]

This is an example of a form with 2 fields with a character count & cutoff:



To do this add these API declarations to a standard module (will need modifying if you have 64-bit Access)

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

Then in your form, use code similar to this - I've removed error handling code to save space

Private Sub Report_Change()

    SelText = SendMessage(hWnd, WM_COPY, 0, 0)
    SetReportLength
        
End Sub


Private Sub SetReportLength()

'called by Change event for Report
    Dim WindowHandle As Long
    Dim MaxLength As Long
    WindowHandle = GetFocus()
    
'Get the window handle for this window.
    WindowHandle = GetFocus()
    MaxLength = intReportLength   'this value is defined in a settings table or it can be hard coded

'Limit the number of characters in the Report text box.
    SendMessage WindowHandle, ConstSetLimitText, MaxLength, 0

'get text length
Const WM_GETTEXTLENGTH = &HE
   Me.txtCharCount = "( Characters remaining : " & MaxLength - SendMessage(WindowHandle, WM_GETTEXTLENGTH, 0, 0) & " )"
    
End Sub

You didn't answer my question about your frx file that I looked at for you

[JRodko]

Thanks isladogs! I'm going to keep this in my back pocket but I think it's a little over my head for the time being. I don't want to implement something unless I understand it completely.

As per your FRX post. I will reply there.

[isladogs]

Basically it just uses an API call. The code was originally in an MS article IIRC and should be used without any need for alteration apart from control names.

[JRodko]

Well since you've gone to the effort to share it maybe I will give it a shot.

What modifications do I need to run it on 64bit Access (Office 365)

[isladogs]

No change to form code
Change the declarations section in the standard module as below using conditional compilation so it will work in 64-bit or 32-bit:
The first 2 lines are also needed - omitted originally by mistake

Const ConstSetLimitText = &HC5&Const WM_COPY = &H301
' Modified from sources by Litwin, Getz, and Gilbert,  Stephen Lebans


'Conditional compilation for use in 64-bit or 32-bit Access (2010 or later)
#If VBA7 Then
     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 '32-bit Office
    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