Split record (1 Viewer)

[momoko]

Hi,

I have the following records in 2 fields (Main and Joint_Party):

Main Joint_Party
1234 4567
1235 7890
1236 3345

how can I make the Joint_Party into another record that is right after the Main, like :

Main Joint_Party
1234 4567
4567
1235 7890
7890
1236 3345
3345

Thanks!

 

[Tim K.]

Check this code out. I assume that your table is Table1.

Sub CopyValue()
Dim dbs As Database, rst1 As Recordset, rst2 As Recordset
Dim I As Long
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("Table1")
Set rst2 = rst1.OpenRecordset
If Not rst1.EOF Then
    For I = 1 To rst1.RecordCount
        rst2.AddNew
            rst2("Main") = rst1("Joint_Party")
        rst2.Update
        rst1.MoveNext
    Next I
End If
rst2.Close
rst1.Close
dbs.Close
Set rst2 = Nothing
Set rst1 = Nothing
Set dbs = Nothing
End Sub