Solved: MS Access error "The text is too long to be edited"



In this article, we're going to look at how to resolve a common Text Box limitation issue on a MS Access form - the number of characters is too large to be added to the box, and as a result of this, the text can't be saved to a table.

Problem explained

Below is the error message that popped up automatically when I pasted a large amount of text into an Access Text Box control.

"The text is too long to be edited."

The root cause of this error is:

Normally a Text Box control is used for viewing, editing, or entering data via user interface (e.g. Access form). If used for entering data through a user interface, the data are normally typed or pasted to the Text Box control and then saved to a Short Text field or Long Text field (used to be called Memo field before 2013) in a table (please note other types of field also can be used). In this case, the following 2 scenarios can happen.

1. If data are to be saved to a Short Text field in a table, the maximum number of characters that should be typed or pasted to the Text Box control must be kept to less than or equal to 255. Therefore, you need to set up a data length validation manually either by using the Text Box control's Validation Rule property or by using VBA coding.

2. If data are to be saved to a Long Text field in a table, the maximum number of characters that can be typed or pasted to the Text Box control is 65,535. For this scenario, no need to do any data validation setup as MS Access automatically enforces the check and pops up error message "The text is too long to be edited" when the text is too long (i.e. more than 65,535 characters).

According to Microsoft, if data are not entered through user interface but entered programmatically via VBA, 1 gigabyte of characters can be saved to Long Text field.

Resolving the issue

Here we focus on resolving issue presented with the second scenario above. It's not uncommon we need to save large amount of text which is more than 65,535 characters to a Long Text field in a table. Our solution is to save the text to two (or more) Long Text fields in the table via VBA. In this case, we need to create two (or more) Text Box controls in a form, then split the text into two (or more) chunks. Text in the first Text Box control is saved to the first Long Text field and Text in the second Text Box control is saved to the second Long Text field. When the text is to be used, extract them from the two Long Text fields, then concatenate them into one single piece of text.

In my project, I had to manually save a web page's large amount of HTML source code to Access and in some cases the size could be over 65,535 characters (including spaces). I created the form below as a work around to bypass the 65,535 characters system limitation of Access.

Here is the table design of the HTML_SOURCE table.

Data in 'HTML Source Part 1' is saved to Source1 field and Data in 'HTML Source Part 2' is saved to Source2 field.

Happy Coding!


Copyright© GeeksEngine.com

Other Recent Articles from the MS Access category: 1. Examples of MS Access DateDiff function used in query and VBA code 2. MS Access DateDiff function 3. How to find out your computer name and username by VBA 4. Examples of MS Access DatePart function 5. MS Access DatePart function 6. Examples of MS Access DateAdd function 7. MS Access DateAdd function 8. IIF function basics - the CASE statement of MS Access 9. MS Access Date Expression 10. Create MS Access Combo Box essential properties by VBA code 11. Create MS Access Combo Box essential properties manually 12. How to do text search in MS Access programmatically 13. Solved - the size of the Access query result is larger than the maximum size of a database (2 GB) 14. How to easily get a list of field names in MS Access 15. How to count distinct records in MS Access 16. How to do transaction based processing in MS Access 17. How to open a document (local/network file or web page) from MS Access 18. How to use ADOX to create unique composite index - the VBA approach 19. How to do cross-table update queries in MS Access - the right way 20. Three efficient ways to get the number of records by using VBA 21. How to create a composite unique index (not as a primary key) in MS Access 22. Use VBA to get the correct number of records in a Recordset object 23. Disable Access Prompt when a record is changed, table deleted, or action queries run 24. How to hide and unhide a MS Access object 25. How to return multiple values from a VBA function (Part 3) 26. How to return multiple values from a VBA function (Part 2) 27. How to return multiple values from a VBA function (Part 1) 28. Three ways to programmatically duplicate a table in MS Access by VBA 29. Create a DLL by CSharp or VB.Net for VBA 30. How to correctly reference and call a DLL 31. How to register a C# or VB.Net DLL 32. Email address validation by Regular Expressions using VBA 33. Fix MS Access error: Query must have at least one destination field 34. How to unselect radio buttons in MS Access after it has been selected 35. How to Change Query Timeout Value for MS Access SQL Queries 36. What is Northwind Traders database