Neverending Loop in Amortization Database - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Neverending Loop in Amortization Database

Arlyn Henken

6 months ago

I am working through the amortization seminar and have hit a problem I cannot figure out. I am in lesson 5, where we write the code to do the recalc of payments, but as soon as I enter a payment in the AmountPaid field of the Schedule, it starts generating the new schedule based on the entered payment, but it does not stop at what should be the last scheduled payment. It continues on, just putting a whole bunch of records with zero values. I have to hit ctrl-break in order to stop the code from running. Has anyone run into this problem?

Here is the code from the Recalc function:

Details

Private Sub DoRecalc()

    On Error Resume Next
    
    Dim BBal As Currency, TotalPrin As Currency, TotalExtra As Currency, Correction As Currency
    BBal = Forms!frmLoans!LoanAmount
    
    Me.Requery
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblSchedule SET Principal=0 WHERE LoanID=" & Forms!frmLoans!LoanID
    
    DoCmd.GoToControl "PaymentNumber"
    DoCmd.GoToRecord , , acFirst
    While Not IsNull(PaymentNumber)
        BeginningBalance = BBal
        If BeginningBalance = 0 Then
            AmountDue = 0
            Principal = 0
            Interest = 0
            EndingBalance = 0
        Else
            AmountDue = Forms!frmLoans!PaymentAmount
            Interest = Round(BeginningBalance * (Forms!frmLoans!InterestRate / Forms!frmLoans!Frequency.Column(2)), 2)
            Principal = AmountDue - Interest
            If BBal < Forms!frmLoans!PaymentAmount Then
                EndingBalance = 0
                TotalPrin = DSum("Principal", "tblSchedule", "LoanID=" & Forms!frmLoans!LoanID) + Principal
                TotalExtra = DSum("extrapayment", "tblSchedule", "LoanID=" & Forms!frmLoans!LoanID)
                Correction = Forms!frmLoans!LoanAmount - TotalPrin - ExtraPayment
                AmountDue = AmountDue + Correction
                Principal = Principal + Correction
            Else
                EndingBalance = BeginningBalance - Principal - ExtraPayment
            End If
            BBal = EndingBalance
        End If
        DoCmd.GoToRecord , , acNext
    Wend
    
    Forms!frmLoans.Requery
    DoCmd.SetWarnings True
End Sub

	Kevin Robertson @Reply 6 months ago
I just tested it and it works the way it should. Please post some screenshots highlighting the issue.

	Arlyn Henken OP @Reply 6 months ago

	Arlyn Henken OP @Reply 6 months ago

	Arlyn Henken OP @Reply 6 months ago

Arlyn Henken OP @Reply

6 months ago

In the images I have attached, it shows that somehow an empty record is being added to the end of the recordset for this loan schedule when the schedule is first generated. Also, the images show that after the Recalc function is used, an unlimited number of empty records are generated after the actual schedule is rerun with the payments included. To stop the code from running, I have to press ctrl-break. The code shows what line is highlighted in relation to the error; however, since no error is actually seen, it is difficult to determine why.

Arlyn Henken OP @Reply

6 months ago

Ok, I was able to fix the issue I was having, but now I have a different problem. After building the Payment subform and adding the code to the Schedule subform to add payments, I keep getting an error in the following code I get a runtime error that says there is no field named "PaymentNumber" in the current record (will post a screenshot of the error):

Details

Private Sub cmdAddPayment_Click()



    Dim S As String

    Dim P As Currency

    Dim D As Date

    

    S = InputBox("What is the payment amount?", "Payment", Forms!frmloans!PaymentAmount)

    If S = "" Then Exit Sub

    P = CCur(S)

    If P <= 0 Then Exit Sub

    

    S = Nz(InputBox("What is the date of the payment?", "Date", Date), "")

    If S = "" Then Exit Sub

    D = CDate(S)

    If D < #1/1/1900# Then Exit Sub

    If D > #1/1/2200# Then Exit Sub

    

    DoCmd.SetWarnings False

    DoCmd.RunSQL "INSERT INTO tblPayments (LoanID, Amount, Paiddate) " & "VALUES (" & Forms!frmloans!LoanID & ", " & P & ", #" & D & "#);"

    DoCmd.SetWarnings True

    Me.Requery

    Forms!frmloans.Requery

    

    DoCmd.GoToControl "PaymentNumber"

    DoCmd.GoToRecord , , acFirst

    While RegularPayment >= AmountDue

        DoCmd.GoToRecord , , acNext

    Wend

    If IsNull(PaymentNumber) Then

        MsgBox "No payment to add to!"

        Exit Sub

    End If

    AmountPaid = AmountPaid + P

    AmountPaid_AfterUpdate

    DoRecalc

End Sub

	Arlyn Henken OP @Reply 6 months ago

	Arlyn Henken OP @Reply 6 months ago
I figured out the last problem. Apparently, when I was requering the focus was being set on the main form. I had to put another docmd.gotocontrol to put the focus back on the subform, then put the focus on the field in question.

	Richard Rost @Reply 6 months ago
I'm not in the office right now, but the first question I always have is "does this happen in my database that you can download?" If not, we just have to figure out what's different in yours.

Arlyn Henken OP @Reply

6 months ago

I was never able to open the downloadable completed template due to the wonderful security features within Access. But I was able to get it to work by making some small modifications or additions to the code.

Something I have been experiencing is needing to do some compact and repairs periodically due to some problems I am having with my main form. This has nothing to do with what I am learning through your seminar.

Once I am finished with the seminar I may contact you directly with some recommendations that may be helpful using this for accounting purposes.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View