Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 49

Thread: VBA to split lines based on fields in table

  1. #1

    VBA to split lines based on fields in table

    Hello,

    I have a database that contains a table of orders - some of which need to be invoiced in installments and are differentiated as such by the 'number of installments column

    If the order is to be invoiced in installments i need to split the order into multiple rows based on number of installments (eg 12 installments = 12 rows,6 installments = 6 rows) and also ensure that these newly created row contain the right 'installment amount' & 'installment posting date' for that installment from the source file

    I've uploaded a database with a sample input file and how i'd like the output to look like.

    Is this possible to do in Access? I've seen something similar done via record sets but not quite like this

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Yes this can be done with a VBA Recordset.
    I will take a look at your database and get back to you.

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    OK, does the data come in to the "Input" table in that structure?
    The output data is not correct, the input data should stay as it is and the Dates and Installments should be in a "Payments" Table to avoid all the duplication that is in your example output table.

  4. #4
    Data comes into the Input table in that structure - source data cannot be changed in anyway

    I need the duplication in the output table in that format as this table will be exported to provide data for another system

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    You can have the table in that format, but the export can be a Query that combines the 2 tables in to the format you want.
    Do you want me to look at the VBA for doing the transfer in to table 2?

  6. #6
    If you could look at the VBA that can do the transfer into table 2 that would be great - i would need it to be able to run as part of a macro if possible also.

    Thanks,
    Tom

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Tom, what is the maximum number of Instalments that can be made?

  8. #8
    12 is the maximum

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Tom, I have finished the VBA code and it says that your Output table was not quite right instalment wise.
    I have run the code once so that you can see the results, you can run it again if you want to see how quick it is.
    Open the form called transfer data and click the button.

    Which brings up a problem, should the input table have a Transferred check box or date to show it has been output, so that it is not actioned again?
    Attached Files Attached Files

  10. #10
    OBP,

    Thanks for doing this VBA code - it's really useful - I've got a few questions regarding the VBA it but I'll quickly type up some background info to help make sense of my questions.

    I'm planning to periodically import in data from system 1 into the input table and then reformat it into the output table in the right way for subsequent import into system 2 - each time it run's I'll have a query that will clear out input + output table so i don't believe a checkbox or date field will be required.

    2 questions

    1) Some rows will not be invoiced in installments so will not need to be split - if i a add a row without installment in input table (fields blank) i get a invalid use of null error - I've tried using the NZ function in the VBA but still get the same error - can the code be amended to run when the input table might have rows with no or less then 12 installments?

    2) Can this code be executed as part of a macro? my thought process is for my macro to import data into input table, run this vba, and export output table.

    Cheers,
    Tom

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Tom, the code should accomodate values less than 12, but not zero.
    I can overcome the error OK, but the question is what do you want to happen to the records with zero installments?

    As to using with a macro, it actually makes more sense to include all the code in to the VBA sub, one click, import, transfer and export.
    If you already have macros for the import and export you can "Save As" them as VBA code to se what the code looks like.

  12. #12
    If the row has zero installments it should copy over as it is into the output table.

    In reference to using a macro i have an automation tool that can run access macros for me (as well as download the required input data for this db from the FTP & upload the output data to another ftp etc) so if it was executable as a macro i would be able to automate the process from start to finish.

    Cheers,
    Tom

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    OK, I will change/add the code to send it over as is, I assume the installmentAmount and Installment Posting Date fields will be blank?

    I will transfer the code in to a module that you can call from a Macro

  14. #14
    Thanks - yes the 2 fields will be blank.

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Tom, here is the Macro Version, there is a new button on the form to run Macro1, which runs a Function that "calls" the code in Module 1.

    if all the fields in the output table have the same name and are in the same order it would be possible to drastically reduce the code by using this format in a loop to transfer the data

    rs.(x) = rstable(x).Value

    with the smaller loop to add installments.
    Attached Files Attached Files

  16. #16
    This is perfect!

    Yes all the fields in the output table should have the same name and order as that of the Input table - are you able to provide the VBA that does this? I'm constantly adding/removing columns to my database at the moment (changes mirrored in source/dest table)so this would be a lot easier to keep up to date as opposed to editing the VBA each time.

    Thanks again for all your help so far.

    Cheers,
    Tom

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Tom, here is the code to replace the code in the Transfer part of module 1.

    Dim data As String, count As Integer, count2 As Integer, rstable As Object
    Dim recount As Integer, rs As Object, x As Integer, fieldcount As Integer
    On Error GoTo errorcatch
    Set rstable = CurrentDb.OpenRecordset("input")
    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 34
                        rs.Fields(fieldcount).Value = rstable(fieldcount).Value
                    Next fieldcount
                    If count > 1 Then
                        x = count2 * 2
                        rs.[Instalment Amount] = rstable(34 + x).Value
                        rs.[Instalment Posting Date] = rstable(35 + 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
    MsgBox "Transferred records"
    Exit Sub
    errorcatch:
    MsgBox Err.Description & " - " & count2 & " field " & fieldcount
    The loop that counts from 1 to 34 is the one that transfers the "standard data", so if you want to add more fields ensure that you add them before the individual Installment fields and then adjust the 34 accordingingly.

  18. #18
    Thanks OBP! I'll look to implement this code the next time i'm asked to add or remove columns from this process.

  19. #19
    Hi OBP,

    I've tried adding more columns in the input/output tables and amending the VBA but the installments no longer split as expected and return various error messages - I'd like to fix this and make any further changes easier.

    You mentioned in post 15 that if the input/output tables are identical then the code could be simplified - would you be able to provide the VBA that does this which includes the smaller installment loop?

    Cheers,
    Tom

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,169
    Location
    Can you provide me with a copy of the tables that are now going wrong?
    I think that the code that I supplied in Post #17 already has the code that you talking about.

Posting Permissions

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