Memo fields +255 characters

[JamesN]

Hi,

I have a form with a few text boxes which users are inputting lots of text, usually over 255 characters.

This is then submitted as new records in a table into fields formatted as memo.

The issue I am having is that anything after 255 characters is cut off.

How can I prevent this from happening and allow the full text to be added?

Thanks

 

[CJ_London]

just to clarify terminology - you don't format fields as memo, memo is their datatype

If the memo field is bound to the control on a form then there is not a problem, so this implies there is something wrong with

'This is then submitted as new records'

How are you submitting? please provide the code, not a description

 

[JamesN]

It is being submitted using the below...

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from TrialDataWarehouse")

rec.addnew

rec("Comments1") = commentsfield
rec("Comments2") = ucncommentsfield2
rec("Comments3") = ucncommentsfield3

rec.update

Set rec = Nothing
Set db = Nothing

 

[CJ_London]

Why not just set the form recordsource to TrialDataWarehouse and dataentry to true?

But back to your code, I seem to recall that using your method truncates the string. To test, between the assignment and your rec.update line put in debug.print lines for both the rec field name and the form control and see what results you get.

You could try using an ADO recordset instead - I don't think ADO truncates, but not sure.

an alternative (which is my preferred method for unbound forms) is

 dim sqlstr as string
  
 sqlstr="INSERT INTO TrialDataWarehouse
 sqlstr=sqlstr & " (Comments1,Comments2,Comments3)
 sqlstr=sqlstr & " VALUES('" & commentsfield & "', '" & ucncommentsfield2 & "', '" & ucncommentsfield3 & "')"
 currentdb.execute sqlstr, dbfailonerror

which will be faster than either of the recordset methods anyway.

 

[JamesN]

I've never used the form recordsource or ADO method before.

Just to add - the form also includes bound objects in addition to unbound.

Would it be possible to use the method I have used below but add an extra bit in for the comments? Also, i'm finding that a local copy of the database will add more than 255 characters when published to Sharepoint it doesn't allow more than 255. Does Sharepoint limit it to 255?

 

[gemma-the-husky]

james.

you must have more code - do you have on error resume next somewhere?

If you try to insert more text in a field than it can handle, I am sure you get a run time error.

[assuming your fields comment1, comment2 and comment 3 are all defined text/short text. and not MEMO (longtext). If they are defined as MEMO, then there shouldn't be an issue]

 

[JamesN]

All of the fields storing lots of comments are saved as memo datatype. I thought this was correct for storing lots of comments in Access 2010?

Currently not getting any run time errors, all that is happening is that the first 255 characters are being added to the table and anything after that is not added.

I do have other code but it is just the code to add the other object results to the table...eg...

rec("Listening") = ucn1
rec("Questioning") = ucn2
rec("Acknowledge") = ucn3

 

[JamesN]

I found on the below link about a limit to characters when storing tables in a Sharepoint list. is this the issue?

https://technet.microsoft.com/en-us/library/cc262787.aspx

 

[CJ_London]

if you are using sharepoint perhaps, but still think your recordset method is the problem.

Would it be possible to use the method I have used below but add an extra bit in for the comments?

I doubt it. Suggest try ADO or the execute method before trying to fit a bolt into a pinhole

 

[JamesN]

You were right CJ London. I didn't use the ADO method as I wasn't familiar with it but I built an SQL query which adds the commentary based objects into the table at the end and that is adding them fully without truncating

Appreciate the support