Limited characters in a memo field

[Robbyp2001]

I have several forms that are linked to a memo field in the main table. This is also linked to a report for printing. I have to limit the characters in the memo field so that the text does not go beyond what can be seen on the report. So, i need to know how I can limit the number of characters that can be netered into the memo field to, say 1500, and I need to show a message box when that limit has been reached.



Can anyone tell me how to do this?

[John_G]

Hi -

Use the On Change event of the control to count the number of characters used; this runs each time the user types (or deletes) a character. For example:

Private Sub ControlText_Change()
Me.Caption = 1500 - Len(ControlText.Text) & " characters remaining)"
If Len(ControlText.Text) > 1490 then
... warn user
endif
End Sub

HTH

John

[boblarson]

Why do you need to limit this? It is totally easy to include ALL on the Report, depending on what your report is supposed to look like. The main problem of truncation can be solved by a special trick when building the report's record source and if the text box is not large enough to display all of the text, then it can be set to CAN GROW = YES in design view and then it can grow to whatever size it needs to be.

But, if you still want to limit them, the John's method will work, except that you would need to supply more than a warning. You would need to cancel the keystroke if it is reached. So, instead of the On Change event you might want to use the KeyPress event. Then you can cancel the keystroke by setting the KeyCode to 0 if the threshold has been reached.

[Robbyp2001]

Thanks for your help folks. I will try this. Bob, the reason that I need to limit the number of characters is so that the user cannot go beyond what can physically be seen on the paper printed report.

[Robbyp2001]

Hello again folks. I tried the Key Press event and put in the following code exactly:

Private Sub MTR_Teacher_Comments_KeyPress(KeyAscii As Integer)
Me.Caption = 800 - Len(ControlText.Text) & " characters remaining)"
If Len(ControlText.Text) > 790 Then
retvalue = MsgBox("Too Many Characters!", vbExclamation)
End If
End Sub

Unfortunately now when typing into the field the databse returns the the error message: Run-time Error '424': Object Required.

I know I'm missing something very simple here.

[boblarson]

If this is in the control MTR_Teacher_Comments then you shouldn't be using

ControlText.Text

but

MTR_Teacher_Comments.Text

in the two places that are there.

the reason that I need to limit the number of characters is so that the user cannot go beyond what can physically be seen on the paper printed report.

What do you mean? You can have the whole 65,000 characters on the paper printed report, IF YOU WANT THEM THERE. Do you have to limit it because you have a specific Report Format that you have to follow and can't have the text boxes grow to fit the number of characters?

[Robbyp2001]

Yes Bob, that's exactly the problem. The reports are on paper size A5 so the space for comments is very limited, given that there are other bits and pieces of information on there. The comments box cannot grow or the text will not be seen. The report cannot have additional pages to accommodate extra characters. It is very fixed in size.

It's unlikely that anyone will actually reach the maximum number of characters that can be seen, but you never know, someone might decide to be creative. Therefore I need the database to flag up a warning that they are about reach the maximum or have reached the maximum. I would prefer this to happen as they type rather than inform them when they try to navigate away from the field.

[Robbyp2001]

Sorry Bob, that should have said "The comments box cannot grow AND the text will not be seen".

I have checked this and the maximum number of characters that can be displayed given the font size is 800.

[boblarson]

Sorry Bob, that should have said "The comments box cannot grow AND the text will not be seen".

I have checked this and the maximum number of characters that can be displayed given the font size is 800.

So, yes, you would need to limit them then if they must fit withing a predefined area. The KeyDown method can cancel any keystrokes past the level you have defined and give an custom message using a message box. You can also give a message box a bit earlier to warn of impending limits being reached.

[Robbyp2001]

Thanks for the info Bob. Do you know what the code would be to achieve this, given that the maximum character count is 800?

Rob

[boblarson]

The KeyPress method is what I meant. And it would be:

Private Sub ControlNameHere_KeyDown(KeyAscii As Integer)
    Dim lngText As Long
    Dim strMsg As String
 
    lngText = Len(Me.ControlNameHere.Text)
 
    Select Case lngText
       Case 780
          strMsg = "Warning - You have only 20 available characters remaining."
       Case 800
          strMsg = "OUT OF SPACE - You have reached the maximum characters allowed."
          KeyAscii = 0
   End Select
 
   If strMsg <> vbNullString Then
      MsgBox strMsg, vbInformation, "Text Notification"
   End If
End Sub

[Robbyp2001]

This works perfectly Bob. Many thanks, you're a star!

Rob