Memo field -- Error 3188, using Access 2007

[lmcc007]

Error 3188:

I have an EventNote table with three fields:
1) EventNoteID - AutoNumber
2) EventID - Number
3) EventNoteData – Memo

The EventDetail form has a button to click to add notes, which will open to EventNoteData field. I found out if you are adding data over 2K it will not save or let you delete the data or revise the data and you get the Error 3188.

I’ve been searching this past week and found out this is a known problem with Memo fields.

Has anyone by now found a solution?

 

[MajP]

As far as I could tell this is not a "known issue." By that, I mean nothings shows that the problem is recreateable. I googled this, and saw a couple of people claiming to see this error, but nothing definitive on what was unique with their form, control, or code to cause this. If you have a link identifying this as a "known problem" please post it.

The limit should be 65k on a memo field, so this is not normal behavior. My first recommendations
1) Ensure you have every service pack and patch on Office and Windows. A2007 had a lot of quirks early on. However, I have not seen this listed.
2) Check your indices on the table. Limit your indices and ensure no index on the memo field.
3) Post your code to add a note. If you have concurrent calls to the recordset and you are doing form updates this will cause a problem

The obvious workarounds (not cures) are
1) Use an unbound control for for the memo field. Then populate/write to the control using the forms recordset. Or you can use sql action queries instead.
2) Put the memo field data into a seperate table. There may be advantages to this if you have big memo fields. See if you can edit a subform instead.



This is a possible workaround with the memo field called "memoFld"

Private Sub Form_Current()
  Call readMemo
End Sub

Public Sub readMemo()
  Dim ctrl As Access.TextBox
  Set ctrl = Me.txtBxMemo
  
  If Me.NewRecord Then
    ctrl.Value = ""
  Else
    ctrl.Value = Me.Recordset!memoFld
  End If
End Sub

Public Sub writeMemo()
  Dim rs As DAO.Recordset
  Dim ctrl As Access.Control
  
  Set ctrl = Me.txtBxMemo
  Me.Dirty = False
  If Not IsNull(Me.txtBxMemo) Then
    Set rs = Me.Recordset
    rs.Edit
      rs!memoFld = ctrl.Value
    rs.Update
  End If
End Sub

Private Sub txtBxMemo_AfterUpdate()
  Call writeMemo
End Sub

 

[lmcc007]

Below are just three links/addresses:

http://search.yahoo.com/search;_ylt...r+3188&fr=att-portal-s&toggle=1&cop=&ei=UTF-8

http://www.accessmonster.com/Uwe/Misc/SR.aspx?p=Error+3188

Workaround: Error 3188 (Cannot not update Memo field with >2k characters)

Check out this link.

http://sites.google.com/site/msaccesscode/forms-1

 

[lmcc007]

MajP,

I have it set up in a separate table. Okay, let me go back. I get Error 3188 -- Couldn't update. Currently locked by another session on this machine

Using Access 2007

In my database my EventNoteData memo field is in a separate table—that is, EventNote table has three fields:

1) EventNoteID – AutoNumber (primary key)

2) EventID – Number (event related to)

3) EventNoteData – Memo (note field)

From that table I created a form called fdlgEventNote. This form is Pop-up and Modal. This form will be called from the EventDetail form.

EventDetail form (Pop-up and Modal) has a command button you can click to add notes, which will open to EventNoteData so you can add notes. You can't go to another form until this form is close, and so on.

1. I tried to update a text field with more than 2K characters.

2. In records with shorter text I can update the field.

3. I can add, revised, or delete the long text field if ONLY one form is open.

4. When two or more forms are opened, I get the Error 3188 when I try to edit, delete, or add.

 

[lmcc007]

Majp,

I am not following you here with the code above. I have to create an unbound field in order to use it as a memo field.

All I want is a simple memo field so I can add lengthy notes to a record. This memo field will be called from another form because I was told that memo fields should be in a separate table and so on.

Uh, this is way too frustrating. I tried the suggestions, but known dealt with when you have form 1 opened and you open form 2 to add an event and the you open form 3 to add a note for that event.

 

[lmcc007]

MajP,

What does memoFld refer to?

 

[MajP]

As I said, this is not normal behavior, you should be able to add 65k in a memo field. Yes this should be simple and work as you designed, but there is something going on. But without being able to see it, I cannot say why. I believe that is has to do with 2 forms bound to this field at the same time. Could be a bug.
Is the main form or another form also referencing the note field? If that is the case, and from what you describe, an obvious work around would be to close the main form, pop open your notes form, make your notes updates, close the note form, pop open the main form, and go back to the record. (obviously all in code and seamless).
The other “work around” as I describe is to use an unbound control. But the thought was that you would use an unbound control to accomplish this. I do not, but some access developers by habit use unbound controls for all memo fields. That is probably a carryover from the old A97 days when there was more problems with Memos.
As stated “memoFld” is the name of the memo field. The code is very similar to your link, but more complete.
Again, what indices are on your notes table?
If you want post a link to your database. You can make a demo version with a couple of pertinent fields and just the two forms.

You mentioned trying suggestions. What suggestions did you try?

 

[lmcc007]

MajP,

How do I accomplish: ...close the main form, pop open your notes form, make your notes updates, close the note form, pop open the main form, and go back to the record. (obviously all in code and seamless)...?

 

[lmcc007]

No, no other form is referencing the note field. The indexes in the EventNote table are EventID and EventNoteID (pk). How do I update a db on here? I'll try to get a demo version of it in the meantime.

 

[lmcc007]

... Could be a bug..." I am thinking the same thing especially if others are experiencing the same problem.

I just tried to create the command button to open the EventNote form using a Macro. The field in the left column for the EventDetail form is not listed, but the fields in the right for the EventNote form are there. Hummm! I created a new form with just the three forms (Company, Event, EventNote) and created the command buttons using a Macro (all the fields are listed on both sides) and I am able to add, revised, and delete in the EventNote form.

 

[MajP]

Does that mean you have a working solution that fits your needs or was that just a test?

As stated, this is not normal behavior. There is some set of conditions (which I do not know) that can get you into this state. I would still be curious to see a demo. That may help figure out the conditions under which this will occur.

 

[lmcc007]

MajP,

I just got back from mediation all day, so I have not worked with it. Frankly, I am sick of this db but since I started it I have to finish it. I don't know what to do. I thought about creating it again from scratch, but that's simply too much work--the last resort. Then I thought maybe the notes I copied and pasted from Adobe Acrobat could be the problem, so I tried copying them into Word unformated, and then copying them back into Access. Still having the same problem; wouldn't let me update or add to the form when other forms are opened.

Then I thought maybe something is wrong with the VBA code, so let me create a command button using a macro; that's when I discovered the wizard is not working correctly. Meaning, I did the following:

1. fmainCompany is opened in Design View

2. Click command button and put on form

3. Command Button Wizard starts.

4. What action do you want to happen when the button is pressed?

5. Categories = Form Operations

6. Actions = Open Form

7. Next

8. What form would you like the command button to open? = fdlgEventDetailAdd

9. Next

10. Open the form and find specific data to display.

11. Next

12. Which fields contain matching data the button can use to lookup information?

Select the fields and then click <-> button.

fmainCompany: is blank

fdlgEventDetailAdd: has all the fields listed.


Why is fmainCompany not showing the fields?

I copied the two tables to a blank db and ran the command button wizard again and all the fields from both tables are listed.

 

[MajP]

If you can post this on 4shared.com I could take a look and am sure I can fix it in half the time it will take you to try to describe the problem and me try to debug without seeing it.

 

[lmcc007]

Yeah, that's the problem. He doesn't want any of the data or designed to be posted. I can remove the data and enter dummy stuff. The forms are the problem--don't want the design and logo posted. Let me see if I can remove the logo and design style.

What is 4shared.com?

 

[MajP]

4shared is one of the free file sharing sites, you can use any.

Yes just provide the very minimum amount of objects to demonstrate the problem. The other issue is it may be an access installation dependent issue. It may not even occur on my version of access. If you can post it, I guarantee I can get you a working demo.

 

[lmcc007]

Now with the three forms the field is working. So I don't know what is what. I need to quit and start from scratch maybe tomorrow. It's noway I can post his db--too many forms and queries and stuff. And narrowing it down to three forms--not getting it.

Thanks anyway!

 

[lmcc007]

Hello MajP,

Will post how to do the following:

"...an obvious work around would be to close the main form, pop open your notes form, make your notes updates, close the note form, pop open the main form, and go back to the record. (obviously all in code and seamless)...."

Thanks!

 

[MajP]

something like this (untested). Also I have no idea how you are determining to add a new note or edit existing. You would have to explain how you have that set up.

from main form
  dim strWhere as string
  dim lngEventID as long
  dim noteCount as integer
  lngEventID = me.eventID
  strWhere = "EventID_FK = " & lngEventID
  'do a dcount here to determine if the event has any notes
  'if it has some notes open in  
  noteCount = dcount("noteID", "qryNotes",strWhere)
  'close form
  docmd.close acform,me.name
  if noteCount > 0 then
    docmd.OpenForm "frmNotes",,,strWhere condition",acFormEdit,acDialog,lngEventID 
  else
     docmd.OpenForm "frmNotes",,,,acFormAdd,acDialog,lngEventID
  end if

from notes form on current event.
me.eventID_fk = clng(me.openArgs)

From the notes close event
docmd.openForm "frmEvent",,,,,acDialog,lngEventID
'If you are not opening the event form in dialog you could return it to the correct record from code in this event. However I think you said it was dialog.

So back in the EventForm on the on open event


If trim(me.openArgs & " ") <> "" then
me.recordset.moveFirst "EventID = " & clng(me.openArgs)

As I said, I did this off the top of my head so it will probably have some errors, but the logic should be correct.

If these forms are not dialog then the solutions are easier. The problem with dialog is once you open a form in dialog code execution stops in the calling form until the popup is closed.

 

[lmcc007]

All the forms are in Pop UP, Modal.

Do the following:

1) FmainCompany form opens
2) EventDetailAdd form opens
3) EventNote form


To revise

1) EventHistory form shows a list of events
2) choose the event you want to revise, which opens
3) EventDetailEdit form from this form you can add or edit
4) EventNoteEdit form

 

[MajP]

That is a little hard to follow, but I think the general idea should be the same. I provided some "overkill" which you will not need. See if you can adapt the general idea.