Restrict size on Memo field

[gobble]

This is probably quite easy to do but how do you restrict the size of a memo field so that you cannot enter more than 200 characters etc?

Any help?

Thanks

 

[gobble]

Sorry, start again!!! I know you can use a text field for this, so how can I restrict a memo field to 500 characters!!!

 

[B827]

You could use the onkey event with the following

Dim FieldStr As String
FieldStr = Me!textField.Text
Select Case Len(FieldStr)
Case Is < 490
Me!Label6.Caption = 500 - Len(FieldStr)
Me!Label6.ForeColor = 0
Case 490
MsgBox &quot;Ten Characters Left&quot;, vbInformation + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = 500 - Len(FieldStr)
Case Is >= 500
MsgBox &quot;NoCharacters Left&quot;, vbCritical + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = &quot;FULL&quot;
Me!Label6.ForeColor = 255
End Select
Sandy

 

[gobble]

Thanks Sandy, I got that working. However is there anyway of stopping the message appearing when you are deleting a character. As at the moment if I am at the 500. I then press delete the message appears and then press delete again and the message appears. Also when I am deleting past 490 the message 10 characters left appears.

That might be asking too much????.........

Thanks

Juls

 

[jebry]

Hi Juls!

You can also do this at the table level using the validation rule and text. Set the rule to Len([YourField]) <= 500 and the text to whatever message you want them to get. I would still use the code for entering text on a form because the table level validation will not check the length until you try to save it. But it will keep any errors from occurring if a user gets behind the scene or if you need to include the memo field in an updateable query.

To correct the problem your having just add the following to the end of your Select statement:

Case Else
Me!Label6.Caption = 500 - Len(FieldStr)
Me!Label6.ForeColor = 0


hth
Jeff Bridgham
bridgham@purdue.edu

 

[B827]

You might have to take car of copying and pasting more tha 500 characters into the Textbox. The following code worked for me in the key up event. You may handle the oversize problem differently, I just used the left most 499 chars and put a # on the end.

Dim FieldStr As String
FieldStr = Me!textField.Text
If Me!Label6.Caption = &quot;FULL&quot; Then
If Len(FieldStr) <= 500 Then
Me!Label6.Caption = 1 '500 - Len(FieldStr)
Me!Label6.ForeColor = 255
Else
MsgBox &quot;No Characters Left truncating data&quot;, vbCritical + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = &quot;FULL&quot;
Me!Label6.ForeColor = 255
Me!textField.value = Left(FieldStr, 499) & &quot;#&quot;
End If
Else
Select Case Len(FieldStr)
Case Is < 490
Me!Label6.Caption = 499 - Len(FieldStr)
Me!Label6.ForeColor = 0
Case 490
MsgBox &quot;Ten Characters Left&quot;, vbInformation + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = 499 - Len(FieldStr)
Me!Label6.ForeColor = 255
Case Is < 499 And Len(FieldStr) > 491
Me!Label6.Caption = 500 - Len(FieldStr)
Me!Label6.ForeColor = 255
Case 499
MsgBox &quot;No Characters Left&quot;, vbCritical + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = &quot;FULL&quot;
Me!Label6.ForeColor = 255
Case Is > 500
MsgBox &quot;No Characters Left truncating data&quot;, vbCritical + vbOKOnly, &quot;Field Size Limit Approaching&quot;
Me!Label6.Caption = &quot;FULL&quot;
Me!Label6.ForeColor = 255
Me!textField.value = Left(FieldStr, 499) & &quot;#&quot;
End Select
End If

It's quite an interesting problem. The delete/backspace actions give different lengths of strings to inserting a character so you appear to go from having 2 spare chars to being full. I think you might need to do a bit of playing around with actions at 499 - 501 lengths.

I tried the validation rule but that only operates when you leave the field. Sandy