table update fails when memo field reaches 2 - 3K

[kscheu via AccessMonster.com]

I have a form with a recordsource set to a table. One of the fields in the
table is a memo field. I added a text box to the form and set its control
cource to be the memo field. This data entry form works perfectly until the
memo field reaches between 2 - 3K. The user will try to add more text in the
text box and when the form tries to .edit I get an error. "Action was
cancelled by an associated object"

Am I doing the edit/update incorrectly. I have a save button on the form
that does the following:
Forms![FrmCommentsUpdateAR].Recordset.Edit
Forms![FrmCommentsUpdateAR]!LastUpdated = Date
Forms![FrmCommentsUpdateAR]!LastUser = strCurrentUser
Forms![FrmCommentsUpdateAR]!Contactid = Nz(Me.cboContact, 0)
Forms![FrmCommentsUpdateAR].Recordset.UPDATE
DoCmd.RunCommand acCmdSaveRecord

I also tried the following: the error that I get is "Could not update,
currently locked by another session on this machine" Although, if I try to
update a row that has a memo field containing fewer charaters, the update is
successful.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Forms![FrmCommentsUpdateAR]!LastUpdated = Date
Forms![FrmCommentsUpdateAR]!LastUser = strCurrentUser
Forms![FrmCommentsUpdateAR]!Contactid = Nz(Me.cboContact, 0)
strnotes = Forms![FrmCommentsUpdateAR]!order_notes

End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

DoCmd.RunCommand acCmdSaveRecord

Exit_cmdSave_Click:
Exit Sub
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub


I know the text box on a form should handle 64 K, but there is some
limitation that is stopping this update. Does anyone have a solution to this
problem.

Thanks.

 

[John Vinson]

I have a form with a recordsource set to a table. One of the fields in the
table is a memo field. I added a text box to the form and set its control
cource to be the memo field. This data entry form works perfectly until the
memo field reaches between 2 - 3K. The user will try to add more text in the
text box and when the form tries to .edit I get an error. "Action was
cancelled by an associated object"

Am I doing the edit/update incorrectly. I have a save button on the form
that does the following:
Forms![FrmCommentsUpdateAR].Recordset.Edit
Forms![FrmCommentsUpdateAR]!LastUpdated = Date
Forms![FrmCommentsUpdateAR]!LastUser = strCurrentUser
Forms![FrmCommentsUpdateAR]!Contactid = Nz(Me.cboContact, 0)
Forms![FrmCommentsUpdateAR].Recordset.UPDATE
DoCmd.RunCommand acCmdSaveRecord

I also tried the following: the error that I get is "Could not update,
currently locked by another session on this machine" Although, if I try to
update a row that has a memo field containing fewer charaters, the update is
successful.

Sounds like you're getting your belt tangled with your suspenders! If
you have a bound form (with the fields bound to form controls) then
you do not need - and should not use! - the code above. Is your form
in fact unbound (i.e. doing it the hard way)?

I think the "other session" is your bound form. Access sees both the
form *and* your code attempting to update the same record at the same
time, and objects.

John W. Vinson[MVP]

 

[kscheu via AccessMonster.com]

Hi John,

My form is bound and I now realize that I do not need to update thru code in
order for the changes to the form to save to the table. I am still having
memo field problems, though.

I open the form that is bound to a table and edit the text control that is
bound to a memo field in the table. I add lots of text and simply close the
form and the memo field is saved fine. I keep doing this and checking the
length of the memo field. When the length of the memo field hits 2,040 I get
a message "Counld not update; currently locked by another sesison on this
machine" When the length of the memo field was 2,034 I was able to save
with no errors. This is the problem that I have been having. I think that
the unnecessary update/save that I was doing was not causing this error since
it worked fine until the memo text hit between 2 & 3 K. Have you ever seen
this problem. As I said, the test that I explained above was simply editing
a form and closing the form. No code involved.

Thanks,
Karen

I have a form with a recordsource set to a table. One of the fields in the
table is a memo field. I added a text box to the form and set its control

[quoted text clipped - 16 lines]

update a row that has a memo field containing fewer charaters, the update is
successful.

Sounds like you're getting your belt tangled with your suspenders! If
you have a bound form (with the fields bound to form controls) then
you do not need - and should not use! - the code above. Is your form
in fact unbound (i.e. doing it the hard way)?

I think the "other session" is your bound form. Access sees both the
form *and* your code attempting to update the same record at the same
time, and objects.

John W. Vinson[MVP]

 

[John Vinson]

Have you ever seen this problem.

I'm afraid not! I'll bring this thread to the attention of the other
MVP's, perhaps someone else has.

John W. Vinson[MVP]

 

[kscheu via AccessMonster.com]

Thanks. I would appreciate that very much.

I'm afraid not! I'll bring this thread to the attention of the other
MVP's, perhaps someone else has.

John W. Vinson[MVP]