Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 49 of 49

Thread: VBA to split lines based on fields in table

  1. #41

    Hi

    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
    Attached Files Attached Files

  2. #42
    Just to add it current populates Installment 1 instead of 'Installment Amount'

  3. #43
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What code are you using and how do you run it?

  4. #44
    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

  5. #45
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  6. #46
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  7. #47
    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

  8. #48
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  9. #49
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •