How to work around Access VBA error 3188

2
votes

Having converted a number of fields in a table tblSource to Rich Text memos, I'm getting an error 3188 in the following circumstances.

Main form has a subform open (frmSource) bound to qrySource. qrySource pulls in some fields from tblSource and adds a calculated field which concatenates the (newly-minted) rich-text memo fields (SD1 to SD20) so that the result can be displayed in a single text box on frmSource called Citation.

If the user wishes to edit SD1 to SD20, they double-click on the Citation field and a modal form frmCitation opens up displaying the SD fields for editing. frmCitation is bound to qryCitation which pulls the SD fields and a couple of others out of tblSource. When finished, they close frmCitation. When SD1 etc were text fields, the tblSource record was updated successfully. However, now they're memo fields, I'm hitting VBA error 3188 ("Could not update; currently locked by another session on this machine.").

Searching on the Internet suggests that this is a common issue with Rich Text memos when a memo size exceeds 2k (limitation possibly due to Access edit buffer size?), so I'm looking for ways to work around it.

One option would be be to split tblSource into two tables tblSource and tblCItation with a one-to-one relationship between them, then base qrySource on tblSource and qryCitation on tblCitation, but that's fairly major surgery with knock-on effects in a number of other places in the application.

Another option is to limit the size of all the memo fields on this form (as per Rich text input into limited length text field in Access 2010), but there's one field for which that wouldn't be acceptable to the users.

Is there another technique I could explore?

1
vbams-accessms-access-2010
Are you sure that the base form does NOT have a dirty record that you are launching from? Before you launch that edit form, place a me.Refresh right before the command that opens that form – any existing pending writes in the current form will thus be flushed. - Albert D. Kallal
@Albert Already in place -- you can't open the modal form if the base form is dirty. - ColeValleyGirl
I suppose you could use a un-bound text box. In the forms on-load event you pull the data from the memo using a recordset and then on form close/save you take the text and update the table via that recordset. As noted, I would try this with row locking turned off and see if that helps. Unfortunately, I never encountered this error. - Albert D. Kallal

1 Answers

0
votes

Per the following MS Link: http://answers.microsoft.com/en-us/office/forum/office_2007-access/memo-field-could-not-update-currently-locked-by/d5c8163a-7ce5-484f-80d4-98c1a8c92160

near the bottom, they suggest the limit is 2,000 characters. Not that it helps, but could you add code to the 'before update' to display what the new size should be wnen concatenated? May help to figure a solution...