Moving records with Arrays

[Mark Wild]

I have jsut been given a flat file that has come out of one of our other system that has come out in a mess.

I have tidied it up to stage where it looks like

Code --- Address
01 --- address1
01 --- address2
01 ---- town

02----address1 etc

(The number of address lines can vary)

I am trying to get this into a table as per one would normally record this information

code--address1--address2...

Here's what I've maanged so far. I haven't incldued my attempt at an array as it was so far wrong!

Set db = CurrentDb
Set rst = db.OpenRecordset("SupplierAddress", dbOpenDynaset)
Set rstNew = db.OpenRecordset("Table1", dbOpenDynaset)
' table1 has columns code, field1, field2, field3 - 6
  
With rst
    .MoveFirst
    
    strCode = !suppcode
        Do Until .EOF
            i = 0
            Do Until strCode <> !suppcode
            'keep adding columns until the code chanes, then need a new row
            .Edit
                    i = i + 1
                    rstNew!Field(1) = !Address
                    ' in a hope that the first line will go into column1 and so on
                    .MoveNext
            Loop
            rstNew!Code = strCode
        .Update
        .MoveNext
    Loop
End With

 

[Fizzio]

Mark, it seems not to have come out at all!

 

[Mark Wild]

I managed to resolve this through one of the better bodge jobs of my developing career. Avoid this solution unless you get desperate!

I ran the code below, which created two columns, code and address. Each part ofthe address was seperated by a comma.

I then pasted it into notepad and imported it as comma delimited, and have the address broken down as I wanted it.

Set rst = db.OpenRecordset("SupplierAddress", dbOpenDynaset)
Set rst2 = db.OpenRecordset("New", dbOpenDynaset)

With rst
    .MoveFirst
     
        Do Until .EOF
           strCode = !suppCode
    
                Do Until strCode <> !suppCode Or .EOF
                
                    If strAddress = "" Then
                        strAddress = !Address
                    Else
                        strAddress = strAddress & ", " & !Address
                    End If
                        
             .MoveNext
            Loop
            rst2.AddNew
            rst2!suppCode = strCode & ","
            rst2!Address = strAddress
  
   rst2.Update
   strAddress = ""
Loop
End With