FAO Jeff Boyce

[Guest]

Hello, I noticed you’ve replied very recently to a couple of threads, and so wondered if you’d enter into a dialogue? I tried to reply to your replies directly, but I get a server error when I do so. However I can start new threads, and so….

There is a posting below from me, titled “127 character limit†which describes a probable bug when using update queries on text fields with >127 characters. This was supposed to be fixed in SP1/1a, but since I’m running SP3 it doesn’t seem to be.

I noticed a couple of days ago (21/10) you had an exchange with a chap called Sam, at the end of which you suggested he re-designed his database rather than fix the problem. Forgive me for being suspicious but as an Access MVP are you aware of this bug resurfacing?

As I said, I can't post replies for some reason, so if you want to ask something please feel free to use the e-mail address.

Cheers,

Terry

 

[Jeff Boyce]

Terry

I've only recently noticed several posts re: 127 characters, and have not
checked the knowledge base or Google.com for previous postings. Those would
be options if you're interested in pursuing it further.

I'm surprised I would tell anyone to "fix their database" rather than
offering ideas for solution -- if I did, sorry, Sam! I suspect what I
intended was to point out an alternative approach to solving the problem...
but I probably defined the problem as needing to use more than 127
characters, not as "this isn't working, make it work!".

Newsgroup protocol asks that Q & A (and discussions) stay in the 'group,
that all might benefit. For this reason, I am not responding directly back
to your email address.

Regards

Jeff Boyce
<Access MVP>

 

[Guest]

I am having the 127 character problem running an update query from a button on a form. If the field on the form that holds the text to be used for the update value contains more than 127 characters, the update query will not run and returns an error 3001. (I'm using Access 2000.) With 127 characters or less in the field, all is well.

I've seen several postings in google.groups and a few postings in this forum, but to date I have not found an answer.

I would appreciate it if anyone could provide an answer or a work-around. Thanks!

Sue

 

[Jeff Boyce]

Sue

As I recall, there was a patch/SR related to this issue. Have you checked
the MS Knowledge Base?

Jeff Boyce
<Access MVP>

 

[Gary Walter]

Sue

As I recall, there was a patch/SR related to this issue. Have you checked
the MS Knowledge Base?

Jeff Boyce
<Access MVP>

Hi Jeff,

I *think* my Office 2000 installs are all patched
but I still can replicate this issue.

Is there a chance you could ask for and then provide the link?

Thanks,

Gary

 

[Jeff Boyce]

Sorry, Gary, it's just a dull memory. Try the MS KB website, or the Office
Updates site.

Jeff

 

[Gary Walter]

Sorry, Gary, it's just a dull memory. Try the MS KB website, or the Office
Updates site.

Jeff

Hi Jeff,

I know "dull memory"..it is my friend.

I did find this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;281153
ACC2000: When You Export an Access Report the Memo Field Is Truncated

*** quote ***
When you export an Access 2000 report in Microsoft Excel 97-2000 format,
text in any Memo field that contains more than 127 characters is truncated to 127
characters.
RESOLUTION
To correct this problem, obtain the latest service pack for Microsoft Office 2000.
*** unquote***

But I could not find "textbox in update query" fix.

It wasn't a wasted search though (as these excursions almost always prove out).

I found an ADO MultiFind sub in

http://support.microsoft.com/default.aspx?scid=kb;EN-US;195222

that I have packed away in my "dull memory."

Gary

 

[Guest]

Can anyone help me out with a work around for this? I'm running into the difficulty while using an update query called by a button on a form. It appears from other posts that putting this procedure into VBA rather than using an update query ends up with the same error message. Any suggestions

Thanks

Sue

 

[Gary Walter]

Can anyone help me out with a work around for this? I'm running into the

difficulty while using an update query called by a button on a form. It appears from
other posts that putting this procedure into VBA rather than using an update query
ends up with the same error message. Any suggestions?Hi Sue,

Would you like a small zipped demo mdb (31KB)
that demonstrates one method to "work around"
this problem? I'll try to explain it in case it won't work for you.

tblMemo
ID MemoField LenMemoField
1 "I am having the 127 character .." 552
2 "Hello, I noticed ...." 909
3 "Terry I've only recently ...." 780

(the MemoField's above are abbreviated here
but were full text from messages in this thread
and had len as shown)

tblM
ID MemoField LenMemoField
1 "-" 1
2 "-" 1
3 "-" 1
4 "-" 1
5 "-" 1

I created a form bound to tblMemo.

On that form I created 2 command buttons,
one would update all MemoField's in tblM
to current MemoField shown on form,
the other command button would update
only the record in tblM whose ID=1
with the current MemoField shown on
the form.

'**** code for form *****
Option Compare Database
Option Explicit

Private Sub cmdUpdALLtblMToMe_Click()
Dim strSQL As String
Dim lngID As Long

lngID = Me!txtID

strSQL = "UPDATE tblM, tblMemo " _
& "SET tblM.MemoField = [tblMemo].[MemoField], " _
& "tblM.LenMemoField = Len([tblMemo].[MemoField]) " _
& "WHERE (((tblMemo.ID)=" & lngID & "));"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Private Sub cmdUpdRec1tblMToMe_Click()
Dim strSQL As String
Dim lngID As Long

lngID = Me!txtID

strSQL = "UPDATE tblM, tblMemo " _
& "SET tblM.MemoField = [tblMemo].[MemoField], " _
& "tblM.LenMemoField = Len([tblMemo].[MemoField]) " _
& "WHERE (((tblMemo.ID)=" & lngID & ") " _
& "AND ((tblM.ID)=1));"
CurrentDb.Execute strSQL, dbFailOnError

End Sub
'**** end of code for form ****

(Reminder: Using "CurrentDb" above requires that you set
a reference to DAO library.)

 

[Guest]

Gary

That is terrific! I'd love to have the .mdb file if you wouldn't mind sending it to (e-mail address removed)

I really appreciate your help. Happy New Year

Su