Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Create a series of rows and append to table (Access2003)

    I have an Employee loan table (tblLoans) that contains the following information:
    LoanID
    Term (which is always 24 months)
    Startdate:
    Enddate:
    FirstPay:
    LastPay:

    A loan payment is deducted from the employee in their month end pay.
    The first 23 loan payments equal the “FirstPay” Amount
    The last (24th) payment equals the “LastPay” Amount

    I need to create something that will add the following to a separate table (tblPaymentDetails):

    Loan ID
    Payment# (1-24)
    PayDate
    Amount

    Originally, I created this information in an Excel spreadsheet and was able to create the monthly loan payments easily. Unfortunately, I am not as successful in Access 2003. I assume that I need to do a “loop” and use some sort of insert into table command. I assume this is possible based on various things I have read but I cannot get it to work. Could someone help me with this? (If this has already been discussed in this forum, I apologize but my searches could not find anything)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a series of rows and append to table (Access2003)

    Here is some VBA code you can use. It requires a reference to the Microsoft DAO 3.6 Object Library in Tools | References... (in the Visual Basic Editor).
    I changed the name of some of your fields slightly because I don't like characters such as a space, : or # in names in Access.
    <code>
    Sub AddPayments()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim n As Integer
    Dim dtmStart As Date
    Dim curAmount As Currency

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    strSQL = "SELECT tblLoans.* FROM tblLoans LEFT JOIN " & _
    "tblPaymentDetails ON tblLoans.LoanID = " & _
    "tblPaymentDetails.LoanID " & _
    "WHERE tblPaymentDetails.LoanID Is Null"
    Set rstIn = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("tblPaymentDetails", dbOpenDynaset)
    Do While Not rstIn.EOF
    n = rstIn!Term
    dtmStart = rstIn!StartDate
    For i = 1 To n
    rstOut.AddNew
    rstOut!LoanID = rstIn!LoanID
    rstOut!PaymentNo = i
    rstOut!PayDate = DateAdd("m", i - 1, dtmStart)
    If i < n Then
    rstOut!Amount = rstIn!FirstPay
    Else
    rstOut!Amount = rstIn!LastPay
    End If
    rstOut.Update
    Next i
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    The code won't append records for LoanIDs that are already present, so it's safe to run the code more than once.

    I have attached a sample database with demo tables and the code.
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create a series of rows and append to table (Access2003)

    Again, I am amazed at how quickly you respond! Thank you.

    As soon as I finish this post I will begin implementing your work. By the way, I used the # sign in the original post because I thought it would make it clearer. In the original table it is simply "Payment"

    Again, thank you for your prompt response!

  4. #4
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create a series of rows and append to table (Access2003)

    I finally was able to clear some time to work through your coding and add it to my database and it works great! Again, thank you for your prompt response!

    Two questions though:

    1) I added your coding to a form that I had created by adding a command button and adding "Call AddPayments" to the on click event. Is that ok or will something that I have missed cause this to do ugly things "down the road"?

    2) If the startdate (dtmStart) is a month where the last day is not the 31st, it appears that the paydates will not show the monthend date but whatever the last day was for the startdate. For example, if the startdate is April 30, 2008, then the date for the second payment is May 30, 2008, not May 31. In my case, the actual paydate is the 28th, so this should not be a problem but I had originally used the monthend date when I discovered this. For future reference, is it a simple chore to amend the coding to allow for month end dates?

    Again, I would like to thank you for your prompt response to my query.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a series of rows and append to table (Access2003)

    1) Calling the code from a button on a form is fine.

    2) If you always want the pay date to be the last day of a month, change the line

    rstOut!PayDate = DateAdd("m", i - 1, dtmStart)

    to

    rstOut!PayDate = DateSerial(Year(dtmStart), Month(dtmStart) + i, 0)

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Create a series of rows and append to table (Access2003)

    Thanks for your prompt response, again!

    I thought that the use of Dateserial was required and I had tried some combinations that were utter failures but your solution is so simple that I am embarassed taht I asked.

    Again, thanks!

Posting Permissions

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