Code Issue

[dref]

Can anyone help me with this code. Am not an expert but am trying to manipulate this code to compute a loan.
I have a field on the form called NumberofPayments which is not taken care of in this code. Originally it was designed to use years but i prefer to use months. Please help because when i use forexample numberofpayment=8, it schedules giving me 7 months in the schedule.


If MsgBox("Are you SURE? This will erase all payment data and create a new schedule?", vbYesNoCancel) <> vbYes Then Exit Sub

If IsNull(Forms!LoanF!PaymentAmount) Then
MsgBox "You need to calculate the payment amount first"
Exit Sub
End If

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM ScheduleT WHERE LoanID=" & Forms!LoanF!LoanID)
Me.Requery
DoCmd.GoToControl "PaymentNumber"

Dim BBal As Currency
Dim Counter As Long
Dim CurDate As Date
Dim X As Integer


Dim TotalPrin As Currency, Correction As Currency

BBal = Forms!LoanF!LoanAmount
Counter = 1

CurDate = Forms!LoanF![txtOneMonth]
While BBal > 0
DoCmd.GoToRecord , , acNewRec
PaymentNumber = Counter
Counter = Counter + 1
DueDate = CurDate
CurDate = DateAdd("m", 1, CurDate)
AmountPaid = 0
RegularPayment = 0
ExtraPayment = 0
BeginningBalance = BBal
'AmountDue = Forms!LoanF!PaymentAmount
AmountDue = Forms!LoanF!PaymentAmount + (Forms!LoanF!Interest)
Interest = Round(BeginningBalance * (Forms!LoanF!InterestRate), 2)

Principal = AmountDue - Interest
If BBal < Forms!LoanF!PaymentAmount Then
EndingBalance = 0
TotalPrin = DSum("Principal", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID) + Principal
Correction = Forms!LoanF!LoanAmount - TotalPrin
AmountDue = AmountDue + Correction
'Principal = Principal + Correction
Else
EndingBalance = BeginningBalance - Principal
End If
BBal = EndingBalance
Wend

Forms!LoanF.Requery

DoCmd.SetWarnings True

End Sub

[June7]

Please post code between CODE tags to retain indentation and readability.

Have you step debugged?

How should NumberOfPayments be utilized?

[Missinglinq]

It's also curious that NumberOfPayments doesn't even appear in any of the posted code!

Linq ;0)>

[RuralGuy]

Here's what the code would have looked like with code tags and indenting:

Public Sub Foo()

   If MsgBox("Are you SURE? This will erase all payment data and create a new schedule?", vbYesNoCancel) <> vbYes Then Exit Sub

   If IsNull(Forms!LoanF!PaymentAmount) Then
      MsgBox "You need to calculate the payment amount first"
      Exit Sub
   End If

   DoCmd.SetWarnings False

   DoCmd.RunSQL ("DELETE * FROM ScheduleT WHERE LoanID=" & Forms!LoanF!LoanID)
   Me.Requery
   DoCmd.GoToControl "PaymentNumber"

   Dim BBal As Currency
   Dim Counter As Long
   Dim CurDate As Date
   Dim X As Integer
   Dim TotalPrin As Currency, Correction As Currency

   BBal = Forms!LoanF!LoanAmount
   Counter = 1

   CurDate = Forms!LoanF![txtOneMonth]
   While BBal > 0
      DoCmd.GoToRecord , , acNewRec
      PaymentNumber = Counter
      Counter = Counter + 1
      DueDate = CurDate
      CurDate = DateAdd("m", 1, CurDate)
      AmountPaid = 0
      RegularPayment = 0
      ExtraPayment = 0
      BeginningBalance = BBal
      'AmountDue = Forms!LoanF!PaymentAmount
      AmountDue = Forms!LoanF!PaymentAmount + (Forms!LoanF!Interest)
      Interest = Round(BeginningBalance * (Forms!LoanF!InterestRate), 2)

      Principal = AmountDue - Interest
      If BBal < Forms!LoanF!PaymentAmount Then
         EndingBalance = 0
         TotalPrin = DSum("Principal", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID) + Principal
         Correction = Forms!LoanF!LoanAmount - TotalPrin
         AmountDue = AmountDue + Correction
         'Principal = Principal + Correction
      Else
         EndingBalance = BeginningBalance - Principal
      End If
      BBal = EndingBalance
   Wend

   Forms!LoanF.Requery

   DoCmd.SetWarnings True

End Sub