Results 1 to 5 of 5
  1. #1
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7

    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 with record-sets.

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    something like this to loop thru the query N times....
    usage if looking at records on a form:

    'call the ID field on the form
    CreateInstallments txtID


    Code:
    Public Sub CreateInstallments(ByVal pvID)
    Dim iNum As Integer, i As Integer
    Dim sSql As String
    
    
    DoCmd.SetWarnings False
    iNum = DLookup("[Number of Instalments]", "input", "[id]=" & pvID)
    For i = 1 To iNum
       sSql = "INSERT INTO [Output] SELECT Input.* FROM [Input] WHERE (((Input.ID)=" & pvID & "))"
       DoCmd.RunSQL sSql
    Next
    DoCmd.SetWarnings True
    
    
    DoCmd.OpenTable "output"
    End Sub
    tho fix the SQL to match your target table.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Tom123456 View Post
    Is this possible to do in Access?
    Yes. Just some VBA and done!

    However, a normalized design would be
    Click image for larger version. 

Name:	Structure1.png 
Views:	13 
Size:	83.8 KB 
ID:	30808

    First thing I would do is get rid of all spaces in object names. Change "Prepayment %" to "PrepaymentPCT". Shouldn't use special characters or spaces in object names.

    You would open a recordset on table INPUT.

    For each record,
    Use variables to get all fields except the installation fields. Then get the first installation data.
    Append the data to the OUTPUT table. (Maybe rename it to "ORDERS"??)
    Loop until all installations have been appended.
    Next record (until done)

  4. #4
    Tom123456 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    7
    Hi ranman,

    I'd like to try and execute your code to test but cannot get it to work - how can i run it via a macro?

    @Ssanfu - thanks i have renamed prepayment field.

    In reference to opening record set grabbing fields etc and looping through is there any documentation/guides you know of that explain how this can be done written in VBA in more detail? my vba knowledge is quite basic!

    Cheers,
    tom

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In reference to opening record set grabbing fields etc and looping through is there any documentation/guides you know of that explain how this can be done written in VBA in more detail?
    Learn the syntax basics of the commands... then practice.

    Maybe start with Crystal's site


    Try this - just 1 way to do it.....
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Split one record into multiple lines
    By Sally in forum Access
    Replies: 1
    Last Post: 03-11-2013, 07:03 PM
  2. Need Grid lines on null fields in report
    By lpkorn423 in forum Reports
    Replies: 1
    Last Post: 12-27-2012, 08:45 AM
  3. How to break lines in memo fields?
    By newyorkyankee in forum Access
    Replies: 2
    Last Post: 05-11-2012, 02:56 PM
  4. Replies: 1
    Last Post: 12-01-2010, 11:01 AM
  5. Replies: 3
    Last Post: 10-23-2008, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums