Access SQL to save value in unbound textbox cannot save more than 255 characters

Question

I've read through a couple similar posts, but not found a solution for this issue:

I have a form with an unbound rich text, multiline textbox named tbxNote. When the textbox is exited, I use VBA code to create an SQL string which I subsequently execute to UPDATE a table field [Note] with the value in the unbound textbox. [Note] is a "Long Text" field (from my understanding, "Long Text" is equivalent to what used to be called a "Memo" field). The backend is an Access database.

Problem is: Only the first 250 characters of what is in tbxNote get stored in the target table field [Note] even though other "Long Text" fields in other tables are accepting values much longer than 250 characters. So, it does not seem to be an issue with the field type or characteristics in the backend table.

Furthermore, if I manually open the target table and paste 350 characters into the same [Note] field in the target table, all 350 characters get stored. But, if I load up that record into the form or put the same 350 characters into the form's tbxNote textbox, only 250 characters are pulled into tbxNote or saved out to [Note].

Is there a way to store more than 250 characters in an unbound textbox using an UPDATE SQL in code?

In case it matters, here's the SQL code that I used to prove only 250 of 350 characters gets saved to the table field [Note]:

dbs.Execute  "UPDATE tblSupeGenNotes " & _
    "SET [NoteDate] = #" & Me.tbxNoteDate & "#, " & _
        "[SupeType] = " & Chr(34) & Me.cbxSupeType & Chr(34) & ", " & _
        "[SupeAlerts] = " & alrt & ", " & _
        "[Note] = " & Chr(34) & String(350, "a") & Chr(34) & " " & _
    "WHERE [SupeGenNoteID] = " & Me.tbxSupeGenNoteID & ";"

Of course, normally I'd have Me.tbxNote instead of String(350, "a") but the String proves that only 250 of the 350 characters get stored in the [Note] field.

I must be missing something simple, but I cannot figure it out.

Answer 1

@HansUp's suggested trying a DAO recordset to update the table. That did the trick! Thank you, HansUp. HansUp requested that I post the answer, so, here is the code that worked for anyone else who comes across this thread:

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

'Open a table-type Recordset
Set rsTable = dbs.OpenRecordset("tblSupeGenNotes", dbOpenDynaset)

'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("qrySupeGenNotes", dbOpenDynaset)

'update the values vased on the contents of the form controls
rsQuery.Edit
rsQuery![NoteDate] = Me.tbxNoteDate
rsQuery![SupeType] = Me.cbxSupeType
rsQuery![SupeAlerts] = alrt
rsQuery![Note] = Me.tbxNote
rsQuery.Update

'clean up
rsQuery.Close
rsTable.Close
Set rsQuery = Nothing
Set rsTable = Nothing

AH! Another bit to the solution is that prior to using the DAO recordset, I was pulling values from the table into a listbox and from the listbox into the form controls (instead of directly into the form controls from the table). Part of the problem (I believe) was that I was then populating the form controls from the selected item in the listbox instead of directly from the table. I believe listboxes will only allow 255 characters (250 characters?) in any single column, so, everytime I pulled the value into the textbox from the listbox, the code was pulling only the first 255 characters into the textbox. Then, when the textbox was exited, the code was updating the table with the full textbox text, but when it was pulled back into the form through the listbox, we'd be back down to 255 characters. Of course, when I switched to the DAO approach, I also switched to reading the textbox value directly from the table instead of pulling it from the listbox.

Moral: Beware of pulling Long Text values through a listbox!

Thanks to everyone who helped me solve this. Sorry for such a newbie error seeming more complicated than it was.

Answer 2

Unfortunately, you posted test code works, but you FAILED to post your actual update string that fails. A common (and known) problem is if you include a function (especially aggregates) in your SQL, then you are limited to 255 characters.

In fact this can apply if you have function(s) that surrounds the unbound text box and is used in the query.

So such an update should and can work, but introduction functions into this mix can cause problems with the query processor.

If you included the actual update, then the above issue(s) likely could have been determined. So the workarounds are:

Don’t use any “functions” directly in the SQL update string, but build up the string.

So in place of say:

Dbs.Execute "update tblTest set Notes = string(350,’a’)"

Note how above the string function is INSIDE the sql.

You can thus place the function(s) OUTSIDE of the query and thus pre-build the string - the query processor is NOT executing nor will it even see such functions.

So we can change above to as PER YOUR EXAMPLE:

Eg:

Dbs.Execute "update tblTest set Notes = ‘" & string(350,’a’) & "’"

(this is how/why your posted example works, but likely why your actual code fails). So functions can (and should) be moved out of the actual query string.

Also make sure there is NO FORMAT in the formatting for the text box, as once again this will truncate the text box to 255.

And as noted here the other suggestion is to consider using a recordset update in place of the SQL update.

Using a recordset can often remove issues of delimiters and functions then become a non issue.

So such SQL updates can work beyond 255 characters, but functions need to be evaluated in your VBA code before the query processor gets its hands on the data as per above examples.

And as noted remove any “format” you have for the text box (property sheet, format tab).

Answer 3

I assume you are using the SqlClient library. In which case, I recommend trying SqlParameters rather than creating a SQL string the way you are. With the SqlParameter you can specify the size of each parameter. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2 . I am a C# dev so my apologies about doing the example code below in C#:

string param = "Hello World";
byte [] encodedStr = Encoding.UTF8.GetBytes(param);
SqlParameter sqlParam = new SqlParameter();
sqlParam.Size = encodedStr.Count;  // uses byte count

you could condense it by calling Encoding.UTF8.GetBytes(param).Count. Anyways, this might fix your issue