Query Will Not Update Over 127 Characters

databasedonr

Registered User.
Local time
Yesterday, 19:53
Joined
Feb 13, 2003
Messages
163
Greetings all,

I've had a similar problem before and posted to this forum, but have rediscovered this problem.

I am updating records from a form; on the form I have a text box called txtRemarks which writes to a Memo field in the database.

This is the code that I use to write the query:

Dim strQueryComment As String

strQueryComment = "UPDATE tblExhibit SET Remarks= [Forms]![InsertExhibit]![txtRemarks]" _
& "WHERE (tblExhibit.ExhibitID) =" & DMax("[ExhibitID]", "tblExhibit") & ";"

DoCmd.RunSQL strQueryComment

This particular update is designed to add a comment to the last entered record. At the end of the procedure

This works fine for anything up to 127 characters. Once I hit 128 characters, a message is returned:

"You can't carry out this action at the present time"

The application then hangs, the update is not added to the table.

I've seen a similar problem before, where I was trying to write to a memo field from a form textbox using a stored query, and I worked around that one by writing the query in SQL in the code, as I've done here, but no success.

I've looked everywhere I can imagine and found no answers -- has anyone seen anything like this before, and is there a solution or workaround?

I am running Access 2000 on a Windows 2000 Pro machine.

Thanks in advance
 
I have slightly modified your code like this in the attached database. It works on my system.
Code:
Private Sub cmdUpdate_Click()
   Me.txtRemarks = Replace(Me.txtRemarks, "'", "''")
   
   Dim strQueryComment As String

   strQueryComment = "UPDATE tblExhibit SET Remarks='" & [Forms]![InsertExhibit]![txtRemarks] _
       & "' WHERE (tblExhibit.ExhibitID) =" & DMax("[ExhibitID]", "tblExhibit") & ";"

   DoCmd.RunSQL strQueryComment
End Sub

The SQL statement now uses the single quote as the text delimiter.

In case there are single quotes entered in txtRemarks, I first use the Replace function to replace any single quote in txtRemarks with two consecutive single quotes before running the SQL statement, so as to avoid posible syntax error.
 

Attachments

Last edited:
Interesting -- and I certainly will give it a try.

Somewhere in the depths of the internet I found a comment that it was not possible to update a memo field from a text box on a form beyond 127 characters if you didn't use a recordset. I can't really comment on the validity of that statement, except to say that I rewrote the code to create a recordset and it seems to work fine in early testing.

Thanks for you input, Jon, and either way I think your code will be extremely useful.

Thanks again,

Don
 
I'm not sure about the recordset. But if you run the report in my attached database, you will see that I was able to insert the contents of your first post into the Remarks field in the third record.
 

Users who are viewing this thread

Back
Top Bottom