Forms & How to assign a previous field value assigned to next Record

[jmeni]

Hi all,I'm working on a loan amortization schedule. I have created tblLoans, tblMembers, tblSchedules and tblPaymentsj also frmLoans, sbfSchedules & sbfPayments. I have also generated the schedule initally using DAO recordset.I want to add payments on a monthly basis with optional extra payments above the amortization amount if desired. I'm ok without the extra payments.

Challenge:
1. When I add extra, it reflects for the 1st record and 2nd record, then stops(should continue through and since extra payments will be made, then the number of payments should be reduced accordingly)
2. I also want a situation where on clicking the btnAddPayment, it checks the txtAmountPaid field, if there is a value(not zero), it moves to the next record and adds the payment, I also wants this check in sbfPayments(it is filled based on btnAddPayment in sbfSchedules)


3.Irrespective of the position of the cursor, I want it to fill the next record with a zero in both sbf's.Please find attached an image and the DB in MS Office 2000 format.
4.I noticed that the schedule table stores only the active member even if the member id is changed and schedule generated. Please how can I generate and store every generated schedule in the schedule table.
Code for sbfSchedules

Option Compare Database
Option Explicit

Public Sub btnAddPayment_Click()
   On Error GoTo btnAddPayment_Click_Error
   
    Dim dblAmountPaid As Double
    Dim datPaidDate As Date
    
    Dim RST As DAO.Recordset
    Set RST = CurrentDb.OpenRecordset("Schedules", dbOpenDynaset)
    
    dblAmountPaid = InputBox("What is the Payment Amount?", "Payment", Form_Loans.txtPMT)
    datPaidDate = InputBox("What is the Payment Date?", "Date", Date)
    
    Me!txtAmountPaid = dblAmountPaid
     Form_Schedules.txtRegular = Form_Schedules.txtAmountDue
    If Not Nz(dblAmountPaid) Then
          Form_Payments.txtAmountPaid = dblAmountPaid
        Form_Payments.txtPaidDate = datPaidDate
        Form_Schedules.txtExtra = Form_Schedules.txtAmountPaid - Form_Schedules.txtRegular
        Form_Schedules.txtEndingBalance = Round((Me.txtBeginningBalance) - _
        (Me.txtAmountDue) - (Me.txtExtra), 2)  
                                               DoCmd.GoToRecord , , acNext
    If Not RST.EOF Then
    Form_Schedules.BeginningBalance = RST!EndingBalance 
Me.txtEndingBalance = Me.txtBeginningBalance - Me.txtAmountDue - Me.txtExtra
    End If
    
    RST.Close
    Set RST = Nothing
   
    Me.Recalc
    Me.Refresh

    End If
btnAddPayment_Click_Exit:
    Exit Sub
    
btnAddPayment_Click_Error:
    Resume btnAddPayment_Click_Exit
End Sub

Private Sub btnRecalcSchedules_Click()
    Me.Recalc
End Sub

Public Sub btnRepaymentSchedule_Click()
    On Error GoTo btnRepaymentSchedule_Click_Error
   
    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    Set db = CurrentDb
    Set RS = db.OpenRecordset("Schedules", dbOpenDynaset)
    
    'Declare variables & Calculate Repayment Schedule
   
    Dim intLoanID As Integer
    Dim PMTN As Integer
    Dim datDueDate As Date
    Dim dblBeginningBalance As Double
    Dim dblAmountDue As Double
    Dim dblAmountPaid As Double
    Dim dblRegularPayment As Double
    Dim dblExtraPayment As Double
    Dim dblEndingBalance As Double
    Dim dblMonthlyRate As Double
    Dim datPaidDate As Date
   
     'Assign variables(from frmLoans)
    If IsNull(Form_Loans!txtPMT) Then
        MsgBox "Please calculate Monthly Payment to continue", vbOKOnly
     Else
        dblAmountDue = Round(Form_Loans.txtPMT, 2)
    End If
    
    dblBeginningBalance = Form_Loans!LoanAmount
    dblMonthlyRate = Round(Form_Loans!InterestRate / Form_Loans!NumberOfPayments, 5)
    datDueDate = Form_Loans!StartDate
    
    'test for BOF & EOF
    If RS.RecordCount <> 0 Then
    
    MsgBox "Are You SURE? This will ERASE all your payment DATA,and create a new schedule!", _
    vbYesNoCancel
        
        While Not RS.EOF
        RS.MoveFirst
        RS.Delete
        RS.MoveNext
        Wend
    
    'Loop for each month
        For PMTN = 1 To Form_Loans!NumberOfPayments - 1
          
    'Calculate the relevant figures (BeginBalance = Principal = oldbalance | EndBalance = newbalance)
        'dblInterestPaid = Round(dblBeginningBalance * Form_Loans!txtInterest, 2)
        'dblPrincipalPaid = Round(dblAmountDue - dblInterestPaid, 2)
        dblEndingBalance = Round(dblBeginningBalance - dblAmountDue, 2)
        'dblTotalInterest = dblTotalInterest + dblInterestPaid
        dblExtraPayment = dblAmountPaid - dblRegularPayment
    
    RS.AddNew
        RS.Fields("LoanID") = Form_Loans.LoanID
        RS.Fields("PaymentNumber") = PMTN
        RS.Fields("DueDate") = datDueDate
            datDueDate = DateAdd("m", 1, datDueDate)
        RS.Fields("BeginningBalance") = dblBeginningBalance
        RS.Fields("AmountDue") = dblAmountDue
        RS.Fields("AmountPaid") = dblAmountPaid
        RS.Fields("RegularPayment") = dblRegularPayment
        RS.Fields("ExtraPayment") = dblExtraPayment
        
        RS.Fields("EndingBalance") = dblEndingBalance
        RS.Update
        RS.Bookmark = RS.LastModified
        
        dblBeginningBalance = dblEndingBalance
         
        Next
         
          'When PMTN = Form_Loans!NumberOfPayments, 'rs!EndingBalance <= rs!AmountDue, so this is_
         'the last payment for this loan!
         
        If PMTN = Form_Loans!NumberOfPayments Then
         
        RS.AddNew
        RS.Fields("LoanID") = Form_Loans.LoanID
        RS.Fields("PaymentNumber") = PMTN
        RS.Fields("DueDate") = datDueDate
            datDueDate = DateAdd("m", 1, datDueDate)
        RS.Fields("BeginningBalance") = dblBeginningBalance
        RS.Fields("AmountDue") = dblAmountDue
        RS.Fields("AmountPaid") = dblAmountPaid
        RS.Fields("RegularPayment") = dblRegularPayment
        RS.Fields("ExtraPayment") = dblExtraPayment
        RS.Fields("EndingBalance") = dblEndingBalance
            RS!EndingBalance = 0
        RS.Update
        RS.Bookmark = RS.LastModified
         
        Me.Recalc
        
        End If
        
    End If

btnRepaymentSchedule_Click_Exit:
    On Error Resume Next
    On Error GoTo 0
    
    RS.Close            'Close opened rs
    Set RS = Nothing    'Deassigns rs & db
    Set db = Nothing
    Exit Sub

btnRepaymentSchedule_Click_Error:
    'Error Handler here
    Resume btnRepaymentSchedule_Click_Exit
End Sub

Please find attached an image and the DB in MS Office 2000 format.

[Dal Jeanis]

Wow. Blast from the past. Back in the early 80's a friend of mine developed TValue, one of the first PC-based loan amortization applications. A few years after that, I spent five years rising from programmer to programming manager of a small data processing shop in the mortgage industry.

Your application has a very simplistic concept of loan calculations. Do your loan contracts really allow/require you to pretend that all payments actually arrived on the date they were supposed to?

Also, is your "interest" precalculated into the principal (ie taken as a fee) or is it accrued on the remaining balance? You don't currently have any code that calculates interest.

It would be more realistic, more typical, and better accounting practice, to have a transaction file that records the dates that payments were actually received, and how those payments were broken up in terms of interest, principal and late fees, giving a remaining principal balance.

Each new payment would cause a calculation of the interest accrued as/of the payment date -- for example 10% times the prior principal amount times 29 days since the last payment divided by 365.25 days in a year -- and take that amount as interest and apply the rest to reduce the principal balance at the date of payment.

When calculating the amortization schedule, you would do the same process, assuming each payment came in on the proper date. The amount of the final payment would be the next-to-last principal balance plus one month's interest.

The above is not the only possible calculation, though. There are different calcs based on whether interest is being paid in advance or arrears, based on whether the interest is based on 365, 365.25, or 366 day year, or whether the interest is calculated assuming a month is 1/12 of a year. You have to review the actual loan agreement to know what the proper calculation method is.

Nevertheless, here's the answer to one of your questions:

4.I noticed that the schedule table stores only the active member even if the member id is changed and schedule generated. Please how can I generate and store every generated schedule in the schedule table.

My first observation is that you seem to be actually creating permanent "records" of future payments due. Normally, an amortization schedule (for a mortgage or consumer loan) would be created once a year, and sent as a report, but the future data wouldn't be stored.

But then again, I see that 12 months is your current max length, and the entire "schedules" table is deleted every time you do another schedule, so "Schedules" is really just being used as a work table at the moment.

Me, I'd limit the recordset in use to the particular loan being looked at. No sense deleting all data in the schedules table for every loan each time you create a schedule for any other loan.

Create a new query called qrySchedule using this SQL code:

SELECT Schedules.ScheduleID, Schedules.LoanID, Schedules.PaymentNumber, Schedules.DueDate, Schedules.BeginningBalance, Schedules.AmountDue, Schedules.AmountPaid, Schedules.RegularPayment, Schedules.ExtraPayment, Schedules.EndingBalance
FROM Schedules
WHERE (((Schedules.LoanID)=[TmpLoanID]));

Now comment out this line of code:

Set RS = db.OpenRecordset("Schedules", dbOpenDynaset)

and change that section to read like this:

    Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim RS As DAO.Recordset
    Set db = CurrentDb
   Set qdf = db.QueryDefs("qrySchedules")
    qdf.Parameters("TmpLoanID") = Me.Parent.LoanID.Value
    Set RS = qdf.OpenRecordset()
' Set RS = db.OpenRecordset("Schedules", dbOpenDynaset)

Add an end-if after the section that deletes the recordset

    
       While Not RS.EOF
           RS.MoveFirst
           RS.Delete
           RS.MoveNext
        Wend
    
    End If

and comment out the end-if after the recalc

 
       Me.Recalc

       End If

   ' End If

That will allow one set of schedules per loan.

I notice that the Loan form doesn't allow choice of Loan number, so you are limited to one loan per member. You'll have to adjust the macro code in the member dropdown box, and maybe change the loanid to a dropdownbox that is loaded when the member id is chosen.

[jmeni]

Wow. Blast from the past. Back in the early 80's a friend of mine ... A few years after that, I spent five years rising from programmer to programming manager.

Interesting, I'm honoured to have a Guru as my Guide, hope the train is not moving backwards, lols! I'm just a beginner and this is actually my first attempt at developing an access application!

Your application has a very simplistic concept of loan calculations. Do your loan contracts really allow/require you to pretend that all payments actually arrived on the date they were supposed to?

Yes, payments arrive at the end of the month, because it's limited to a set of members with payments deducted at salary source, except the optional extra payments.

Also, is your "interest" precalculated into the principal (ie taken as a fee) or is it accrued on the remaining balance? You don't currently have any code that calculates interest.

It would be more realistic, more typical, and better accounting practice, to have a transaction file that records the dates that payments were actually received, and how those payments were broken up in terms of interest, principal and late fees, giving a remaining principal balance.

a) Yes, interest is calculated upfront and deducted from loan amount(actual amount becomes LoanAmount - Interest) and then monthly payment is calculated as LoanAmount/ NumberOfPayments.

b)Initially, I had payments broken down in the schedules into interest and principal, though no late fees as earlier mentioned of payment process but was informed that's interest is upfront and hence the modifications.

Each new payment would cause a calculation of the interest accrued as/of the payment date -- for example 10% times the prior principal amount times 29 days since the last payment divided by 365.25 days in a year -- and take that amount as interest and apply the rest to reduce the principal balance at the date of payment.

This would have been the case if Interest is not upfront.

When calculating the amortization schedule, you would do the same process, assuming each payment came in on the proper date. The amount of the final payment would be the next-to-last principal balance plus one month's interest.

What applies now is principal minus AmountPaid(AmountDue(Regular)+Extra) and becomes old bal, which is then assigned as new bal (principal bal) and the process is repeated till the loan is amortized.

The above is not the only possible calculation, though. There are different calcs based on whether interest is being paid in advance or arrears, based on whether the interest is based on 365, 365.25, or 366 day year, or whether the interest is calculated assuming a month is 1/12 of a year. You have to review the actual loan agreement to know what the proper calculation method is.

Sure, in this case it's based on a monthly basis and the year is defined as the NumberOfPayments (12 or 9 or 6 or 3 months year)

Nevertheless, here's the answer to one of your questions:
My first observation is that you seem to be actually creating permanent "records" of future payments due. Normally, an amortization schedule (for a mortgage or consumer loan) would be created once a year, and sent as a report, but the future data wouldn't be stored.

Please advise, as earlier mentioned I'm a novice: the one time payment is what results from the initial btnRepayment Schedule_click but when there is an extra payment or a balloon payment, the schedule becomes altered. The storing of the future data is due to the optional extra payments, what's the best way to handle this(I was wondering if I'll removed the ScheduleID, b'cos assuming an initial 12 payments were generated(Member 1), 6 payments(Member 2), and 12 payments (Member 3) etc and then either 2,3,4,5,6 extra payments are made by Member 1 thereby reducing the initial 12 rows, how will this be reflected in the fldScheduleID (autonumber) of the tblSchedule) as earlier saved(30 records)

But then again, I see that 12 months is your current max length, and the entire "schedules" table is deleted every time you do another schedule, so "Schedules" is really just being used as a work table at the moment.

Me, I'd limit the recordset in use to the particular loan being looked at. No sense deleting all data in the schedules table for every loan each time you create a schedule for any other loan.

Create a new query called qrySchedule using this SQL code: ...
That will allow one set of schedules per loan.

Thanks,this is what I want! I will implement and give you feedback

I notice that the Loan form doesn't allow choice of Loan number, so you are limited to one loan per member. You'll have to adjust the macro code in the member dropdown box, and maybe change the loanid to a dropdownbox that is loaded when the member id is chosen.

Yes, it's one member to a loan, multiple loans are not expressly allowed but a little tweaking if a second loan is required(apply for 2nd loan, pay off 1st loan bal, then give you the remaining amount as the 2nd loan)!
I'm so very greatly for your time!
Jmeni

[Dal Jeanis]

Yes, interest is calculated upfront

Okay, so you have a mutual loan society based on payday loans, with interest prepaid...the equivalent of a tanemoshi or sous-sous. Got it. Hopefully you have also agreed on terms regarding what happens if someone loses their job, or otherwise fails to pay.

Also, technically, that means you can just multiply the payment amount by the discount rate (the interest built in) to determine the portion of the payment that is interest (profit to the group) as opposed to repayment of group capital. Might or might not be significant depending on the structure of your group.

one member to a loan

You probably need to change that code anyway. Even if a member can have only one active loan, the member can have multiple past/inactive and one present/active, and the current code in the member selection box will only bring up their earliest loan, active or not. Best to have selecting the member load the LoanID box with all their loans, and then default to bringing up the active one.


OVERALL DESIGN -

I strongly urge you to keep the record of actual payments made in a different place from the "schedule". Actual payments made by a member should NEVER be deleted, or you can imagine the dire results. Likewise, the initial outgoing loan payment should be stored in the transaction file with the recorded incoming payments.

Creating a "schedule" for a loan would consist of copying the recorded payments over to the schedule file, and then calculating the remainder of the future payments.

Let me give it some thought and see if i can suggest an upgrade to the architecture.

[jmeni]

Success on your Recommendation!
Sir, I've implemted the Query to the recordset and this is to inform you that the schedule table now shows the records for the different loan and no longer deletes the entire schedule! Gracias!

Hopefully you have also agreed on terms regarding what happens if someone loses their job, or otherwise fails to pay.

Yes, also there is a monthly contribution scheme in place, so a member has some money from the contribution which can augment the payment in the event of loss of job

You probably need to change that code anyway. Even if a member can have only one active loan, the member can have multiple past/inactive and one present/active, and the current code in the member selection box will only bring up their earliest loan, active or not. Best to have selecting the member load the LoanID box with all their loans, and then default to bringing up the active one.

Yes Sir, most willing and ready for the code change for best practice and also it didn't occur to me to consider the possibility of loans history! Thanks for bringing it to my notice.

OVERALL DESIGN -

I strongly urge you to keep the record of actual payments made in a different place from the "schedule". Actual payments made by a member should NEVER be deleted, or you can imagine the dire results. Likewise, the initial outgoing loan payment should be stored in the transaction file with the recorded incoming payments.

Yes, it's already implemented as such, if you look to the right of the sbfSchedules, you'll see sbfPayments which records the incoming payments, though it's filtered from the tblPayments(2 additional fields: PaymentID and LoanID(FK) from tblLoans) to show only 2 flds

Creating a "schedule" for a loan would consist of copying the recorded payments over to the schedule file, and then calculating the remainder of the future payments.

I'm not too clear on this point, Sir!

Let me give it some thought and see if i can suggest an upgrade to the architecture.

Thanks for all your assistance!
Jmeni

[Dal Jeanis]

1) Switch all money amounts to type "currency", not double. Access doesn't handle money well unless the type is currency.

2) If any payments have been made, change all significant fields on the loan form to non-updatable. (Loan amount, interest percent, etc)

3) Create a qryPayments, just like the qrySchedules. Open and read this recordset this to copy the payments over to the schedule before continuing to calculate the future schedule.

SELECT Payments.PaymentID, Payments.LoanID, Payments.AmountPaid, Payments.PaidDate
FROM Payments
WHERE (((Payments.LoanID)=[TmpLoanID]))
ORDER BY Payments.PaidDate;

4) For verifying the payment amount, don't just check for Null, do a reasonability check. If the number is less than 1/20th the balance, it isn't getting paid off in 12 months. Also, if it's bad, remember to "exit sub" or "go to exit" rather than still running through the calculation code.

5) Speaking of exit sub, you have to actually read the response from a msgbox and do something about it, otherwise you'll just drop through the code and do what you were going to do anyway. The typical code for that looks like this:

Dim LResponse As Integer
LResponse = MsgBox("Do you wish to continue?", vbYesNo, "Continue")
If LResponse = vbYes Then
   {...statements...}
Else
   {...statements...}
End If

6) For calculating future scheduled payments:
A) Instead of looping a given number of times related to the expected number of payments, loop until the ending balance goes to zero.
B) Show AmountDue but don't show AmountPaid and don't break out the amount paid regular vs extra - that way you can visually see which ones are not really payments.

[jmeni]

Dal Jeanis,
Thanks! Thanks!! and Thanks!!!
Regards:
(1) done;
(2) not too clear on the implementation!
(3) done
(4)Done and understand the rationale but do not know the choice of 1/20th!
(5)Noted, wasn't too sure how to go about it but now it's clear!
(6)Thanks! It's a learning curve for me!

Now, there are TWO key issues(EXTRA payments and Updating next Record when the fldAmountPaid Not (IsNull or Zero)):
1. How to assign the value of the fldEndingBalance to be the Value of the fldBeginningBalance through the recordset when Payments are added by clicking on btnAddPayments (NB: without extra payments, this is no issue)
Effort:
I have tried implementing a Microsoft Support solution without success
Please find below the link to the Microsoft site(issue of previous value in next record) and also another link which discusses the issue of EXTRA payments:
Link 1: http://support.microsoft.com/default...b;en-us;210504
Link 2: http://mobile.experts-exchange.com/S...-Payments.html

2. Also when I click on the btnAddPayments, I want a check the FieldValue of fldAmountPaid in both sbf's and if it's Null or Zero, then the update of the Inputbox is implemented else the next record, which would be Null Or Zero is updated irrespective of the position of the cursor!
Thanks

[Dal Jeanis]

1/20th is an arbitrary number. I didn't use 1/12 because rounding may sometimes go the wrong way.

In my opinion, you don't update or predict future payments ever. You just track the payments you receive, and allocate them to their regular and extra components. A payment record is just that - a record of what payment you received. The future payments are always expected to be standard, except the last one with is full payoff.