memo fields, 2048 characters, unable to update

[303factory]

Hi

We've got a rather strange problem with our access database, we're getting the following error while trying to udpate the text in a memo field

'Could not save; currently locked by another user'

This occurs if only one person is using the database. Performing a compact/repair doenst fix the error.

This seems to be dependant on the amount of text in the field. If you cut half of it out from the table manually it works next time. I've looked around on the internet and found reference to problems with memos where you are adding text so the amount of characters exceeds 2048. For example:

http://groups.google.com/group/comp...hread/6093c4f4b8c931f9/88987d8e59b63db5?pli=1

However the solutions ive read dont apply to us. the problem usually occurs with a sql back end or when updating from a bound form. We're updating the field from code

Public Function AddExhibitNote(strNote As String)
    Set tempExhibitRS = New ADODB.Recordset
    strSQL = "SELECT * FROM tblExhibitInfo WHERE UniqueRef =" & gUniqueRef
    tempExhibitRS.Open strSQL, gMIDdbase, adOpenKeyset, adLockOptimistic, adCmdText
   
    tempExhibitRS.Fields("ExhibitNotes")  = tempExhibitRS.Fields("ExhibitNotes") & vbNewLine & Now & " - " & gUser & " - " & strNote
  
    tempExhibitRS.Update
    tempExhibitRS.Close
    set tempExhibitRS = nothing
End Function

A form is open linked to the same table (although the ExhibitNotes field is not bound to the form) and the form is refreshed and requiried before the code update is attempted so dont think that's causing a conflict.

A forum implied clearing the field and adding all the data again fixes the problem but can't work out how to do this by code (setting the field to "" causes the same error). Does anyone have any ideas?

 

[gemma-the-husky]

is this a sql backend - i think a sql memo field has a max of 2048 chars, so this might be causing a spurious error message

the way you enter data into the memo field may cause your error - if you popup a data entry window, to recieve the memo, then if you have already edited/dirtied the record vefore opening the popup, access cant save the memo because "another user has edited the record" - ie you, on the main form.

if this is what is causing the problem, then you could save the record BEFORE opening the popup.

 

[303factory]

is this a sql backend - i think a sql memo field has a max of 2048 chars, so this might be causing a spurious error message

the way you enter data into the memo field may cause your error - if you popup a data entry window, to recieve the memo, then if you have already edited/dirtied the record vefore opening the popup, access cant save the memo because "another user has edited the record" - ie you, on the main form.

if this is what is causing the problem, then you could save the record BEFORE opening the popup.

It's an access back end, so the memo field should be able to hold a lot more than 2048 characters, hence im a bit confused as to why the code works fine for small amounts of text, but doestn work for larger amounts of text.
Also the memo field is not displayed on screen or bound to any form. The user will type some text into an empty unbound text box and click an 'add note' button which then passes the line of text to the function above, which adds the new line of text to the memo field. So as far as I can tell the record cant have been dirtied..

 

[303factory]

Interesting update:

There is a subform open that is linked to the same table (although the field I am trying to update is not bound to this form).

If I set its visibility to false before I try to update the table by code, the problem, goes away!

So I have

Me.subformExhibitInfo.Visible = False
AddExhibitNote(Me.AddNote)
Me.subformExhibitInfo.Visible = True

It's not ideal but a workaround, perhaps this could shed some light on where im going wrong?

 

[RuralGuy]

Are you using queries or binding directly to tables? You should be using queries.

 

[303factory]

Are you using queries or binding directly to tables? You should be using queries.

Aaaah the main form was linked to a query but the subform was linked directly to the table. Changed this and its working now wihtout making the subform invisible! thanks. Not sure why it was dependant on the amount of data in the field, but I'm beyond caring now

 

[RuralGuy]

Glad I could help.