Limit Long Text
By Richard Rost
4 years ago
Set a Maximum Size Limit for Long Text Fields
You can't set a maximum length for a long text field like you can for a short text field by setting the field size property. So in this Microsoft Access tutorial, I'll teach you how to use a validation rule to accomplish that task. Then, I'll show you a better method using the Before Update event and a tiny bit of VBA code.
Chloe from Eugene, Oregon (a Platinum Member) asks: My company prepares insurance claims. We have to submit a form to the insurance company outlining the details of a claim, however they have a maximum of 1000 characters. Is there any way to set a max limit on long text fields like you can with short text?
Members
Members will learn how to keep a running count of how many characters are remaining in a text box below your long text field. This way you can see the counter change each time you enter a character, and make it turn red when it's getting low. We'll learn about the Text and OldValue properties of a text box, the On Change event, and the On Undo events.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Course
Links
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, before update, beforeupdate, max field size for long text fields, memo, validation rule, on change event, change, .text property, text box .text, textbox.text property, On Undo event, OldValue
Intro In this video, I will show you how to set a maximum character limit for long text fields in your Microsoft Access database. We'll discuss common challenges when working with long text, compare simple methods like validation rules with more advanced solutions using VBA event procedures, and demonstrate how to provide immediate feedback to users entering data. You'll learn step-by-step how to prevent users from exceeding your character limits in Access forms and tables.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how you can set a maximum size limit for long text fields in your Microsoft Access Database.
Today's question comes from Chloe in Eugene, Oregon, one of my platinum members. Chloe says, my company prepares insurance claims. We have to submit a form to the insurance company outlining the details of a claim. However, they have a maximum of 100 characters. Is there any way to set a max limit on long text fields like you can with short text?
Well Chloe, I've never been asked this before. It's a very good question. Yes, there are a couple of different ways you can do it. The first way is really easy, but it doesn't give you very many options. The second way is a little bit better and gives you some more options. Then in the extended cut, I'll even show you how you can put a little countdown here. As the user is typing, it will update that count and even warn you when you only have so many characters left. We'll do that in the extended cut.
But first, I'll show you a couple simple ways just to limit the number of characters that are entered.
Now before we get started, if you don't know what validation rules are, go watch this video. I'll put a link to it down below. You can click on it and go watch this one. Also, go watch my string functions video. We need this to tell how long, how many characters are in a text string, a text field.
If you don't know how to use the length, LEN function, go watch this too. Then come on back.
This is my TechHelp free template. You can download a copy of this database on my website if you want to, but this will work with pretty much any database that you want.
Let's say I have a customer form here and in this long text field, this notes field, I can only put a max of a thousand characters. If you go to the table properties over here, design view for our short text fields like first name, we can set a field size down here. Pretty much the only reason you'd use that field size nowadays is to limit the amount of data that this text field can hold. So if you only want to hold a maximum of 10 characters, put a 10 in there.
But if you look at long text fields like notes, there is no field size. So what you can do instead of using field size is you can use the validation rule property. Go watch the validation rule video. The validation rule says when this record is entered, check it, make sure that whatever is in this field meets whatever criteria we set.
So what's the criteria we're going to set here? We're going to say that the notes field has to be less than, let's make it 10 characters for now just to keep it simple. I'm going to zoom in for you so you can see it better (Shift-F2). I'm going to say the length of notes has to be less than 10. I'll change it to a thousand for later, but for now we'll just keep it 10.
Now be careful, watch what happens. I'm making this mistake because I make it all the time and I know you are going to make it. This is one of those things that people always ask me, why isn't it working? Hit OK and look what happened. Can you see it? Let me zoom in again so you can see it clearly. Watch what happened (Shift-F2). Look at that. You see what happened? Access changed it just slightly. It put notes inside quotes.
This is a common thing that happens. In this particular case, you want to make sure you put notes inside brackets. We don't want the actual word notes there. We want the notes field.
I usually say you only have to put those brackets around field names if you use spaces or weird characters, but once in a while, you have to know to put those brackets in there because Access sometimes tries to change your functions for you. It's trying to be helpful, but in this case, it's not.
Now, the validation text can be whatever you want it to be. I'm going to turn this property sheet off here real quick. Validation text is, for example, "You entered too many characters" or whatever. You can be more helpful than that if you want to.
Save that. It's going to say the data integrity rules have changed. OK, fine. I'm going to hit cancel, which means it's just going to ignore whatever's in the table. We covered that in the other video.
So close this. Come over here to the customer form. Now, if I put in one, two, three, four, five characters, everything's fine.
Here's the interesting thing about the validation property: it doesn't actually check that until you leave the record or close the form. This is one of the reasons why I find the validation rule a little annoying, because I have more than 10 characters there, and it's not going to catch that. I can go and do more stuff, and it's not going to catch that until you either leave the record or close the form. Now it says, you added too many characters.
You should be a little better here with the rule, maybe say, your notes field is too long or something like that. But still, that's kind of annoying.
Do you want to close the data? No, stop. Don't do anything.
Can we come back in here and change this? Now it lets me do it. So as you can see, that's OK. That's one way you could do it. But again, you have to wait until you leave the record or you close the form, and that's annoying. I don't like the validation rule for that particular instance. It's good for some things, but not this. I want a message right away as soon as the user tries to leave that field.
How can we do that? Let's get rid of that validation rule first of all. Where are you? Delete. Delete.
Now, for this next step, that will work for you. If you don't want to do any programming, that'll work. It's not my favorite solution, but it will work.
Now for this next thing, we need a couple of lines of code. Don't let VBA scare you if you've never done any programming before. Go watch this. My intro to VBA is free. It's on my website, it's on my YouTube channel. It's about 20 minutes long and teaches you all the basics. Go watch this and then come on back.
Oh, and then go watch this one, "After Update." What we're going to do is use an event called "Before Update," which is slightly different from "After Update," but "After Update" is easier to master first, so go watch this after you watch "Intro to VBA."
Watch the "After Update and DLookup" video. This is a really good video, one of my favorite ones. Then come on back.
Once you have watched VBA and you watched "After Update," now we can do something. After this is updated, we can run the "After Update" event and tell you how many characters are in there. Let's see how that works.
Design view. Go into the notes property here. Go to the event tab. Go to "After Update" ... (dot, dot, dot). That will bring up our code builder.
Now in the "After Update" event, just to see what's in there, let's say:
MessageBox "There are " & LEN(notes) & " characters."
That is in the "After Update" event. So after that's updated, I want to see how many characters there are.
Save that. Control-S. Back over here. Let's close it down. Open it up.
Let's type in a couple more things. I'm going to type in one, two, three, four, five and then hit Tab. There are five characters. Good.
Six, seven, eight, nine, ten, eleven, twelve. Let's see what we got now. There are twelve characters. Very good.
The problem with "After Update" is by the time "After Update" runs, it's too late to change things. You can undo that, but now that's too much work. What you want is an event that runs before that change is committed, so Access can look at that value and go, wait a minute, you have too many characters here, or, wait a minute, this value is too low or whatever. That's what the "Before Update" event does. "After Update" is after you make the change. It's too late. It's being saved. But "Before Update" happens before that change is committed to the table.
Let me hit Escape here. Let's go back to our code. Let's get rid of this "After Update." You can get to this from going right in the code window, but I don't like to confuse people who are just learning this stuff.
Let's go back to events. Go to "Before Update" this time ... (dot, dot, dot). Now we are in the notes "Before Update."
Look right here. This is one of the major differences between "After Update" and "Before Update." "Before Update" can be canceled. You can say, oh, this doesn't meet my criteria, cancel it.
Right in here, we're going to say a little If...Then. If you have never written an If...Then statement before, go watch this. I'll put a link to this down below too.
We're going to say:
If LEN(notes) > 10 Then
Cancel = True
MessageBox "You entered too many characters."
End If
There's no reason to do an Exit Sub or any other stuff in there. It's going to check this. If you have typed in more than 10 characters, it will cancel the event, give you a message box, exit out, and then leave you sitting there in that field. You can't leave it until you either fix this or hit Escape to cancel it.
Back here again, close everything. Always close everything down. Did they change? Yes.
Open it back up again. I have one, two, three, four, five in there. I'll hit Tab. Everything's good.
Six, seven, eight, nine, ten, eleven, twelve, Tab. You entered too many characters. Right away, immediately. Hit OK.
Maybe put a couple more in. Tab. Too many. You can put the count in there too: "You entered five too many characters," or whatever you want.
I have to back it up now until maybe seven, hit Tab, and now I'm good. The alternative is if you have too many in there, you can hit Tab, and if you don't want to fix it, you can just hit Escape, and it goes back to where it was. Basically, it cancels it.
There you go. There's the simple method with a validation rule. There's the better method with the "Before Update" event.
Now in the extended cut for the members, we're going to do a whole other thing. We're going to go to a whole other level. We're going to make a box down here with an active count as you type. If you get close to being almost too many characters, it will give you a warning. If you go over, it will give you a warning, but it will still let you type.
I find it annoying on some web forms on the internet when you are typing and typing and typing and it hits the max and just stops. I think that's annoying. Sometimes I want to finish my thought and then go back and edit it, maybe abbreviate some words or take some spacing out somewhere else.
We're going to let the user type and then just say, "Hey, you're over by 12 characters. Go into your text somewhere and find 12 characters to remove." Here's my database that I built. I'm going to type in. I've only got seven characters left. Now we're over. As I'm typing, I have to go back and get rid of something. Still nine characters, come down here, maybe find something else, get rid of that, and there we go.
One thing I just noticed, if you have one extra character, you're over by zero characters. I'm going to fix that. We'll fix that in the extended cut. We'll make it say "zero," or "perfect."
There you go. You have exactly a thousand characters, and if you type more, oh, you're over. Now it's working well.
Now we can do this in the extended cut. If you want to see how that works, sign up now. Join now if you're not a member already. It's what, six bucks a month, folks. That's like the cost of a Starbucks. I love me some Starbucks. You can send me some Starbucks if you want.
That's covered in the extended cut. We're going to do a text box showing the live count, warning when it gets low. We're going to learn a bunch of new stuff that I haven't covered - I don't think I've covered these in extended cut videos before. I've covered these in my developer courses.
There's a text property of text boxes that we have to learn. This seems like it's really easy, but there are like five or six different weird events that happen as you type. We're going to learn about something called the old value property, the value that a box used to have in it. We're going to do the on change event and the undo event, that is, what happens when the user hits Escape. Weird stuff happens.
This is all covered in the extended cut. Silver members and up get access to all of my extended cut videos. There are a lot of them now. I don't even know how many. I lost count. Gold members can download these databases, and you can access the code vault. Platinum members - well, you platinum members are my best friends. We have tea every other Sunday. I'm just kidding. But check it out.
If you have any questions, post them down below in the comments section. We'll see you next time. Hope you learned something today.
How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me. You'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access, but also for Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are a sponsor. You'll get a shout out in the video, and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. Why might you want to set a maximum character limit on a long text field in Microsoft Access?
A. To comply with external submission requirements that limit field size
B. To save money on database licensing
C. To prevent users from entering text
D. To automatically summarize text
Q2. In Access, which field type allows you to directly set a Field Size property in table design view?
A. Short Text
B. Long Text
C. Number
D. Date/Time
Q3. What property can you use to restrict the length of input in a Long Text field?
A. Validation Rule
B. Input Mask
C. Formatting
D. Default Value
Q4. What function in Access can be used to determine the number of characters in a text field?
A. LEN()
B. SUM()
C. COUNT()
D. LEFT()
Q5. When setting a validation rule for a field in Access, what should you use around the field name in the rule?
A. Brackets []
B. Curly braces {}
C. Parentheses ()
D. Quotation marks ""
Q6. What is a limitation of using the Validation Rule property for restricting input length on forms?
A. The validation only checks after the user leaves the record or closes the form
B. The validation prevents any input
C. The validation deletes the record
D. The validation does not display any error messages
Q7. Which Access form event allows you to prevent saving a record if criteria are not met?
A. Before Update
B. After Update
C. On Click
D. On Open
Q8. What VBA statement would you use to cancel an update in the Before Update event if a field exceeds the allowed length?
A. Cancel = True
B. Exit Sub
C. Me.Undo
D. Stop
Q9. Why is the "After Update" event not ideal for restricting text length in a field?
A. By the time it runs, changes are already committed to the table
B. It cannot display messages
C. It only works on short text fields
D. It deletes the field contents
Q10. In the extended cut, what added feature is demonstrated to help users stay within the character limit?
A. A live countdown showing characters left as the user types
B. Auto-correcting text
C. Coloring the background of the form
D. Disabling the form entirely
Q11. Which membership level grants access to all extended cut TechHelp videos and more advanced classes?
A. Silver
B. Bronze
C. Guest
D. Copper
Q12. What is NOT required to use the validation rule method to limit long text field length?
A. Writing VBA code
B. Using the LEN function
C. Setting the validation rule property
D. Providing a validation text
Q13. What can a user do if they try to leave a field with too many characters and the Before Update event cancels the action?
A. Edit the text to reduce its length or press Escape to cancel
B. Access will automatically fix the text
C. The form will close by itself
D. The database will be deleted
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone shows how to set a maximum character limit for long text fields in Microsoft Access. Chloe, who works for a company that processes insurance claims, asked if there is a way to restrict the size of long text fields just like you can with short text fields, since insurance forms only permit 100 characters of detail.
This is actually an interesting scenario and something I have not been asked about before. There are a couple of ways to handle this. The first solution is simple but somewhat limited in its flexibility. The second method provides greater options for feedback and control. In the extended cut of this video, I also demonstrate how to create a live countdown for characters as the user types, which gives more dynamic feedback and alerts when approaching or exceeding the limit.
To start, let me briefly explain validation rules in Access. If you are not familiar with validation rules, you should learn about them first, since that is essential to this process. Validation rules allow you to define criteria so that when a record is added or edited, Access checks to make sure the inputs match your requirements. For short text fields, there is a straightforward field size property where you can specify the maximum number of characters. For example, if you only want to allow 10 characters, you set the field size to 10.
However, long text fields do not have a field size property. Instead, you have to use the validation rule property on the table. You can enter a rule that checks the length of the field using the LEN function. For example, you might require the length of the notes field to be less than 10 characters just for the sake of demonstration. Inputting this rule, however, has a quirk: Access may automatically put the field name inside quotes. This is not what you want. The correct approach is to use brackets around the field name (for example, [notes]) to indicate that you are referring to the field itself and not the literal word "notes."
You can also define custom error messages for your validation text property if the rule is broken, such as "You entered too many characters." Once this is set, Access checks the rule when the user attempts to leave the record or close the form. However, this is not always ideal because the feedback is not immediate; it alerts the user only after they try to leave the record rather than right when they finish typing in the field. This could be frustrating if more immediate validation is preferred.
If you want to provide more immediate feedback, you can use VBA to check the field as the user tries to exit from it. I recommend learning some VBA basics if you have not already, as this unlocks a lot more flexibility in Access. First, I demonstrate using the "After Update" event, which triggers after a control is updated, to display a message box telling the user how many characters they have entered. While this works, it is too late to stop invalid data because the change is already committed by the time "After Update" runs.
For more proactive validation, the "Before Update" event is preferable. This event fires just before the data is saved, which allows you to cancel the update if the value does not meet your criteria. You can check the length of the notes field using an If statement inside this event, and if the limit is exceeded, present a message to the user and cancel the operation, keeping them in the field until they fix their input. This method is much more user-friendly because it provides instant feedback as soon as the user tries to leave the field.
For even more advanced interaction, such as displaying a live character count and alerts as you approach the character limit, these features are covered in the extended cut for members. I do not simply cut off input once the limit is reached, as some web forms do, since it can be more helpful to let the user know by how many characters they have gone over so they can edit and revise their text appropriately. In this more involved setup, we use an additional text box to display a live character count and provide warnings as needed. I also go into some lesser-known properties and events, such as the text property of text boxes, the difference between various update events, how to respond when the user presses Escape, and other advanced VBA techniques.
Membership options are available if you want to access this and many other extended cut videos, along with downloadable databases, code vault access, and priority question handling at different levels. There is a membership plan for everyone, depending on how much Access content you wish to unlock and whether you want sample databases and advanced courses as well.
If you have questions about anything covered here, feel free to leave them in the comments section. To recap, today I demonstrated how to control the maximum size of long text fields in Access using simple validation rules, then VBA event handling for immediate feedback, and mentioned how to set up a live character counter in the extended version.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Setting a max limit for long text fields in Access
Using the Validation Rule property for text limits
Configuring Validation Text for user feedback
Limitations of validation rules with long text fields
Using VBA to check text length in the After Update event
Benefits and drawbacks of After Update event handling
Switching to the Before Update event for immediate validation
Writing an If...Then block to enforce text length in VBA
Cancelling updates with the Before Update event if invalid
Providing instant user feedback for too many characters |
