ERROR 3188 and Memo Fields

[RezaE]

I have been searching the net for the past 2 weeks trying to come up
with a solution for this and so far I have found other people with
the same problem but no solution. So for all the Access enthusiast,
here it goes;



I have a form that updates a table (on the network) containing some text
fields and a memo field by adding or editing records. The form’s text
boxes are all Unbound and I have a list box which retrieves the desired
record to be updated. I put a save button to perform the Edit or Add
depending if the record existed or not. All works well until the memo
field gets close to or larger than 2k and then ERROR 3188 during
editing. I know it’s probably caused by the way the DAO updates the
data (2k pages at a time) but what I don’t know is how to fix it. Is
there a way to increase this limit? I read somewhere that AppendChunk is
a possibility but I can’t figure how to use it if I got only 1 table and
I am trying to Edit the memo field from an Unbound text box in a form.
Any suggestion will be appreciated.

 

[TC]

Show us the code where the error occurs (and mark the line where it occurs).

Also, what is error 3188? (we don't all have Access on our
internet-connected PCs)

TC

 

[RezaE]

Thanks for the reply TC.



This is the code of the save button that I put on the form. It
updates a specific record of the table with the data in the unbound
fields of the form.



Dim rst As Recordset



Set rst = CurrentDb.OpenRecordset("tbl_ers", dbOpenDynaset)



rst.FindFirst "ers_no=" & Me.ers_number1

rst.Edit

rst![ers_no] = Me.ers_number1

rst![ers_type] = Me.ers_type1

rst![engine_type] = Me.engine_type1

rst![engine_build] = Me.engine_build1

rst![txt_module] = Me.txt_module1

rst![wbs] = Me.wbs1

rst![edp] = Me.edp1

rst![title] = Me.title1



So far no problem. But when it tries to update the following memo
field, if the data that was retrieved from the table was over 2K then it
cannot update and gives a runtime error of (Couldn't update; currently
locked by another session on this machine. (Error 3188)). However if
the size of this field was under 2k it gives no error and updates the
recordset.



rst![details] = Me.details1 'The memo filed'

rst.Update

rst.Close

MsgBox "Save of ERS " & Me.ers_number1 & " Completed.", vbOKOnly,
"ERS Database"



Thanks.

 

[TC]

Hi RezaE

I've done some searching through Google Groups, and found many references to
the problem that you describe.

For example:

<QUOTE> I've found that users can add records with more than 2048 characters
in a memo field, but once a record has a memo field with more than 2048
characters, attempts to edit that record result in the "Could not update;
currently locked by another session on this machine" error. </QUOTE>

One poster said this. I do not know if it is true:

<QUOTE> I have run across this problem in A97 often. Sorry to hear it's not
fixed in 2000. It happens when 2 users try to update the same memo field in
the same record at the same time. The record becomes permanently locked.
The way I found it was to write a quick module which attempts to update the
memo field in each record, ie

dim x as recordset
set x=currentdb.openrecordet("Offending Table",dbopendynaset)
while not x.EOF
with x
.edit
!memofield=!memofield & ""
.update
end with
x.movenext
wend

</QUOTE>


Sorry that I can't give you any more information. This is a new error to me.
Perhaps someone else can shed some light on it?

Just a few other things about your code:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tbl_ers", dbOpenDynaset)
rst.FindFirst "ers_no=" & Me.ers_number1
rst.Edit

It is recommended to use CurrentDb as shown below. Also, should you check
that the findfirst did find a record?

Dim db as Database, rst As Recordset
Set db = currentdb()
Set rst = db.OpenRecordset("tbl_ers", dbOpenDynaset)
rst.FindFirst "ers_no=" & Me.ers_number1
if rst.nomatch then ..............
rst.Edit

HTH,
TC

Thanks for the reply TC.

This is the code of the save button that I put on the form. It
updates a specific record of the table with the data in the unbound
fields of the form.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tbl_ers", dbOpenDynaset)
rst.FindFirst "ers_no=" & Me.ers_number1
rst.Edit
rst![ers_no] = Me.ers_number1
rst![ers_type] = Me.ers_type1
rst![engine_type] = Me.engine_type1
rst![engine_build] = Me.engine_build1
rst![txt_module] = Me.txt_module1
rst![wbs] = Me.wbs1
rst![edp] = Me.edp1
rst![title] = Me.title1

So far no problem. But when it tries to update the following memo
field, if the data that was retrieved from the table was over 2K then it
cannot update and gives a runtime error of (Couldn't update; currently
locked by another session on this machine. (Error 3188)). However if
the size of this field was under 2k it gives no error and updates the
recordset.

rst![details] = Me.details1 'The memo filed'
rst.Update
rst.Close
MsgBox "Save of ERS " & Me.ers_number1 & " Completed.", vbOKOnly,
"ERS Database"

Thanks.
Posted via http://dbforums.com

 

[RezaE]

Thanks for your time TC...much appreciated.



I will try the fix that you have found and will post the result.



Thanks again,



RezaE

 

[RezaE]

Hi TC,



Unfortunately it still does not work. The only way I have found to get
around it is by;



1) cutting the data from the memo field.

2) pasting it back to the memo field prior to updating the record.



Basically using the clipboard as the edit buffer. I think access is
handicapping developpers with this problem unless there is a way around
this which I haven't found so far.



In resume; How can one edit a record which contains a memo field that
has more than 2048 characters using the data of unbound fields of a form
without being handicapped by the 2k edit buffer size. So could someone
please let me know if this is an MS Access issue or is there a script
that could do this without any problems.



Any help is appreciated.

 

[Tim Ferguson]

In resume; How can one edit a record which contains a memo field that
has more than 2048 characters using the data of unbound fields of a form
without being handicapped by the 2k edit buffer size. So could someone
please let me know if this is an MS Access issue or is there a script
that could do this without any problems.

You can shell out to a dedicated text editor like Notepad or Textpad, which
will give you 32K plus..

You can search for a large-size text edit control: have you looked in
ComponentSource or Winfiles.com? There is a free one called TER32, but I
forget the maker's name, something like Susbsystems Inc. Try googling for
it.

You can create your own editor, using bunch of text boxes and a String()
array

You can create your own Active X control using VB proper.

Well, that's a few ideas to get you started. I am sure other people will
have better ideas.

Best wishes


Tim F

 

[RezaE]

Thanks for the info Tim.



I was finally able to get this problem solved. It's probably not the
best way to do it but hey it worked for me. So for all those who have
the same problem as me, here's what I did;



Since I was not able to edit the memo field once it got bigger than 2k,
I have decided to not use "Edit" and use "AddNew" instead.



- I retrieve the desired record, hence the desired memo field to edit
and all other fields within that record, on my form into unbound
text boxes.



- at this time the memo field or any other field could be edited from
the form's unbound text boxes.



Now when the save button is pressed.



- I do a "FindFirst" in the recordset to locate the desired record that
matches the record in the form, and then do a "Delete" to completely
delete it from the table.



- Now I do an "AddNew" to write all my unbound text boxes to the
table and to create a new record with the same data as the unbound
text boxes.



I know this sounds elementary but the idea is to solve the
problem...right. So if it works for you then all the best.



P.S. I don't think this will work if you have autonumber fields.



Thanks again to all who helped.



RezaE

 

[1ersen]

Hi



I have also encountered ths showstopper.



I used

Forms.frmItems.Refresh

rs.Edit

....



to get around it.



try if it works for you.