Can you set the MaxLength property or add a character limit to an Access Long Text field?

Question

I have an Access form I've created for users. The form is connected to an Access table, not a Linked Table, because multiple users will need to update this form and the connected table at once and I continued running into concurrency errors when using a linked table. The connected table had a variety of short text fields and I added a character limit where needed.

Users are starting to run over the allotted record size (because we have about 30 fields) so they'll get the "Record Too Large" error and it will lock their record and I'll have to fix the entire Access database, since it won't let me delete or amend the locked record. To get around this I've made some of the textboxes Long Text boxes. However, I still need to set a character limit because their entries into the form and connected table get sent to dashboards that have a set amount of characters that will fit the dashboard.

I've already added code to show users their character count and limit (i.e. 230/250 characters used). But, since I've changed the textboxes to Long Text, I can no longer set a character limit as I could with short text boxes. I've tried to add the MaxLength property to the long text fields but it does not work.

Does anyone know how I can add a character limit to a Long Text field without changing it to a short text field?

Thank you!

Answer 1

You can do the input limit in your Form. Using the Form.BeforeUpdate event you can capture input before it is written to the database. Here you can cancel the update and present the user with an error message about why.

30 Fields is too long for one table. You need to research database normalization. Breaking the tables down in to smaller logical objects has many improvements, one of which is that you won't have these problems.

Answer 2

If you want to set the limit at the table level you can use a "Before Change" data macro:

Answer 3

Why use Long text if your responses are limited to 250 chars ---that's the max length of short text? I agree that 30 fields is quite large for a single table --not unheard of, but rare. A review of your database structure may identify hidden entities,so normalization may be in order.