Hi,
I've modified it to the below and it's run as part of a macro (RunCode) - I've included it in the sample db as a module called 'Split Data -Instalments'
Option Compare Database
Function calltransfer()
Call TransferData
End Function
Sub TransferData()
Dim data As String, count As Integer, count2 As Integer, rstable As Object
Dim recount As Integer, rs As Object, x As Integer
On Error GoTo errorcatch
Set rstable = CurrentDb.OpenRecordset("DestTable")
Set rs = CurrentDb.OpenRecordset("output")
rstable.MoveLast
recount = rstable.RecordCount
rstable.MoveFirst
For records = 1 To recount
If Not IsNull(rstable![Number of Instalments]) And rstable![Number of Instalments] <> 0 Then
count = rstable![Number of Instalments]
Else
count = 1
End If
For count2 = 1 To count
rs.AddNew
For fieldcount = 1 To 46
rs.Fields(fieldcount).Value = rstable(fieldcount).Value
Next fieldcount
If count > 1 Then
x = count2 * 3
rs(47) = rstable(47 + x).Value
rs(48) = rstable(48 + x).Value
rs(49) = rstable(49 + x).Value
End If
rs.Update
rs.Bookmark = rs.LastModified
Next count2
rstable.MoveNext
Next records
skip:
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description & " - " & count2 & " field " & fieldcount
End Sub