Error 3188 - Solution (MS Access 2010 vba)

[arpeggione]

Hi All:

I have gotten LOTs of help over the past few years on this and other forums. So...Below is a solution to the Error 3188 (when saving a memo field with over 2000 characters) error - and, by george...it actually works for me. The idea - cut the memo text into sections of less than 2000 characters and save a section at a time with a "for-next" loop. It worked for a memo field of 13,000 characters - a huge one, I can't say)....Doubtless, someone may have a better way to do this, but here it is!

Cheers,
Karen (aka Arpeggione)

myCharCount = Len(Forms!frmeditevents!txtEditDSNotes)
myMemoText = Forms!frmeditevents!txtEditDSNotes
'MsgBox "memo field has = " & myCharCount & " characters"
If myCharCount > 2000 Then
rstEvents.Update
'Round up
numStrings = -Int(-1 * myCharCount) / (2000)
'MsgBox numStrings
Exit Sub

For i = 0 To numStrings - 1
myMemoTextSection = Mid(myMemoText, (i * 2000) + 1, 2000)
'MsgBox myMemoTextSection
rstEvents.MoveLast
rstEvents.MoveFirst
rstEvents.Bookmark = varBookmark
'MsgBox rstEvents("syseventID")
'Exit Sub
rstEvents.Edit
rstEvents("Notes") = rstEvents("Notes") & myMemoTextSection
rstEvents.Update
Next
Else
rstEvents("Notes") = Me.txtEditDSNotes
'Me.Refresh
rstEvents.Update
End If

 

[MichaelRed]

Not really my kind of thing, so please have some patience?

varbookmark doesn't seem to ever be assigned a value?

numStrings = -Int(-1 * myCharCount) / (2000) ' SEEMS to be overly complex, while insufficient?

from debug:
myCharCount = 13000
numStrings = -Int(-1 * myCharCount) / (2000)
? numStrings

Numstrings2 = myCharCount \ 2000
? Numstrings2
6

... but ...
? 6 * 2000 = 12000 What about the LAST 1000?

I believe the "roundup" function could be found on Tex-Tips. Thus alleviating the complex math as well as providing the necessary pad to accommodate the additional chars.

I do not see how / where myCharCoount gets updated, however I do seem to observe theat the function wants to exit straightaway with the IF statement then the exit statement immediately above the loop

finally, there are NO declaration statements ... ??? ...

I am painfully sure that this is all my failure to understand.



MichaelRed

 

[arpeggione]

To michaelred: thx for questions. I will answer some now & some next week when i return to work.
1. Varbookmark, Declaration ststements: these are assigned earlier in the procedure. I did not post the whole procedure.
2. My char count only needs to be tested once. If it is under 2000, then the procedure is not needed
3. I am doing this from memory, but i dont think access vba has roundup. Maybe excel and other versions of vb do. The "int" statement is a workaround.
4. Will post a refined version in a few days.

Thx so much for your comments!
Karen

 

[arpeggione]

To MichaelRed:

More response from Karen:
==============
1. Varbookmark, Declaration ststements: these are assigned earlier in the procedure. I did not post the whole procedure.
2. My char count only needs to be tested once. If it is under 2000, then the procedure is not needed
3. I am doing this from memory, but i dont think access vba has roundup. Maybe excel and other versions of vb do. The "int" statement is a workaround.
4. Will post a refined version in a few days.

Thx so much for your comments!
Karen
=====================
#2...If you have myCharCount = 12,950 and you compare the two formulas:
a) numStrings = -Int(-1 * myCharCount) / (2000) will give you 7 (a workaround - no "roundup" function in MSAccess vba. See

http://allenbrowne.com/round.html

for more info.

b) mycharcount/2000 will give you 6

#4 here are my declaration statements (keep in mind, I'm only posting snippets from the procedure):
Dim dbOutages As dao.Database
Dim rstEvents As dao.Recordset
Dim mySQL
Dim myCharCount As Long
Dim myMemoTextSection
Dim numStrings As Long
Dim i As Integer
Dim myEventID As Integer
Dim varBookmark As Variant

Let me know if you have any more questions!
Thank you,
Karen

 

[arpeggione]

One more post from Karen....I am new at posting solutions. I am on the right track, but what I've posted isn't quite perfect...so thank you for your patience.

Karen

 

[MichaelRed]

Karen, Thanks for your response.

An issue w/ posting partial solutions is that anyone responding cannot know what is missing. I recognize this "procedure" needs to be called from a Form {e.g. the Key Word "Me"}. When I mention the "roundup" procedure, I should, perhaps, have elaborated to specify that these were posted in the various fora as examples developed by members of the specific fora as helpful solutions to inquiries (a.k.a posts) as FAQs.

I believe there are at least two versions of routines of "Round" procedures which you could use / modify to suit your needs. As to the int "function" the backslash character (e.g. "\") has been a integer divisor symbol in basic since the early 1980's and continues to be available in VBA.

As I posted:

Numstrings2 = myCharCount \ 2000
? Numstrings2
6

Thanks or your response



MichaelRed

 

[MichaelRed]

see faq702-4019

for ONE example of a member contributed solution to ROUINDING. Not necessarily my favorite.

The issue, for me, in your posting is that it IS incomplete. I might be searching for a solution to this - or a similar problem, but am missing a number of the pieces as posted.

Thank you. again, for your courtesy in responding.

Sincerly

MichaelRed

 

[MichaelRed]

See faq702-4019



MichaelRed

 

[Andrzejek]

My $0.02 to your post would be – use some kind of standards for naming your variables, and avoid Variants wherever possible.

[tt]Dim dbOutages As dao.Database
Dim rstEvents As dao.Recordset
Dim mySQL[/tt] Why use Variant here, String would do just fine, so [tt]Dim strSQL As String
Dim myCharCount As Long[/tt] How about [tt]Dim lngCharCount As Long
Dim myMemoTextSection[/tt] Variant again, how about: [tt]Dim strMemoTextSection As String
Dim numStrings As Long[/tt] How about: [tt]Dim ;ngStrings As Long
Dim i As Integer
Dim myEventID As Integer[/tt] How about [tt]Dim intEventID As Integer
Dim varBookmark As Variant[/tt]

And please use code TAGS in your posts, it makes the code a lot easier to read.

Just a suggestion...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

I do not get this post. As far as I know 3188 is a concurrency issue, when a user has the field locked. Curios on how to recreate this issue, I would like to test. You should have no problem saving any memo field less than 64K characters.