Query Will Not Update Over 127 Characters (1 Viewer)

[databasedonr]

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

 

[Jon K]

I have slightly modified your code like this in the attached database. It works on my system.

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.

 

[databasedonr]

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

 

[Jon K]

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.