I have a database with a memo field. Sometimes the field holds more than 2,048 characters. On the edit form, the memo field is displayed with a text field.
Whenever there are more than 2,048 characters in the field, it appears that the user can still edit the field, but the changes aren't saved.
I've done the research and found that there's a 2,048 character limit for text boxes on forms, but nothing on what to do about it. Is there a magic setting I'm missing? I did see a little bit about using an OLE field, but I'm not very familiar with that, and if that's the route I have to take, I need it to be as seamless as possible for the users, because they are already accustomed to using the database for viewing data. Not to mention it'll be a lot of work for an otherwise simple database!
A Memo field can store up to 65,536 characters. If you want to store formatted text or long documents, you should create an OLE Object field instead of a Memo field.
In your table make sure the field to hold your converted SOP is a data type of memo. Text boxes have a limit of 255 characters.
Just another thought on the form used for editing is there coding similar to this that limits the field size set by the variable max_chars. I use this code in the On Change event of a memo to display a message when the length of the field exceeds maximum characters that the user can enter. It prevents the user from being verbose.
After the limit is reached a message is displayed and the length of the field is truncated to the maximum characters
max_chars = 750
If Len(.text) > max_chars Then
MsgBox strmsg, vbCritical + vbOKOnly, msg_title
.text = Left(.text, max_chars)
.SelStart = max_chars
Thanks. I will try the SQL Update if I can get it to work in the background. That sounds easier to do and like less of a change for the users. If it doesn't work, I'll figure out the OLE Object way.
The problem is that when you have a memo field, you can only use a text box on a form. There is no "memo box". Even though a text field holds only 255 characters, a text box will hold 2,048. A text box will display more than 2,048 characters, but won't let the user make any changes on the form. It's an Access bug.