AddNew Using Field Name Variable

[crissyjo1]

I was hoping someone could give me a hand. I need to split up a memo fields in some large tables. The tables all have the same name for the memo field, but the users could have renamed other fields or even added and deleted some. Thus, the field name variable.

Using the code below seems like it should work, but it gets to one of the fields and gives me a data type conversion error (on line 54). Mind you, the new table was created using the acExport function and I can physically look compare all of the fields in my test tables and see they are the same format. Is there something I need to do to insulate the field name variable to get this to work? Thanks for your help!

Sub SplitMemo()
Dim i As Integer
Dim rstOld As DAO.Recordset, rstNew As DAO.Recordset, rstTemp As DAO.Recordset
Dim items As Variant, thing As Variant
Dim strField, strTemp As String
strField = "Fieldname"

Set rstOld = Nothing
Set rstNew = Nothing


On Error GoTo Error_Handler

10  Set rstOld = CurrentDb.OpenRecordset("Select * from [Old Table Name]", dbOpenSnapshot)

20  Set rstNew = CurrentDb.OpenRecordset("New Table Name", dbOpenTable)

    rstOld.MoveFirst
    Do While Not rstOld.EOF
30  items = Split(rstOld![FieldName], ";", , vbTextCompare)

40  For Each thing In items
    rstNew.AddNew
    rstNew!Requirements = thing
50
51          For i = 0 To rstOld.Fields.Count - 1
52          If Not rstOld.Fields(i).Name = strField Then
53               If Not IsNull(rstOld.Fields(i).Value) Then
54                  rstNew.Fields(i) = rstOld.Fields(i)
56               End If
57
58          End If
59          Next i
60
   
        

61  rstNew.Update

62  Next
64  rstOld.MoveNext
68  Loop

70  rstNew.Close
72  Set rstNew = Nothing
74  rstOld.Close
76  Set rstOld = Nothing

Error_Handler:
MsgBox "The following error has been generated" & vbCrLf & vbCrLf & _
"Error Number:" & Err.Number & vbCrLf & "Line Number:" & Erl & vbCrLf & _
"Error Source: SplitMemo" & vbCrLf & "Error Description: " _
& Err.Description, vbCritical, "An Error Has Occured!"

 

[spikepl]

1. That particular field contains perhaps something indigestible: debug.print the value contained in rstOld.Fields(i) when it fails and paste it here
2. You say data is formatted in the same way. Did you verify that the Data Type of the field in question is in fact the same in both tables

 

[crissyjo1]

1. That particular field contains perhaps something indigestible: debug.print the value contained in rstOld.Fields(i) when it fails and paste it here
2. You say data is formatted in the same way. Did you verify that the Data Type of the field in question is in fact the same in both tables

Spikepl

Thanks for the quick reply. I determined it's failing on a text field, the value being a 10-character digit with a letter at the end. The field is defined as text, 255 characters. Those and all other properties regarding this field match on both tables. The weird thing is, I try deleting that field from the old database along with any records with blank fields, and it still errors on that field.

 

[spikepl]

Did you try to debug-print the field? Or go into the table, get into the field and scroll through ALL characters and see what is there ? If you have a line break in a text field, you might not be seeing all the contents

 

[crissyjo1]

Did you try to debug-print the field? Or go into the table, get into the field and scroll through ALL characters and see what is there ? If you have a line break in a text field, you might not be seeing all the contents

Well, I'm not familiar with debug.print, but I was using msgbox to show me the offending field and its value is fine. It looks fine in the table itself too.

But your response got me to thinking. I am able to manually copy the record and paste it into the new db. Is it possible that while it's looping, it gets to line 52, detects that memo field I'm trying to split and then gets hung up? To test this theory, how do I change the order so that that memo field gets dealt with first in both tables?

Thanks!