Hello Access Gurus,
Please I seriously need some guidance regards recordsets; I am still reading other references but I'm in a fix!
This is my situation:
I am working on a Loan DB and have tblLoans(ID|Amount|InterestRate|StartDate|NumberOfPayments|IsActive), tblschedules(PMT#|DueDate|BeginBalance|AmountDue|AmountPaid|Regular|Extra|Principal|Interest|), tblPayments EndBalance|Cum.Interest)with forms already created for each table! The problem I'm having is I want the to create a loan amortization such that after entering the LoanAmount, InterestRate, and the NumberOfPayments, I want the MonthlyPayment button on clicking to calculate based on a Custom Formula(Simple Interest), I don't want to use the built-in PMT function(Compound). Secondly, after the MonthlyPayment Calc, I want the Create Repayment Schedule button to show the payment details and I understand this is where Recordset comes in! Please i seriously need all assistance as I'm a newbie to Access and VBA!
Hi All,
Progress Report: Entering the Homestretch!
Please I need assistance to wrap this db up:
Presently, I can calculate the PMT (not using built-in function) giving LoanAmount, InterestRate and NumPayments on frmLoans and have been able to access the values declared in frmLoans in sbfSchedules but my challenge is in creating the recordsets.
Issue:
The issue lies with generating the recordsets showing the number of payments to be made to amortize the loan and I'm getting run-time error 3421:data type conversion error(error number 3421: Microsoft is saying it's expected in DAO recordsets, ref:support(dot)microsoft(dot)com/kb/172101: Please, where do I input this code? ).
Please find attached the db in .mdb format and a code snippet!
Run-time Error:
The error is underlined in the code below, pls access gurus look through the db and assist with generating the recordset. Stacked Here
Thoughts!: if I set the Other Property of the sbfSchedules control, instead I get a Null error.
Solution desired:
1.How to assign dblEndingBalance As dblBeginingBalance after the 1st pass through the recordset? (Will a Dlookup work and how?)
2.Normally, the dblPrincipalPaid is calculated i.e dblPrincipalPaid = dblAmountDue - dblInterestRate, but during the last pass through the recordset, the dblEndingBalance is normally less than the dblAmountDue, hence I want a check such as
If dblEndingBalance <= dblAmountDue Then
dblPrincipalPaid = dblEndingBalance and
dblInterestPaid = dblAmountDue - dblEndingBalance
3. How to initialize dblTotalInterest such that:
1st Pass thro rs_
dblTotalInterest = dblInterestPaid Then subsequently,
dblTotalInterest = dblInterestPaid + dblTotalInterest
4. In the sbfPayments, to add the amount paid on the loan, when the btnAddPayment is clicked, I noticed that it only updates the sbfSchedules "Amount Paid" field and sbfPayments "txtAmountPaid" and "txtDate" fields only on the 1st instance, subsequently, it updates only the field with focus, How can I automatically set the focus after the 1st update to the next record?
Please how can these be integrated in the code.
Code for sbfSchedules
Code:
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 intPaymentNumber As Integer
Dim datDueDate As Date
Dim dblBeginningBalance As Double
Dim dblAmountDue As Double
Dim dblAmountPaid As Double
Dim dblExtraPayment As Double
Dim dblPrincipalPaid As Double
Dim dblInterestPaid As Double
Dim dblEndingBalance As Double
Dim dblTotalInterest As Double
Dim dblMonthlyRate As Double
Dim datPaidDate As Date
'Initialize dblTotalInterest
dblTotalInterest = 0
'Assign variables(from loan form)
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
While Not rs.EOF
rs.Delete
rs.MoveNext
Wend
Else
'Loop for each month
For intPaymentNumber = 1 To Form_Loans!NumberOfPayments
'Calculate the relevant figures (BeginBalance = Principal = oldbalance | EndBalance = newbalance)
dblInterestPaid = Round(dblBeginningBalance * dblMonthlyRate, 2)
dblPrincipalPaid = Round(dblAmountDue - dblInterestPaid, 2)
dblEndingBalance = Round(dblBeginningBalance - dblPrincipalPaid, 2)
dblTotalInterest = Round(dblInterestPaid + dblTotalInterest, 2)
rs.AddNew
rs.Fields(intPaymentNumber) = intPaymentNumber
[u] rs.Fields(datDueDate) = datDueDate[/u]
datDueDate = DateAdd("m", 1, datDueDate)***
rs.Fields(dblBeginningBalance) = dblBeginningBalance
rs.Fields(dblAmountDue) = dblAmountDue
rs.Fields(dblAmountPaid) = dblAmountPaid
rs.Fields(dblExtraPayment) = dblExtraPayment
rs.Fields(dblPrincipalPaid) = dblPrincipalPaid
rs.Fields(dblInterestPaid) = dblInterestPaid
rs.Fields(dblEndingBalance) = dblEndingBalance
rs.Fields(dblTotalInterest) = dblTotalInterest
rs.Update
rs.Bookmark = rs.LastModified
Next
'Update BeginningBalance for the next pass through the For Loop
dblBeginningBalance = dblEndingBalance
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
Sub btnAddPayment_Click
On Error GoTo btnAddPayment_Click_Error
Dim dblAmountPaid As Double
Dim datPaidDate As Double
dblAmountPaid = InputBox(“What is the Payment Amount“, "Payment", Form_Loans.txtPMT)
datPaidDate = InputBox("What is the Payment Date", "Date", Date)
Me!txtAmountPaid = dblAmountPaid
Me!txtPaidDate = datPaidDate
btnAddPayment_Click_Exit:
Exit Sub
btnAddPayment_Click_Error:
Resume btnAddPayment_Click_Exit
End Sub
Code for Loan Form
Code:
Private Sub btnMonthlyPayment_Click()
On Error GoTo btnMonthlyPayment_Click_Error
'Declare variables
Dim dblLoanAmount As Double
Dim dblInterestRate As Double
Dim dblMonthlyRate As Double
Dim dblMonthlyPayment As Double
Dim intNumPayments As Integer
Dim datStartDate As Date
Dim strFrequency As String
'Check for inputs on form
If IsNull(Me!txtAmount) Then
MsgBox "Please enter the LoanAmount to Continue", vbOKOnly
Exit Sub
End If
If IsNull(Me!txtRate) Then
MsgBox "Please enter an InterestRate to Continue ", vbOKOnly
Exit Sub
End If
If IsNull(Me!txtMonths) Then
MsgBox "Please enter the NumPayments to Continue ", vbOKOnly
Exit Sub
End If
If IsNull(Me!txtDate) Then
MsgBox "Please enter the StartDate to Continue ", vbOKOnly
Exit Sub
End If
'Convert input values to numeric values
dblLoanAmount = CDbl(Me!txtAmount)
dblInterestRate = CDbl(Me!txtRate)
IntNumPayments = CInt(Me!txtMonths)
datStartDate =CDate(Me!txtDate)
'Calculate Monthly Payment
dblMonthlyPayment = (dblLoanAmount / intNumPayments) * (1 + dblInterestRate)
'Assigns MonthlyPayment
Me!txtPMT = dblMonthlyPayment
btnMonthlyPayment_Click_Exit:
Exit Sub
btnMonthlyPayment_Click_Error:
'Error handler here
Resume btnMonthlyPayment_Click_Exit
End Sub
Sub txtAmount_LostFocus()
txtPMT = ""
End Sub
Sub txtDate_LostFocus()
txtPMT = ""
End Sub
Sub txtFrequency_LostFocus()
txtPMT = ""
End Sub
Sub txtMonths_LostFocus()
txtPMT = ""
End Sub
Sub txtRate_LostFocus()
txtPMT = ""
End Sub
Code for sbfPayments
Code:
Dim dblAmountPaid As Double
Dim datPaidDate As Date
dblAmountPaid = Form_Schedules.txtAmountPaid
datPaidDate = Form_Schedules.txtPaidDate(visible = false)
Thanks for taking time to look at my issue!
I thought since recordsets are involved, it should be in Modules & VBA forum, or should it be in the Forms Forum, please advice accordingly, so I can ask a moderator to move my post to the appropriate forum or delete and repost to the appropriate forum.
