VBA to split lines based on fields in table

[Tom123456]

Hi OBP,

Sorry for the delayed response - you were right in that the fields in question were the wrong data type,changing them fixed the issue but forgot to reply and thank you.

I was hoping you'd be able to help me with an amendment to the VBA i was trying to do - I'd like to also grab the field 'Installment Description' from the source table and populate the field in the output - i made some amendments which worked. However in doing so i broke the way 'Installment amount' was being populated. I've gone over the code several times but cant see what could be causing these differences logically.

Thanks,
Tom

[Tom123456]

Just to add it current populates Installment 1 instead of 'Installment Amount'

[OBP]

What code are you using and how do you run it?

[Tom123456]

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

[OBP]

You said
"Just to add it current populates Installment 1 instead of 'Installment Amount'"
Installment 1 is 7500 in both tables, so that appears to be correct, but the second Installment is not being populated.
I will have to go back to the original database to see what it was doing.

[OBP]

I have compared the 2 sets of code and the following errors show up
The original code counts to 46 records the later code also counts to 46.
However the transfer from horizontal to vertical fields the original code was
x = count2 * 2
rs(46) = rstable(47 + x).Value
rs(47) = rstable(46 + x).Value
ie 2 fields starting at 46 but tranferring 47+x
your code has
x = count2 * 3
rs(47) = rstable(47 + x).Value
rs(48) = rstable(48 + x).Value
rs(49) = rstable(49 + x).Value
3 fields as you want, but you are starting at 47 = 47 not 46 = 47.

[Tom123456]

Hi OBP,

I've moved the columns slightly to allow for the new field i wanted to add and as a result changes the col numb it was using - from what i understand in this VBA code X represents the installment number for the row being created multiplied by the number of columns i need it to go along the input table to grab the correct installment date/amount/description columns for that installment row.

Given that

RS(47) = Installment Amount
RS(48)= Installment Posting Date
RS(49) = Installment Description Final.

                    x = count2 * 3
                     rs(47) = rstable(47 + x).Value
                     rs(48) = rstable(48 + x).Value
                     rs(49) = rstable(49 + x).Value

Why does it work for RS48 & 49 but for RS47 it doesn't take the values from rstable50,53,56? Or have i misunderstood how the code works?

Thanks,
Tom

[OBP]

If you have moved the staring point to 47 then the original count has to go to 47 as well and then 47 =48+3, 48 = 49+ 3, 49 = 50 + 3
It is the first line after x = count2 * 2 that you have changed it was 46 = 47 + x and you made it 47 = 47 + x and then the others have to follow that.

[Tom123456]

Makes sense thanks! I've actually been testing since i posted & found a simpler way of achieving this that won't require me to add/update 12 columns to both tables for the VBA - however this wouldn't of been possible without your earlier work so thanks again!

 rs(fieldnumber) = "Invoice " & count2 & " of " & count

Where count2 is the current installment number being processed and count is the number of installments

Tom