Update Memo field cuts text

[Cris]

I have a query by form which copies the content of a Memo type field to another record, but the text gets cut when the length is over 510 characters and weird symbols are added at the end of my text. This happens in Access 2003, I'v tried in Access 2007 and my update works fine.
This is my query

UPDATE MyTable SET MyMemofield = [Forms]![Myform]![MysourcememoField]
WHERE Idfield=[Forms]![MyForm]![Idtoupdate];

I've been searching about this problem I know there are limitations when using distinct or group by, but i can not find a solution for my update problem but by all my research

I supposed there is a limitation when using parameters in this way. So, i tried running the query in code with query definitions and adding parameters but it was worst because the parameters only takes values until 255 characters.


Any one know a workaround for this?? Help please

Thanks in advance

 

[jdraw]

I have a query by form which copies the content of a Memo type field to another record, but the text gets cut when the length is over 510 characters and weird symbols are added at the end of my text. This happens in Access 2003, I'v tried in Access 2007 and my update works fine.
This is my query

UPDATE MyTable SET MyMemofield = [Forms]![Myform]![MysourcememoField]
WHERE Idfield=[Forms]![MyForm]![Idtoupdate];

I've been searching about this problem I know there are limitations when using distinct or group by, but i can not find a solution for my update problem but by all my research

I supposed there is a limitation when using parameters in this way. So, i tried running the query in code with query definitions and adding parameters but it was worst because the parameters only takes values until 255 characters.


Any one know a workaround for this?? Help please

Thanks in advance

Not sure what your problem is. In 2003 I just created a table with ID field autonumber, MyMemo type memo. Then a form with ID, myMemo and txtbox with length of MyMemo.

I can paste 1200, 905, 1007 bytes into the memo field via the form. Once I save the record nad toggle thru record I see the length as well. Doesn't appear to be a 2003 issue.

If you get truncation at exactly 255, then you have a text type field (limited to 255).

 

[Cris]

Thanks jdraw for you reply.

My copy is not a "cut and paste". I have a button "copy" wich takes my current record and copies some fields to a new record. The user choice weather or not to copy the notes (stored in my memo field), so, once I have created the new record, I verify if user wants to copy the notes and I run my query "rql_copy_notes" which containes:

UPDATE MyTable SET MyMemofield = [Forms]![Myform]![MysourcememoField]
WHERE Idfield=[Forms]![MyForm]![Idtoupdate];

This query copies the notes(memo field) from one record( [Forms]!)[Myform]![MysourcememoField] to the new one(MyMemofield)

I don't have problems when I input text through the form. It has been always functional. My problem is just when I use this copy functionnality and It just happen in Access 2003.

Thanks

 

[jdraw]

Thanks jdraw for you reply.

My copy is not a "cut and paste". I have a button "copy" wich takes my current record and copies some fields to a new record. The user choice weather or not to copy the notes (stored in my memo field), so, once I have created the new record, I verify if user wants to copy the notes and I run my query "rql_copy_notes" which containes:

UPDATE MyTable SET MyMemofield = [Forms]![Myform]![MysourcememoField]
WHERE Idfield=[Forms]![MyForm]![Idtoupdate];

This query copies the notes(memo field) from one record( [Forms]!)[Myform]![MysourcememoField] to the new one(MyMemofield)

I don't have problems when I input text through the form. It has been always functional. My problem is just when I use this copy functionnality and It just happen in Access 2003.

Thanks

I just created a table tblMemo with 3 fields
id autonumber
lengthmemo number
mymemo memo

and a form with id, lengthmemo =Len(myMemo)
and myMemo and a button cmdAdd_new with caption Add Record and Copy Memo field

The ON Click is

Private Sub cmdAddNew_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblmemo")
On Error GoTo Err_cmdAddNew_Click
'
'if you click the Add Record and Copy Memo
'--a new record is added
'--the memofield on the Form is copied to new record
'--the length of the memo field is placed in field lengthMyMemo in new record
'--the table is updated with the new record

rs.AddNew
rs!Mymemo = Me.Mymemo
rs!LengthMemo = Len(rs!Mymemo)
rs.Update
Me.Requery

Exit_cmdAddNew_Click:
    Exit Sub

Err_cmdAddNew_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNew_Click
    
End Sub

It adds the memo field in current record to the new record if you click the button.

If you just move the record selector to next record, you do not get the memo field copied.
I'm using Access 2003

 

[Cris]

Thanks jdraw for your tests and answers.

I see that the copy of the memo field with the recordset works. But I still wonder if it could be anything else wrong with my program the way I was doing the update or it is really a limitation of Access 2003.

As I told you before I made the copy with a query by form (rql_Copy_Notes) and I called it on my button click event :

  If CopyNotes Then
      With DoCmd
        .SetWarnings False
                .OpenQuery "rql_Copy_Notes"
                .SetWarnings True
            End With
   End If

rql_Copy_Notes contains:

UPDATE MyTable SET MyMemofield = [Forms]![Myform]![MysourcememoField]
WHERE Idfield=[Forms]![MyForm]![Idtoupdate];

I really don't understand why this is not working because all research I've done talks about memo fields problems with distincts, unions, agregate functions and limitations of 255 when using parameters in code but I couldn't find anything about this kind of problem, and my text is not cutting at 255.

I thanks again for your replies, i just really would like to avoid future problems with this kind of queries and memo data type.