How to Limit Memo to 1500 Characters

[ms_user]

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

 

[Clifford Bass]

Hi,

Yes, validation rule is best; it is not dependent on where data comes
from.

You could use "Is Null or Len([My_Memo_Field]) <= 1500" for the
condition.

Clifford Bass

 

[fredg]

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

You don't write code? Perhaps you should learn.
A simple solution? Write code! It's no big deal.

In Design view, select the memo field.
Display it's property sheet.
Select the Event tab.
On the Change line write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

If Len(Me!MemoName.Text) >1500 then
MsgBox "Only 1500 characters are allowed."
Me!MemoName.Text = Left(Me!MemoName.Text,1500)
End If

Change [MemoName] to whatever the actual name of your form control is.

Save the code.

When the user enters the 1501 character, the message will appear and
the text will be rolled back to 1500 characters.

Now if you wish to use a Validation rule you can.
Set the Validation Rule in the table to:

Len([MemoFieldName])<= 1500

Write a Validation Text message.

The problem is the user will be able to write much more than 1500
characters and won't know until he exits the field at which time the
data will not be saved and he will then have to figure out where, in
the text, the 1500th character is.

Good luck.

 

[ms_user]

fredg,

Thanks for the terrific explanation and code. I tried it and I got it to
work.

One follow up question. When I type over the limit, I get the following
message:

Run-time error '438':
Object doesn't support this property or method

Can I customize this text somehow to let the user know exactly what the
error is? Something like "Please enter less than 1500 characters."

Thanks again for your help!

Dan

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

You don't write code? Perhaps you should learn.
A simple solution? Write code! It's no big deal.

In Design view, select the memo field.
Display it's property sheet.
Select the Event tab.
On the Change line write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

If Len(Me!MemoName.Text) >1500 then
MsgBox "Only 1500 characters are allowed."
Me!MemoName.Text = Left(Me!MemoName.Text,1500)
End If

Change [MemoName] to whatever the actual name of your form control is.

Save the code.

When the user enters the 1501 character, the message will appear and
the text will be rolled back to 1500 characters.

Now if you wish to use a Validation rule you can.
Set the Validation Rule in the table to:

Len([MemoFieldName])<= 1500

Write a Validation Text message.

The problem is the user will be able to write much more than 1500
characters and won't know until he exits the field at which time the
data will not be saved and he will then have to figure out where, in
the text, the 1500th character is.

Good luck.

 

[ms_user]

Clifford,

Thanks for the help. I used your rule and got it to work. However, as fredg
mentioned I don't know about the limit until after I'm done typing. I think
for my purposes fredg's method may work better. However, I don't know how to
customize the error message using his method. Any ideas?

Thanks,

Dan

Hi,

Yes, validation rule is best; it is not dependent on where data comes
from.

You could use "Is Null or Len([My_Memo_Field]) <= 1500" for the
condition.

Clifford Bass

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

 

[fredg]

fredg,

Thanks for the terrific explanation and code. I tried it and I got it to
work.

One follow up question. When I type over the limit, I get the following
message:

Run-time error '438':
Object doesn't support this property or method

Can I customize this text somehow to let the user know exactly what the
error is? Something like "Please enter less than 1500 characters."

Thanks again for your help!

Dan

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

You don't write code? Perhaps you should learn.
A simple solution? Write code! It's no big deal.

In Design view, select the memo field.
Display it's property sheet.
Select the Event tab.
On the Change line write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

If Len(Me!MemoName.Text) >1500 then
MsgBox "Only 1500 characters are allowed."
Me!MemoName.Text = Left(Me!MemoName.Text,1500)
End If

Change [MemoName] to whatever the actual name of your form control is.

Save the code.

When the user enters the 1501 character, the message will appear and
the text will be rolled back to 1500 characters.

Now if you wish to use a Validation rule you can.
Set the Validation Rule in the table to:

Len([MemoFieldName])<= 1500

Write a Validation Text message.

The problem is the user will be able to write much more than 1500
characters and won't know until he exits the field at which time the
data will not be saved and he will then have to figure out where, in
the text, the 1500th character is.

Good luck.

Did you remove the Validation Rule that you mentioned in your reply to
Clifford Bass. You don't need both a Validation Rule and my code.

If you wrote the code as I gave you, then you should get the message
you want, i.e. MsgBox "Only 1500 characters are allowed."
Evidently you have written it incorrectly, or you have something else
going on that I don't know about.
Copy and Paste your exact code into a reply so I can see it.

i noticed that you have also posted the same original question to
another newsgroup.
Please do not multi-post. If you feel you must post the same question
to more than one newsgroup (and it's seldom necessary), crosspost by
adding each additional newsgroup in the To Newsgroups: box, separated
by a comma.
This way an answer in one newsgroup will be seen in each of the
others. More readers will see the response and learn, and less time
will be spent on duplicate answers.

See Netiquette at http://www.mvps.org/access

It's a great site to visit anyway.

 

[ms_user]

fredg,

I see the error message in there now. Maybe I didn't see it because I didn't
save at first.

Thanks again!

fredg,

Thanks for the terrific explanation and code. I tried it and I got it to
work.

One follow up question. When I type over the limit, I get the following
message:

Run-time error '438':
Object doesn't support this property or method

Can I customize this text somehow to let the user know exactly what the
error is? Something like "Please enter less than 1500 characters."

Thanks again for your help!

Dan

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

You don't write code? Perhaps you should learn.
A simple solution? Write code! It's no big deal.

In Design view, select the memo field.
Display it's property sheet.
Select the Event tab.
On the Change line write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

If Len(Me!MemoName.Text) >1500 then
MsgBox "Only 1500 characters are allowed."
Me!MemoName.Text = Left(Me!MemoName.Text,1500)
End If

Change [MemoName] to whatever the actual name of your form control is.

Save the code.

When the user enters the 1501 character, the message will appear and
the text will be rolled back to 1500 characters.

Now if you wish to use a Validation rule you can.
Set the Validation Rule in the table to:

Len([MemoFieldName])<= 1500

Write a Validation Text message.

The problem is the user will be able to write much more than 1500
characters and won't know until he exits the field at which time the
data will not be saved and he will then have to figure out where, in
the text, the 1500th character is.

Good luck.

 

[MikeJohnB]

A nice way of telling your users how much text they have left to type is to
add a text counter unbound control.

This is a text box, lets call it Text10 (to find the name of the textbox,
select its properties in design view and look at the ALL or Other Tab at the
top of the properties box. This should show Name ....... (what is in the row
of that is the name of your textbox))

In the On Change Event of the Memocontrol, select properties in design view
and then code builder as you did for the other code and enter the following
code

Me.Text10 = 1500 - Len(Me.MemoName.text)

You can set the title of the unbound text box to something like

Characters Left to Type

with the textbox following the word type, you will get a countdown of how
many characters there are left in the memo field from the original 1500 limit.

If you want to do this and I havent explained clearly, I am sure fredg will
be able to guide you through it. He's the man.

regards

Mike B


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

fredg,

I see the error message in there now. Maybe I didn't see it because I didn't
save at first.

Thanks again!

fredg,

Thanks for the terrific explanation and code. I tried it and I got it to
work.

One follow up question. When I type over the limit, I get the following
message:

Run-time error '438':
Object doesn't support this property or method

Can I customize this text somehow to let the user know exactly what the
error is? Something like "Please enter less than 1500 characters."

Thanks again for your help!

Dan

On Fri, 7 Nov 2008 09:22:04 -0800, ms_user wrote:

I am a new user looking for a simple solution (I don't write code) to limt a
memo field to 1500 characters. Is there a way to do this using the Validation
Rule?

You don't write code? Perhaps you should learn.
A simple solution? Write code! It's no big deal.

In Design view, select the memo field.
Display it's property sheet.
Select the Event tab.
On the Change line write:
[Event Procedure]
Then click on the little button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

If Len(Me!MemoName.Text) >1500 then
MsgBox "Only 1500 characters are allowed."
Me!MemoName.Text = Left(Me!MemoName.Text,1500)
End If

Change [MemoName] to whatever the actual name of your form control is.

Save the code.

When the user enters the 1501 character, the message will appear and
the text will be rolled back to 1500 characters.

Now if you wish to use a Validation rule you can.
Set the Validation Rule in the table to:

Len([MemoFieldName])<= 1500

Write a Validation Text message.

The problem is the user will be able to write much more than 1500
characters and won't know until he exits the field at which time the
data will not be saved and he will then have to figure out where, in
the text, the 1500th character is.

Good luck.

 

[Clifford Bass]

Hi Dan,

The other's advice is good. However, if there is any chance that you
may have data coming into the table in some other way such as through an
import, keeping the validation rule would be a good idea.

Clifford Bass