Long memo text greater than 2000 characters won't update (1 Viewer)

Danick · Aug 25, 2018

I have a split 2010 database that am having a problem updating a memo field when there is more than 2032 characters in the field. If I try to edit the field, I get an error message, "Could not update; currently locked by another session on this machine." The probem I am having is explained here.

https://social.msdn.microsoft.com/F...48/long-memo-text-wont-update?forum=accessdev

For convenience, I've copied the OPs discrepancy here which is exactly the same thing I'm experiencing.

However the OP fixed the problem by using an unbound popup form. I may end up doing that, but wanted to check here first to see if anyone knew of another solution since this problem was posted over 4 years ago and I can't believe that Micorsoft hasn't addressed it with an update or something.

Any input would be appreciated before I go and re-do to an unbound form and VBA to copy the text back to the main form on close.

Something is happening with memo fields when the length is greater than 2032 characters. Changes to these longer texts won't save in certain circumstances.

This is Access 2010. I have a memo field displayed on a crowded form, so it's a small display with a scroll bar. I allow the user to double-click the field to access a pop-up form that gives them lots of space to view and edit the text. The pop-up is a bound form, so it is saving as it goes.

This all works fine in most cases, but fails in one circumstance: if they have entered text longer than 2032 characters (I've done a lot of picky testing!), and then go back to that field to edit using the pop-up, the edits are lost.

I can scroll down in the original form (that tiny space) and edit the field and it's fine.

I can take a shorter text and use the pop-up to add text that brings it to more than 2032 characters and it's fine.

However, if they have already entered 2032 characters, and then I go back to that field and open the pop-up, any edits I make in the pop-up (either to add or delete text) are not saved when I return to the main field.

June7 · Aug 25, 2018

Still a problem in 2017 https://stackoverflow.com/questions/44028144/ms-access-memo-field-truncated

missinglinq · Aug 26, 2018

Unsure about the apparent 2032 character thing...but if you have two Forms open, both Bound to the same Table, and try entering data in the second Form that was open...you should always get this error!

There are a couple of things you can do, without using a separate Form, to address having a larger view of a given Control, in this case a Memo Field.

Simply using the Hotkey <Shift> + <F2> will zoom the Control up bigger. The code

DoCmd.RunCommand acCmdZoomBox

will also do this. You can use it when the Control gets Focus, if it has data in it:

Code:

Private Sub YourControl_GotFocus() If Nz(Me.YourControl,"")<>"" Then DoCmd.RunCommand acCmdZoomBox End If End Sub

If you want the text box to zoom for initial data entry as well later viewing, simply drop the test for data:

Code:

Private Sub YourControl_GotFocus() DoCmd.RunCommand acCmdZoomBox End Sub

If you want more control over the size and location of the Zoom Box, you'll have to 'roll you own!' Note that the code below only works in or Single View Forms:

A fairly simple way to roll your own Zoombox is to place a large Textbox (call it MyZoomBox) on your Form. Format it as you like, position as you like and assign it the same Control Source as the Field you want to expand. Yes, a Form can have two Textboxes with the same Control Source!

Substitute the actual name of your Control to be expanded for YourTextBox.

Code:

Private Sub Form_Load() 'Make the Zoombox invisible on loading the form MyZoomBox.Visible = False End Sub

Code:

Private Sub YourTextBox_DblClick(Cancel As Integer) 'When you double click the field, make the MyZoomBox 'visible and move the cursor to the beginning to 'de-select the text MyZoomBox.Visible = True MyZoomBox.SetFocus MyZoomBox.SelStart = 0 End Sub

Code:

Private Sub MyZoomBox_DblClick(Cancel As Integer) 'Double click the MyZoomBox to close it and 'return to your original field Me.YourTextBox.SetFocus MyZoomBox.Visible = False End Sub

Now make the Zoombox visible by double-clicking the Control you want expanded. Do whatever you want to the data, then double-click the Zoombox to close it.

Linq ;0)>

arnelgp · Aug 26, 2018

it is still true about the problem if you are using an Sql to update the fiekd. use recordset instead.

June7 · Aug 26, 2018

...but if you have two Forms open, both Bound to the same Table, and try entering data in the second Form that was open...you should always get this error!

It is possible to have multiple forms open to same data. Issue arises when initiate edit in one then try to edit same record on another before first is saved.

Danick · Aug 31, 2018

arnelgp said:
it is still true about the problem if you are using an Sql to update the fiekd. use recordset instead.

I'm not sure what this means. How can I tell which way I'm using to update the field.

Basically, I have a form with a note field. I can update the field directly in that form and will never have a problem. But I want to give the user more space, so I double click the note field and use this code to open a new form for data input to that field. (Shift - F2 also works but it looks terrible)

Code:

DoCmd.OpenForm "frmNewCall", , , "CallID=" & Me.[CallID]

The "frmNewCall" opens using OpenArgs to modify the notes field and requery the original form on closing this form. This works 100 % of the time except when you try to edit a note that has more than 2000 characters.

For missinglinq - Yes, I have used a zoombox for other forms/fields and have now started using it for this one as well. But I really would like to use the bound form since there are other locations in the database where the user can get to the same record / note field where a zoombox won't work because all that is available is the record ID and not the Note field itself. So I have to continue using the bound form for those events.

This would all go away if Microsoft would fix this 2032 character problem.
To illustrate the problem, I've attached a small database called LongNoteError. Use the frmNotes and follow the instructions in the first note

June7 · Aug 31, 2018

Do this test:

1. Open frmNotesSumnary

2. Double click second record to open frmNoteSingle

3. Go back to frmNotesSummary and select different record

4. Go back to frmNoteSingle, edit and close

Issue seems to be record locking. And unfortunately only with longer text data as you indicated. Do another test:

File > Options > Client Settings > Advanced > uncheck Open database by using record-level locking
Run Compact and Repair. I get mixed results. Sometimes I can edit multiple records, sometimes only one, sometimes none.

Sorry, don't see solution. I avoid memo/long text field as much as possible

isladogs · Aug 31, 2018

I just tested this in Access 2010 32-bit.

I believe I followed your instructions PRECISELY.
Created new record of approx 2700 characters
Moved to a different record to save it.
Returned to the record & edited it
Closed the form - NO ERROR!

If you are still having problems, 1 recommend you add the following line in Notes_AfterUpdate event to force a save:

Code:

If Me.Dirty Then Me.Dirty=False

BTW the ONLY time I've ever suffered a similar issue is when trying to use a very long URL with Internet Explorer.
In that case, the limit is 2083. Above that limit and IE can't handle it

HTH

June7 · Sep 1, 2018

@ridders, have to open frmNoteSingle by double click on frmNotesSummary.

Danick · Sep 4, 2018

ridders said:
I just tested this in Access 2010 32-bit.

I believe I followed your instructions PRECISELY.
Created new record of approx 2700 characters
Moved to a different record to save it.
Returned to the record & edited it
Closed the form - NO ERROR!

HTH

I forgot to mention in the instructions in the first note that to get the error, you have to double click the long record you want to edit.
I've added the lines in your post above.

Created new record of approx 2700 characters
Moved to a different record to save it.

*** Double click the long text record to edit it in the single form ****
Delete the first character and then close the form (closing the form will save the record)
Now you should get the error.
But if you double click that record again after receiving the error the first time, then it seems to work.
************

One more thing. You don't actually have to close the single form to save the record to produce the problem.

You could just doubleclick the long note (opens the single form)
make a change, and click the top left corner icon (pencil) to save the record. That may work the first time, but if you make another change and click the pencil again, you will get the error. And it won't matter if you leave that form open point to a different record on the "frmNotes" Form, you will still get the error if you try to save that record.

Danick · Sep 4, 2018

June7 said:
Do this test:

1. Open frmNotesSumnary

2. Double click second record to open frmNoteSingle

3. Go back to frmNotesSummary and select different record

4. Go back to frmNoteSingle, edit and close

Issue seems to be record locking. And unfortunately only with longer text data as you indicated. Do another test:

File > Options > Client Settings > Advanced > uncheck Open database by using record-level locking
Run Compact and Repair. I get mixed results. Sometimes I can edit multiple records, sometimes only one, sometimes none.

Sorry, don't see solution. I avoid memo/long text field as much as possible

Hi June,

I put the sample database using a form/subform which is the way my database was set up and having the problem. So you can't use "frmNotesSumnary" because it's missing the contactid field and would only produce an empty record when you come back from the single form.

In order to get the error, you have to use the main form "frmNotes". Then you will get this same error even if you go back to the "frmNotes" and select a different error before closing the single form.

But I've tried it a few times and noticed that sometimes it works and sometimes it doesn't. Very inconsistent.

Long memo text greater than 2000 characters won't update (1 Viewer)

Danick

Registered User.

June7

AWF VIP

missinglinq

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

June7

AWF VIP

Danick

Registered User.

Attachments

June7

AWF VIP

isladogs

MVP / VIP

June7

AWF VIP

Danick

Registered User.

Danick

Registered User.

Similar threads

Users who are viewing this thread