RecordSet Edit Memo Field, getting error Could Not update (1 Viewer)

[Telecom]

Hello,

I have a form that allows me to add or edit data to a table. In that table I have a Memo field called 'fld_Tech_Intr'.

When I first create the record I'm able to add 2,000 plus characters to that memo field 'fld_Tech_Instr'.

When I try to update that field by using a RecordSet I receive an access error.

"Error Number -2147217887, Could not update; currently locked by another session on this machine."

This only happens when my memo field is 2,000 plus characters. If the memo field is shorter, I don't get the error. There is no one else using the database when I'm making this change so I know it's not another user locking out my record.

I'm searched around the internet and I only found a thread that this was Microsoft bug. Is there a way around this error? I want to continue to use RecordSets to update my data. I don't want to put a character limit of 2,000 on my memo fields.

Here is my RecordSet code that I used to update my table. For sake of keeping it small I only including the one Memo Field I'm having problems with.

'Declare DIMs
Dim RecordSet As New ADODB.RecordSet
Dim Connection As ADODB.Connection
Dim strSearchFieldName As String
Dim intFindValue As Integer
Dim strRecSetTble As String
Dim intTbleFldCnt As Integer


'Define DIMs
strSearchFieldName = "fld_Work_Order_ID"
intFindValue = Me.txt_fld_Work_Order_ID
strRecSetTble = "tbl_Work_Orders"
intTbleFldCnt = 1


Set Connection = Application.CurrentProject.Connection 'Sets the connection to the current project

RecordSet.Open strRecSetTble, CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Pulls the info from the table

Connection.BeginTrans 'Begins a new transaction

'Searches on the table for the strSearchFieldName that is equal to intFieldValue
RecordSet.MoveFirst
Do While Not RecordSet.EOF

    If (RecordSet(strSearchFieldName).Value = intFindValue) Then

        'intTbleFldCnt is going to be equal to the # of fields in the RecordSet table - It steps through each field
        For intFieldCount = 1 To intTbleFldCnt Step 1
        
            strFieldName = "fld_Tech_Instr"
            varFieldTemp = Me.txt_fld_Tech_Instr
        
            'Test for Null or "" - Table won't accept ""
            If varFieldTemp & "" = "" Then
                RecordSet([strFieldName]).Value = Null 'No data in field = Null
            Else
                RecordSet([strFieldName]).Value = varFieldTemp
            End If
        
        Next intFieldCount

        RecordSet.Update
        
        Exit Do

    End If

    RecordSet.MoveNext

Loop

Connection.CommitTrans 'Saves any changes and ends the current transaction

'Clean Up
RecordSet.Close
Connection.Close
Set RecordSet = Nothing
Set Connection = Nothing

Thank You!

 

[Telecom]

Anyone else have this issue with the memo field not updated past 2k characters using a RecordSet to save the table?

I never had to have a memo field past the 2k limit so this is the first time I've seen this issue.


PS: Should this thread be moved to VBA and Modules section?

 

[Telecom]

I looked around the web to find more information on error 3188 and found this thread...

http://www.dbforums.com/microsoft-access/1606228-runtime-error-3188-while-updating-memo-field.html

I found an interesting post in that thread about this problem. The poster indicated that the record is locked because the main form still has the record in use. I'm not sure why a memo field on 2,000+ characters would cause an issue with this and not any other field.

I set out to test the theory out...


I have a main form that has a command that allows me to edit a new record. When I choose to edit a record, a modal popup form comes up. There are no bound records to a query in the modal popup form. I pull all my information through VBA from my main form. When I save the information in my popup form it will requery or refresh the main form.

I went into my popup form and set it up temporary so it doesn't require the information from the main form. With my main form closed I run the edit modal popup form. I save over approx 3,000 characters in my memo field and the record saves with no error.

So the issue is why does the memo field has have issue with 2,000 + characters in it? I need my main form opened because that is how I run multiple forms and I'm not about to change for a bug that shouldn't even be there. There has to be a way to save a record with that memo field error limit...

Anyone have a solution to that?

 

[Telecom]

I found no easy solution to fix the issue with the memo field and the 3188 locked error. I used to open up my modal popup form off the main form to edit the record. I would pull the table record fields from the main form to fill in my popup modal form.

Private Sub Form_Open(Cancel As Integer)

Set varSourceForm = Forms![frm_Work_Orders]

'etc

End Sub

Private Sub ProcLoadFields()


Me.txt_fld_Work_Order_ID = [varSourceForm].[Form]![fld_Work_Order_ID]
Me.txt_fld_Created_By = [varSourceForm].[Form]![fld_Created_By]
Me.txt_fld_Date_Created = [varSourceForm].[Form]![fld_Date_Created]
Me.txt_fld_Time_Created = [varSourceForm].[Form]![fld_Time_Created]

'etc

End Sub

Now I pull just my ID field from the main form into my modal popup form. Then I clear that link to the main form. I load my fields by a RecordSet table load instead of pulling all the fields from the main from and leaving the link open. That is the only way I seem to get this to work.

'Declare DIMs
Dim Connection As ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
Dim strSearchFieldName As String
Dim intFindValue As Integer
Dim strRecSetTble As String
Dim intTbleFldCnt As Integer


'Sets the main field ID
Me.txt_fld_Work_Order_ID = [varSourceForm].[Form]![fld_Work_Order_ID]

'Clears the link to the main form but clearing the varSourceForm
varSourceForm = ""


'Define DIMs
strSearchFieldName = "fld_Work_Order_ID"
intFindValue = Me.txt_fld_Work_Order_ID
strRecSetTble = "tbl_Work_Orders"
intTbleFldCnt = 1


Set Connection = Application.CurrentProject.Connection 'Sets the connection to the current project

RecordSet.Open strRecSetTble, CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Pulls the info from the table

Connection.BeginTrans 'Begins a new transaction

'Searches on the table for the strSearchFieldName that is equal to intFieldValue
RecordSet.MoveFirst
Do While Not RecordSet.EOF

    If (RecordSet(strSearchFieldName).Value = intFindValue) Then
        'Debug.Print RecordSet(strSearchFieldName) 'For debugging purposes only
        
        'intTbleFldCnt is going to be equal to the # of fields in the RecordSet table - It steps through each field
        For intFieldCount = 1 To intTbleFldCnt Step 1
        
            strFieldName = "fld_Tech_Instr"
            Set ctlFieldTemp = Me.txt_fld_Tech_Instr
        
            'Test for Null or "" - Table won't accept ""
            If RecordSet([strFieldName]) & "" = "" Then
                ctlFieldTemp.Value = ""
            Else
                ctlFieldTemp.Value = RecordSet([strFieldName])
            End If
        
        Next intFieldCount
        
        Exit Do

    End If

    RecordSet.MoveNext

Loop

Connection.CommitTrans 'Saves any changes and ends the current transaction

'Clean Up
RecordSet.Close
Connection.Close
Set RecordSet = Nothing
Set Connection = Nothing