Advanced Monthly Billing

[Rich1968]

Hello All!

I am trying to to create entries into a table called tblinvoice for auto loans. If I have a 12 month loan then the re will be 12 entries with the due date and payment amount due. etc. I am using code I got here and I have part of it to work however ther are always problems. My brain is on overload so I need some objective help. Here's the code.

Private Sub Command100_Click()

Dim NL, TB, Fmt, FVal, pval, APR, totpmts, paytype, Payment, Msg, MakeChart, P, I, response
Dim numscrn, j As Integer, PERIOD As Integer

Const ENDPERIOD = 0, BEGINPERIOD = 1 ' When payments are made.
NL = Chr(13) & Chr(10) ' Define newline.
TB = Chr(9) ' Define tab.
Fmt = "###,###,##0.00" ' Define money format.
FVal = 0 ' Usually 0 for a loan.
pval = Forms!frmLoanCalc![Amount Financed]
APR = Forms!frmLoanCalc!APR
LoanNumber = Forms!frmLoanCalc![Loan Number]
InvoiceDate = Forms!frmLoanCalc![First Payment Due]

'If APR > 1 Then APR = APR / 100 ' Ensure proper form.

totpmts = Forms!frmLoanCalc![Payment Period]

'paytype = "1"
'If paytype = "1" Then paytype = BEGINPERIOD Else paytype = ENDPERIOD
'Payment = Abs(-Pmt(APR / 12, totpmts, pval, FVal, paytype))

MakeChart = vbYes ' See if chart is desired.
If MakeChart <> vbNo Then

DoCmd.SetWarnings False

'For PERIOD = 1 To totpmts
'For PERIOD = -1 To totpmts - 2

For PERIOD = 1 To Forms!frmLoanCalc![Payment Period]

If PERIOD > totpmts Then Exit For

'P = PPmt(APR / 12, PERIOD, totpmts, -pval, FVal, paytype)
'P = (Int((P + 0.005) * 100) / 100) ' Round principal.
'I = Payment - P
'I = (Int((I + 0.005) * 100) / 100) ' Round interest.
'Payment = Pmt(APR / 12, [Payment Period], -[Amount Financed], 0, 1)
Payment = Abs(-Pmt(APR / 12, totpmts, pval, FVal, paytype))
Invoice_Date = DateAdd("m", Forms!frmLoanCalc![Payment Period], Forms!frmLoanCalc![First Payment Due])
LoanNumber = Forms!frmLoanCalc![Loan Number]

DoCmd.RunSQL "Insert into tblInvoices (LoanNumber,Period,InvoiceDate,Payment) values(" & LoanNumber & "," & PERIOD & "," & Payment & "," & InvoiceDate & ")"

Next PERIOD

DoCmd.RunSQL "update tblInvoices set payment=round(payment,2)"

DoCmd.SetWarnings True
DoCmd.OpenTable "tblInvoices"
End If

End Sub

As you can see I have commented out some parts of this.

Here's the resulting table.

Invoice ID Loan Number Period Invoice Date Payment Discription
1327 8 1 4/2/1900 11:43:42 PM $0.00 Installment Loan
1328 8 2 4/2/1900 11:43:42 PM $0.00 Installment Loan
1329 8 3 4/2/1900 11:43:42 PM $0.00 Installment Loan
1330 8 4 4/2/1900 11:43:42 PM $0.00 Installment Loan
1331 8 5 4/2/1900 11:43:42 PM $0.00 Installment Loan
1332 8 6 4/2/1900 11:43:42 PM $0.00 Installment Loan
1333 8 7 4/2/1900 11:43:42 PM $0.00 Installment Loan
1334 8 8 4/2/1900 11:43:42 PM $0.00 Installment Loan
1335 8 9 4/2/1900 11:43:42 PM $0.00 Installment Loan
1336 8 10 4/2/1900 11:43:42 PM $0.00 Installment Loan
1337 8 11 4/2/1900 11:43:42 PM $0.00 Installment Loan
1338 8 12 4/2/1900 11:43:42 PM $0.00 Installment Loan
1339 8 13 4/2/1900 11:43:42 PM $0.00 Installment Loan
1340 8 14 4/2/1900 11:43:42 PM $0.00 Installment Loan
1341 8 15 4/2/1900 11:43:42 PM $0.00 Installment Loan
1342 8 16 4/2/1900 11:43:42 PM $0.00 Installment Loan
1343 8 17 4/2/1900 11:43:42 PM $0.00 Installment Loan
1344 8 18 4/2/1900 11:43:42 PM $0.00 Installment Loan
1345 8 19 4/2/1900 11:43:42 PM $0.00 Installment Loan
1346 8 20 4/2/1900 11:43:42 PM $0.00 Installment Loan
1347 8 21 4/2/1900 11:43:42 PM $0.00 Installment Loan
1348 8 22 4/2/1900 11:43:42 PM $0.00 Installment Loan
1349 8 23 4/2/1900 11:43:42 PM $0.00 Installment Loan
1350 8 24 4/2/1900 11:43:42 PM $0.00 Installment Loan

I can't seem to get The correct invoice date or the correct payment to write.
Any advice would be appriciated.

Many Thanks
Rich De Gray

 

[goober01]

i realize this is old, but did you ever figure it out.

 

[highandwild]

Just right for using a cartesian product and an append query but maybe it's been solved by now.