Okay, here is the code that I used that transfer more than 256 characters from one memo field to another. This works, but I have to click it twice to get the text to show up in the form. I am not sure why that is.
I used a table called Table1, and TableID as an Autonumber PK, with Memo1 and Memo2 memo fields.
'---------Button On Click Event----------
Private Sub Command6_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sID As Integer
sID = Me.TableID
sSQL = "SELECT * FROM Table1 WHERE TableID = " & sID
'Open Local Connection
Set cn = New ADODB.Connection
.ConnectionString = CurrentProject.Connection
.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open sSQL, cn
'Move contents of one memo field to the other.
rs("Memo2") = rs("Memo1")
'Try to get the Memo2 field on the form to show current state.