Recordset MEMO copying mismatch error 1

[PhoenixDon]

I have two Access 2000 tables whose structure is identical. Every week I copy the contents of the TR table to the TR_History table. The code below is supposed to compare each record in the TR table with the History table. If matching records are found then I increment a counter, copy a string field, and copy a memo field. That is where I encounter my problem. Works fine until data in the memo field is found, then I get a #13 run time error for a data mismatch. (CopyLargeField rstTR_History!actTaken, rstTR!actTaken)

My code below is straight from the help file under getchunk/appendchunk. I'm kinda of new to VB (Delphi experienced) and have target fixation. Been staring at the same error for a day now, can't see what is wrong. Could be stupid newbie problem. Do functions have to be declared?Anyway ... thanks in advance for any help.

Don



Dim db As DAO.Database
Dim rstTR As DAO.Recordset
Dim rstTR_History As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
Set rstTR = db.OpenRecordset("TR&quot
If rstTR.RecordCount > 0 Then
rstTR.MoveFirst
Else
MsgBox "The TR table is empty, please import some data to compare."
Exit Sub
End If

Do Until rstTR.EOF
If IsNull(rstTR!actTaken) Then
rstTR.Edit
rstTR!actTaken = " "
rstTR.Update
End If
strSQL = "SELECT SSAN, trtype, actTaken, ChargeWho, ChargeNumber "
strSQL = strSQL & "FROM TR_HISTORY WHERE SSAN = '" & rstTR!ssan & "' "
strSQL = strSQL & "AND TRTYPE = '" & rstTR!trtype & "'"
Set rstTR_History = db.OpenRecordset(strSQL)

Debug.Print "ActTaken = " & rstTR_History!actTaken & " : Record Count = " & rstTR_History.RecordCount

If rstTR_History.RecordCount > 0 Then
rstTR.Edit
If IsNull(rstTR_History!actTaken) Then
rstTR_History.Edit
rstTR_History!actTaken = " "
rstTR_History.Update
Else
'Copy memo field SOURCE to DESTINATION
CopyLargeField rstTR_History!actTaken, rstTR!actTaken
End If

If IsNull(rstTR_History!ChargeWho) Then
rstTR!ChargeWho = " "
Else
rstTR!ChargeWho = rstTR_History!ChargeWho
End If

If IsNull(rstTR_History!ChargeNumber) Then
rstTR!ChargeNumber = 0
Else
rstTR!ChargeNumber = Str(rstTR_History!ChargeNumber + 1)
End If
rstTR.Update
rstTR.MoveNext
Else
rstTR.MoveNext
End If
Loop
rstTR.Close
rstTR_History.Close
db.Close

End Sub


Public Function CopyLargeField(fldSource As Field, fldDestination As Field)

' Set size of chunk in bytes.
Const conChunkSize = 32768

Dim lngOffset As Long
Dim lngTotalSize As Long
Dim strChunk As String

lngTotalSize = fldSource.FieldSize
Do While lngOffset < lngTotalSize
strChunk = fldSource.GetChunk(lngOffset, conChunkSize)
fldDestination.AppendChunk strChunk
lngOffset = lngOffset + conChunkSize
Loop

End Function

 

[Aivars]

VBA allows to update Memo fields without using Chunk method.

If IsNull(rstTR_History!actTaken) Then
rstTR_History.Edit
rstTR_History!actTaken = &quot; &quot;
rstTR_History.Update
Else
'Copy memo field SOURCE to DESTINATION
rstTR_History.Edit
rstTR_History!actTaken, rstTR!actTaken
rstTR_History.Update
End If

Aivars

 

[PhoenixDon]

Aviars,

Thanks for the post! Did you mean:

.edit
rstTR!actTaken = rstTR_History!actTaken
.update

rather than:

'Copy memo field SOURCE to DESTINATION
rstTR_History.Edit
rstTR_History!actTaken, rstTR!actTaken
rstTR_History.Update

Though I no longer get the mismatch error, test data from the TR_History table is not populating the field in the TR table. Any thoughts?

Thanks,
Don

 

[Aivars]

Sorry, Don! I mistook before during coying your codes:

'Copy memo field SOURCE to DESTINATION
rstTR_History.Edit
rstTR_History!actTaken = rstTR!actTaken
rstTR_History.Update

Aivars

 

[PhoenixDon]

Still no error, but no data either. Anyone?

 

[Aivars]

Some times recordset.RecordCount doesn't count the records. It's good if record set is populated before using RecordCount property

rstTR_History.movelast
rstTR_History.movefirst
If rstTR_History.RecordCount > 0 Then

Aivars

 

[PhoenixDon]

I added the following code and watched record count values. Didn't seem to have problem.

Debug.Print &quot;Record Count =&quot; & rstTR_History.RecordCount
rstTR_History.MoveLast
rstTR_History.MoveFirst
Debug.Print &quot;Record Count =&quot; & rstTR_History.RecordCount

Also I watched the values in the memo ActTaken field of both recordsets and test data from the TR_History recordset went to the TR recordset. However, when I looked at the tables, the memo text had not transferred.

Are you sure I don't have to use the getchunk/appendchunk method?

Don

 

[Aivars]

I really never have meet such problem. I always give expected results of recordsets updating (including MEMO fields).

Try to print field values:

rstTR_History.Edit
debug.print rstTR!actTaken
debug.print rstTR_History!actTaken

rstTR_History!actTaken = rstTR!actTaken
debug.print rstTR_History!actTaken
rstTR_History.Update
debug.print rstTR_History!actTaken

Aivars