By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

How to assign a previous record value to next record in DAO recordset

P: 9
I am working on a Loan Amortization using Access 2000 format and have generated a Repayment Schedule. I have some tables, queries and forms already created and attached here.
I will base my questions on the image attached.
Challenges:
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)

Thanks
Jmeni

[z{moderator's eddits}]
[z:{ Your Zipped ACC2000 file will most likely not be opened. Most of use either can not (as in my case) or will not open such files due to various IT policies or simple safe computing practices.}]
[z:{ Please note, ACC2000 is extremly old in computer/software aging. In fact, I have no real way to review your files and so much has changed going into ACC2010 that there may not be alot of us left that can help with that version. I highly recomend that you upgrade to at least ACC2003}]
[z:{ Keep in mind that in ACC2000 that the DAO reference library was not the default}]




From the plain text file:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btnMonthlyPayment_Click()
  5.     On Error GoTo btnMonthlyPayment_Click_Error
  6.  
  7.     Dim ccyLoanAmount As Currency
  8.     Dim dblInterestRate As Double
  9.     Dim ccyInterestPaid As Currency
  10.     Dim ccyPayment As Currency
  11.     Dim ccyMonthlyPayment As Currency
  12.     Dim intNumPayments As Integer
  13.     Dim datStartDate As Date
  14.     Dim strFrequency As String
  15.  
  16.     'Check for inputs on form
  17.     If IsNull(Me!txtAmount) Then
  18.         MsgBox "Please enter the LoanAmount to Continue", vbOKOnly
  19.         Exit Sub
  20.     End If
  21.  
  22.     If IsNull(Me!txtRate) Then
  23.         MsgBox "Please enter an InterestRate to Continue ", vbOKOnly
  24.         Exit Sub
  25.     End If
  26.  
  27.     If IsNull(Me!txtMonths) Then
  28.         MsgBox "Please enter the NumPayments to Continue ", vbOKOnly
  29.         Exit Sub
  30.     End If
  31.  
  32.     If IsNull(Me!txtDate) Then
  33.         MsgBox "Please enter the StartDate to Continue ", vbOKOnly
  34.         Exit Sub
  35.     End If
  36.  
  37.     'Convert input values to numeric values
  38.     ccyLoanAmount = CCur(Me!txtAmount)
  39.     dblInterestRate = CDbl(Me!txtRate)
  40.     intNumPayments = CInt(Me!txtMonths)
  41.     datStartDate = CDate(Me!txtDate)
  42.     txtInterest = Round(ccyLoanAmount * dblInterestRate, 2)
  43.  
  44.     'Call Payment Function
  45.     If intNumPayments = 0 Then
  46.         'if no NumMonths, then loan must be repaid immediately
  47.         ccyPayment = ccyLoanAmount
  48.     End If
  49.  
  50.     If Not IsNull(dblInterestRate) Then
  51.         'if loan has no interest then just repay the principal overtime_
  52.         'Interest is paid upfront
  53.          ccyPayment = ccyLoanAmount / intNumPayments
  54.     'Else
  55.     'Otherwise use the formula for determining the monthly payment
  56.     'ccyPayment = (ccyLoanAmount / intNumPayments) * (1 + dblInterestRate)
  57.     End If
  58.  
  59.     'round the monthly payment if there are fractions
  60.     ccyMonthlyPayment = Math.Round(ccyPayment + 0.005, 2)
  61.  
  62.     'Assigns Monthly Payment
  63.     Me!txtPMT = ccyMonthlyPayment
  64.  
  65. btnMonthlyPayment_Click_Exit:
  66.     Exit Sub
  67.  
  68. btnMonthlyPayment_Click_Error:
  69.     'Error handler here
  70.     Resume btnMonthlyPayment_Click_Exit
  71. End Sub
  72. Sub txtAmount_LostFocus()
  73.     Me!txtPMT = ""
  74.     Me!txtInterest = ""
  75. End Sub
  76. Sub txtDate_LostFocus()
  77.     Me!txtPMT = ""
  78. End Sub
  79. Sub txtMonths_LostFocus()
  80.     Me!txtPMT = ""
  81. End Sub
  82. Sub txtRate_LostFocus()
  83.     Me!txtPMT = ""
  84.     Me!txtInterest = ""
  85. End Sub
  86.  
Code for sbfSchedules
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub btnAddPayment_Click()
  5.    On Error GoTo btnAddPayment_Click_Error
  6.  
  7.     Dim ccyAmountPaid As Currency
  8.     Dim datPaidDate As Date
  9.  
  10.     Dim RS As DAO.Recordset
  11.     Set RS = CurrentDb.OpenRecordset("Schedules", dbOpenDynaset)
  12.  
  13.     ccyAmountPaid = InputBox("What is the Payment Amount?", "Payment", Form_Loans.txtPMT)
  14.     datPaidDate = InputBox("What is the Payment Date?", "Date", Date)
  15.  
  16.     Me.txtAmountPaid = ccyAmountPaid
  17.     Me.txtPaidDate = datPaidDate
  18.     Form_Schedules.txtRegular = Round(Form_Loans.txtPMT, 2)
  19.  
  20.     If Not Nz(ccyAmountPaid) Or IsNull(ccyAmountPaid) Then
  21.         Form_Payments.txtAmountPaid = ccyAmountPaid
  22.         Form_Payments.txtPaidDate = datPaidDate
  23.         Form_Schedules.txtExtra = Round(Form_Schedules.txtAmountPaid - Form_Schedules.txtAmountDue, 0)
  24.         Form_Schedules.txtEndingBalance = Round((Me.BeginningBalance) - _
  25.         (Me.txtAmountDue) - (Me.txtExtra), 2)
  26.  
  27.         If txtEndingBalance <> 0 Then
  28.         RS.Fields("BeginningBalance") = txtBeginningBalance
  29.         RS.Bookmark = RS.LastModified
  30.         txtBeginningBalance = txtEndingBalance
  31.  
  32.         Me.Recalc
  33.         Me.Refresh
  34.         End If
  35.     Else
  36.  
  37.     RS.MoveNext
  38.         RS.Close
  39.         Set RS = Nothing
  40.     End If
  41.  
  42. btnAddPayment_Click_Exit:
  43.     Exit Sub
  44.  
  45. btnAddPayment_Click_Error:
  46.     Resume btnAddPayment_Click_Exit
  47.  
  48. End Sub
  49. Public Sub btnRepaymentSchedule_Click()
  50.     On Error GoTo btnRepaymentSchedule_Click_Error
  51.  
  52.    'Declare database and recordset variables
  53.     Dim db As DAO.Database
  54.     Dim qdf As DAO.QueryDef
  55.     Dim qdfP As DAO.QueryDef
  56.     Dim RS As DAO.Recordset
  57.     Dim RSP As DAO.Recordset
  58.  
  59.     'Declare variables & Calculate Repayment Schedule
  60.     Dim intYNC As Integer
  61.     Dim intLoanID As Integer
  62.     Dim PMTN As Integer
  63.     Dim PMTX As Integer
  64.     Dim datDueDate As Date
  65.     Dim datPaidDate As Date
  66.     Dim datLastDate As Date
  67.     Dim ccyBeginningBalance As Currency
  68.     Dim ccyAmountDue As Currency
  69.     Dim ccyAmountPaid As Currency
  70.     Dim ccyRegularPayment As Currency
  71.     Dim ccyExtraPayment As Currency
  72.     Dim ccyEndingBalance As Currency
  73.  
  74.     'If payment is missing or invalidly low, flag the error
  75.     'otherwise use the payment amount from the loan form
  76.     If IsNull(Form_Loans!txtPMT) Then
  77.         MsgBox "Please calculate Monthly Payment to continue", vbOKOnly
  78.         Exit Sub
  79.     Else
  80.         If Form_Loans.txtPMT < (Form_Loans!LoanAmount / 20) Then
  81.            MsgBox "Please calculate Monthly Payment to continue", vbOKOnly
  82.            Exit Sub
  83.         Else
  84.            ccyAmountDue = Round(Form_Loans.txtPMT, 2)
  85.         End If
  86.     End If
  87.  
  88.     ' check for existing schedules and payments
  89.     Set db = CurrentDb
  90.     Set qdf = db.QueryDefs("qrySchedules")
  91.     qdf.Parameters("TmpLoanID") = Me.Parent.LoanID.Value
  92.     Set RS = qdf.OpenRecordset()
  93.  
  94.     Set qdfP = db.QueryDefs("qryPayments")
  95.     qdfP.Parameters("TmpLoanID") = Me.Parent.LoanID.Value
  96.     Set RSP = qdfP.OpenRecordset()
  97.  
  98.     'test for BOF & EOF
  99.     If RS.RecordCount <> 0 Then
  100.  
  101.         intYNC = MsgBox("Are You SURE? This will ERASE all your schedule DATA, " & _
  102.                "and create a new schedule!", vbYesNoCancel)
  103.         If intYNC = vbNo Or vbCancel Then
  104.            GoTo btnRepaymentSchedule_Click_Exit
  105.         End If
  106.  
  107.         While Not RS.EOF
  108.            RS.MoveFirst
  109.            RS.Delete
  110.            RS.MoveNext
  111.         Wend
  112.  
  113.     Else
  114.  
  115.     'set initial values
  116.     ccyBeginningBalance = Form_Loans!LoanAmount
  117.     ccyEndingBalance = Form_Loans!LoanAmount
  118.     datDueDate = Form_Loans!StartDate
  119.     PMTN = 1
  120.  
  121.     'copy actual payments, if any
  122.     If RSP.RecordCount <> 0 Then
  123.         RSP.MoveFirst
  124.         Do While Not RSP.EOF
  125.  
  126.            If RSP.Fields("AmountPaid") > ccyAmountDue Then
  127.               datDueDate = RSP.Fields("PaidDate")
  128.               ccyAmountPaid = RSP.Fields("AmountPaid")
  129.               ccyRegularPayment = ccyAmountDue
  130.               ccyExtraPayment = ccyAmountPaid - ccyAmountDue
  131.               ccyEndingBalance = Round(ccyBeginningBalance - ccyAmountPaid, 0)
  132.            Else
  133.               datDueDate = RSP.Fields("PaidDate")
  134.               ccyAmountPaid = RSP.Fields("AmountPaid")
  135.               ccyRegularPayment = ccyAmountPaid
  136.               ccyExtraPayment = 0
  137.               ccyEndingBalance = Round(ccyBeginningBalance - ccyAmountPaid, 0)
  138.            End If
  139.  
  140.            RS.AddNew
  141.            RS.Fields("LoanID") = Form_Loans.LoanID
  142.            RS.Fields("PaymentNumber") = PMTN
  143.            RS.Fields("DueDate") = datDueDate
  144.            RS.Fields("BeginningBalance") = ccyBeginningBalance
  145.            RS.Fields("AmountDue") = ccyAmountDue
  146.            RS.Fields("AmountPaid") = ccyAmountPaid
  147.            RS.Fields("RegularPayment") = ccyRegularPayment
  148.            RS.Fields("ExtraPayment") = ccyExtraPayment
  149.            RS.Fields("EndingBalance") = ccyEndingBalance
  150.            RS.Update
  151.            RS.Bookmark = RS.LastModified
  152.  
  153.            'Set up next payment number, default date, and beginning balance
  154.            PMTN = PMTN + 1
  155.            ccyBeginningBalance = ccyEndingBalance
  156.            datDueDate = DateAdd("m", 1, datDueDate)
  157.  
  158.            'Go to next payment record, if any
  159.            RSP.MoveNext
  160.  
  161.         Loop
  162.     End If
  163.     End If
  164. 'above is part one of two.
  165. '***********part 2***********
  166.  
  167. 'Loop for each future month
  168.     Do Until ccyEndingBalance <= 0
  169.  
  170.         If ccyBeginningBalance < ccyAmountDue Then
  171.             ccyAmountDue = ccyBeginningBalance
  172.         End If
  173.  
  174.     'Calculate the relevant figures (BeginBalance = Principal = oldbalance | EndBalance = newbalance)
  175.         ccyEndingBalance = ccyBeginningBalance - ccyAmountDue
  176.  
  177.         RS.AddNew
  178.         RS.Fields("LoanID") = Form_Loans.LoanID
  179.         RS.Fields("PaymentNumber") = PMTN
  180.         RS.Fields("DueDate") = datDueDate
  181.         RS.Fields("BeginningBalance") = ccyBeginningBalance
  182.         RS.Fields("AmountDue") = ccyAmountDue
  183.         RS.Fields("AmountPaid") = 0
  184.         RS.Fields("RegularPayment") = 0
  185.         RS.Fields("ExtraPayment") = 0
  186.         RS.Fields("EndingBalance") = ccyEndingBalance
  187.         RS.Update
  188.         RS.Bookmark = RS.LastModified
  189.  
  190.         ccyBeginningBalance = ccyEndingBalance
  191.         datDueDate = DateAdd("m", 1, datDueDate)
  192.  
  193.         PMTN = PMTN + 1
  194.  
  195.     Loop
  196.  
  197.     Me.Recalc
  198.  
  199. btnRepaymentSchedule_Click_Exit:
  200.     On Error Resume Next
  201.     On Error GoTo 0
  202.  
  203.     RS.Close            'Close opened rs
  204.     Set RS = Nothing    'Deassigns rs & db
  205.     Set qdf = Nothing
  206.  
  207.     RSP.Close            'Close opened rsP
  208.     Set RSP = Nothing
  209.     Set qdfP = Nothing
  210.     Set db = Nothing
  211.     Exit Sub
  212. btnRepaymentSchedule_Click_Error:
  213.     'Error Handler here
  214.     Resume btnRepaymentSchedule_Click_Exit
  215. End Sub
  216.  
  217.  
Attached Images
File Type: jpg test.jpg (51.6 KB, 1241 views)
Attached Files
File Type: zip test.zip (50.9 KB, 129 views)
File Type: txt codes.txt (9.4 KB, 199 views)
Dec 4 '13 #1
Share this Question
Share on Google+
25 Replies


zmbd
Expert Mod 5K+
P: 5,287
1) As I noted inline with your post: ACC2000 isn't supported very well anymore.

2) You have multiple questions. We ask/require that each question be within its own thread. This makes it easier to "stay on topic" and for others to find the information they are searching for to solve their problem(s).

You will need to choose a specific topic and we might be able to help you from there.

3) Is sounds as if your database is highly un-normalized.
> Database Normalization and Table Structures.

Your question three would be easily solved in the form design, no VBA required, provided your database was/is properly normalized. You would have a parent form that was bound to the master-accounts or customer in some way, then a child form that would show the open the subaccounts. The filtering can be done in various ways. For example, I work in a lab, ISO requires tracking of various equipment. SO I have a table that lists "Stations" within the lab, there is another table that has the equipment, and yet a third table where the "stations" and inventory are related (and a few other tables) The parent form is based on the "Stations" the subform (child) is related to the parent on the station and bound to the table that relates the two. When I change records on the parent, the child shows all of the records that were entered against that parent from the third table.
Of course, this form has a lot more functionality built in.

4) How much experience do you have in Database design, Access, and VBA? The reason I ask is that the code you have included vs. the questions asked are a mis-match in the required level of knowledge. I truely mean no dis-respect; however, in order to answer the questions we really need to have a clear understanding of your knowledge base (^-^)


-
Dec 4 '13 #2

P: 9
Hi zmbd,
Thanks for your feedback! In line with your queries:
1)Actually, I'm running Access 2013 but had to post in Access 2000 format for backward compatibility; I will now upload in Access 2003 and Access 2010(if both formats will be allowed and will wait for this confirmation from the moderators)m
2)Also, I will edit and post a single question.
3)I will need to look have another look at the database regards the normalization: but this is what I have; the tblMembers is related to the Loan in a 1-to-many relation while the Loan is also related to sbfSchedules & sbfPayments too in a 1-to-many relation.
4)No offence regards the dis-respect issue! I'm a novice:beginner in Access: this is my actually my first application, I'm coming in from a networking/windows background and have had some assistance with regards to the query elements of the code, hence your assertion to my knowledge-level, aside this the rest is my study/effort.

Thanks
Dec 4 '13 #3

zmbd
Expert Mod 5K+
P: 5,287
I remember when...
K:
Try the following tutorials whilest we take a look under the hood.
MS Access 2010 Tutorials
Access Basics - By Crystal

These two will get you up to speed in fairly short order as far as nomenclature and foundational requirements.

Actually, I think that once you work your way thru them, you wont need us :(

In anycase, I think your table structure could be tweeked a tad:

PK = Primary key
FK = Foriegn key
1:M = relationship, enforced, 1 to many between the tables and indicated field.

tbl_customer
[customer_pk] autonumber - pk
(other UNIQUE fields here. Some DB go so far as to pull the telephone numbers, states, and postal codes into seperate tables. Be carefull about what personal information is stored here... like SSN, birthdates, etc...)

tbl_accountclass
[accountclass_pk] autonumber - pk
[accountclass_name] text(25)
(basically a table with things like "checking-free", "savings", "primecredit"... interest rate might go here; however, I see a different table for interest rates and fees, schedule of payments etc... these can all be related into the DB...)

tbl_accounts
[accounts_pk] autonumber - pk
[accounts_fk_customer] 1:M with tbl_customer![customer_pk]
[accounts_fk_accountclass] 1:M with tbl_accountclass![accountclass_pk]
[accounts_code] text
(so now, you can have a customer with an entry for each accountclass type... in fact you could have a customer with 20 entries for each account class... the accounts code could be like the checking account number etc... I wouldn't store this in an unprotected database.)

from here you can have table with payment history etc...

In anycase, with this table layout, you get your multiple accounts. Normally one would use queries to do the calculations and pull data from the table.

The accounting side is going to get complex; however, there's usually a formula for that ;-)

We can't do the project for you; however, if you get stuck on something specfic well see what can be done to get you back on track!

-z
Dec 4 '13 #4

ADezii
Expert 5K+
P: 8,597
The Data appears to be Normalized to the extent that you have existing 1 ==> MANY Relationships among the Tables and your Loan Parent Form and the Child Schedule and Payment Sub-Forms have the proper Parent ==> Child Linkage based on [LoanID]. I am a little confused as to what you are trying to do. To me it appears that:
  1. The User clicks the Add Payment Button, enters a Date and Amount.
  2. Some form of Validation is performed on these entries.
  3. Assuming Validation passes, a new Record is created in the Payments and Schedules Tables.
  4. Regarding the Schedules Table, the newly added Record's Beginning Balance becomes the Ending Balance of the Current Record - (Amount Paid - Extra).
  5. The Amount Due remains constant and is added to the New Record.
  6. The Ending Balance and Extra Fields of the newly added Record are set to 0.
  7. The Schedule Sub-Form is Requeried to reflect the Newly Added Record.
  8. Am I even close to interpreting your Request?
Dec 4 '13 #5

P: 9
@zmbd:Thanks for your links and the tutorials, i will take the next few days and go through the materials and see how i can improve on my understanding!

@ADezii: Thanks for going through the DB; regards the highlighted points:
I am a little confused as to what you are trying to do. To me it appears that:
Basically, i am working on a Membership based Loan Contributory Scheme and i have trimmed the DB to show only gray areas i am having difficulties. Members contribute monthly (Contributions and other tables not shown ) and also take loans from the Scheme. When a loan is to be accessed, the Loan form is filled and it is desired that a Repayment Schedule be generated immediately as shown in my image, (This is achieved by clicking on Create Payment Schedule(no issue here, though i have some gray areas that will emerge when i have been able to allow extra payments). My challenge is in the area of the extra payments when added, then I have not been able to make the fldEndingBalance become the next record fldBeginningBalance)(by clicking on Add Payment)!

4.Regarding the Schedules Table, the newly added Record's Beginning Balance becomes the Ending Balance of the Current Record - (Amount Paid - Extra).
5.The Amount Due remains constant and is added to the New Record.
It's the reverse,the fldEndingBalance value of previous record becomes the fldBeginningBalance value of next and AmountDue is constant and only changes are to be made in BeginningBalance, AmountPaid, Extra and EndingBalance.

The Ending Balance and Extra Fields of the newly added Record are set to 0.
This is initially true for AmountPaid, Regular and Extra when payments have not been made.

Am I even close to interpreting your Request?
You're almost in tune.
Thanks
Dec 5 '13 #6

ADezii
Expert 5K+
P: 8,597
Let me try to sort things out and I'll see what I can do.
Dec 5 '13 #7

zmbd
Expert Mod 5K+
P: 5,287
Once recorded, shouldn’t the original principal amount be a fixed entry?
The remaining principal for any given point would be a calculated amount such as:
(Original principal) – (payments to date – ( interest to date + fees charged to date))?
(assuming interest and fees do not become part of the principal if not paid)
Or the more evil way:
(Original principal + ( interest to date + fees charged to date) ) – (payments to date)?

Therefor the final balance being a calculated value is never stored.
The only reason I would store the interest charged is if this amount will change with the remaining principal due or is calculated per period… say take the 30 day average principal due and then multiply that against the daily interest rate for the APR/APY being used. This amount is then charged against the account and the payment credited against the account (and I’m sure that in Accounting jargon I have this backward somehow… my wife would know) In any case, the sum of all of the payments and charges taken against the original principal is the current balance due.
Let’s say that Interest, if not paid, becomes part of the rolling principal this once again becomes an issue of how to record the payments and charges and might require a slight de-normalization; however I think not… this can be done in Excel, there’s no reason we can’t achieve the same here (an example of such and Excel is done here: Fixed Rate Loan Amortization Schedule with Optional Extra Principal Payments )

This moving of the Final balance to the Balance forward is a Worksheet/Ledger mindset, at its hear; it is however, a running total, just broken down into steps – this should be able to be done with a query

Now Of course, I’m a Chemist; thus, I may be off on the paradigm that we need to use to solve the issue but reading through (and I did a VERY quick scan, like 3 minutes) of the article I linked to leads me to believe that I’m at least on the correct set of train-tracks and may be in the tunnel too (-)
Dec 5 '13 #8

ADezii
Expert 5K+
P: 8,597
Let's solve one problem at a time. The first one is to retrieve the Last Ending Balance for the Previous Record given the Form ID as indicated in the Parent Form. This can easily be accomplished by using the DLast() Method which will find the Last Ending Balance for Loan ID 1, assuming you are working on Record# 1. Since you are obtaining the Loan ID from the context of the Add Payment Command Button in the Sub-Form, notice the Syntax used in the Criteria. Hopefully, this is what you are looking for.
Expand|Select|Wrap|Line Numbers
  1. Dim curEndingBalance As Currency
  2.  
  3. curEndingBalance = DLast("[EndingBalance]", "Schedules", "[LoanID] = " & Me.Parent![LoanID])
  4.  
Dec 6 '13 #9

P: 9
@zmbd: Yes, the principal is fixed once recorded, but the interest is deducted upfront(Principal - interest), then balance is given to borrower hence the absence of interestpaid column in the schedule. Also the PMT(Monthly payment) is Principal/NumberofPayments. Please note that I'm not using the Built-in functions like PMT/IPMT/PPMT etc.
@ADezii: I have added the code to the btnAddPayment section before Me.Recalc and the EndingBalance is still not carried forward.

Regards the repayment schedule, I want it such that (if extra payment is added even at the point of PMTN 1,the EndingBalance is calculated and assign to PMTN 2 BeginningBalance and the process is repeated till the end of the recordset even before the next payment is added) i.e a recalc of the recordset whenever payment is added.
Thanks
Dec 7 '13 #10

ADezii
Expert 5K+
P: 8,597
What Value are you getting when you execute the Expression
Expand|Select|Wrap|Line Numbers
  1. DLast("[EndingBalance]", "Schedules", "[LoanID] = " & Me.Parent![LoanID])
in the Click() Event of the Add Payment Button. It should be the Ending Balance for the Last Record for the Current Loan ID which now becomes the Beginning Balance. If I am way out in left field on this one, please let me know.
Dec 8 '13 #11

P: 9
When I add N51,666.67 to say the 4th record(extra N10,000) as the AmountPaid, the EndingBalance becomes N323,333.32 and the BeginningBalance of 5th record stays same(N333,333.32)
Thanks
Dec 8 '13 #12

ADezii
Expert 5K+
P: 8,597
Isn't that what is supposed to happen, namely the Beginning Balance of the Next Record is the Ending Balance of the Previous one?
Dec 8 '13 #13

P: 9
@ADezii: Yes, but it's not happening as such when AmountPaid above the AmountDue is added and this is the challenge.
Thanks
Dec 9 '13 #14

ADezii
Expert 5K+
P: 8,597
Are we referring to adding 'Extra' to 'any' Record, then automatically adjusting Ending/Beginning Balances for the remainder of Records?
Dec 9 '13 #15

P: 9
@ADezii: Exactly, just what I wanted.
Thanks
Dec 9 '13 #16

ADezii
Expert 5K+
P: 8,597
Give me a little time while I think this one out, now that I have it straight! (LOL).
Dec 10 '13 #17

ADezii
Expert 5K+
P: 8,597
OK, had enough time. Change any Amount Paid or Extra Payment Value in any of these Fields, in any Record, and the following Public Sub-Routine will perform an automatic Recalculation of the Schedules Table. Simply pass to the Routine the Loan ID Number (LONG) and it will do the rest. Obviously it relies on a strict and sequential order of Payments. Here it goes:
  1. Sub-Routine Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub fRecalcSchedule(lngLoadID As Long)
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim intRecCtr As Integer
    5. Dim strSQL As String
    6. Dim curNextBeginningBalance As Currency
    7.  
    8. intRecCtr = 0   'Initialize
    9. strSQL = "SELECT * FROM Schedules WHERE [LoanID] = " & lngLoadID & ";"
    10.  
    11. Set MyDB = CurrentDb
    12. Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
    13.  
    14. With rst
    15.   Do While Not .EOF
    16.     intRecCtr = intRecCtr + 1
    17.       If intRecCtr = 1 Then
    18.         curNextBeginningBalance = ![EndingBalance]
    19.           .Edit
    20.             ![EndingBalance] = ![BeginningBalance] - (![AmountPaid] + ![ExtraPayment])
    21.           .Update
    22.       Else
    23.           .Edit
    24.             ![BeginningBalance] = curNextBeginningBalance
    25.             ![EndingBalance] = curNextBeginningBalance - (![AmountPaid] + ![ExtraPayment])
    26.               curNextBeginningBalance = ![EndingBalance]
    27.           .Update
    28.       End If
    29.       .MoveNext
    30.   Loop
    31. End With
    32.  
    33. rst.Close
    34. Set rst = Nothing
    35. End Sub
    36.  
  2. Sample Call to perform a Recalculation on Loan ID# 1:
    Expand|Select|Wrap|Line Numbers
    1. Call fRecalcSchedule(1)     'Recalculate Schedule for Loan 1
  3. Any other questions, feel free to ask.
Dec 10 '13 #18

zmbd
Expert Mod 5K+
P: 5,287
Have we made this too difficult?

Original loan: $50,000.00(US)
over say 60 months.
Front load the interest - poor borrower because they'll never see the break that early payment receives.

Now it doesn't matter if the borrower pays the $833.34/mo installment or pays $1833.34 because the entire payment is going against the balance due...

So now the question becomes:
BF - (if payment = 0 then - installment else - payment) = EB

Lookup via the paymentdue or payment field for the BF

check for 0 balance.

I think this can be done in a query... easy in vba with an open table.
Dec 10 '13 #19

ADezii
Expert 5K+
P: 8,597
Either I am making this too complicated or you are over simplifying (LOL).
  • What if neither Payment nor Installment is made at all?
  • What if periodic Extra Payments are made?
  • How would a Query handle the carryover of the Ending Balance to the Beginning Balance of the next Record?
  • The OP needs to randomly modify any Amount Paid or Extra Payment for any Record at any time, how is this factored in with the above Logic?
  • These actions need to be performed from the context of a Form with the subsequent Requery of the Schedules Sub-Form. How does a Query satisfy this?
Dec 10 '13 #20

zmbd
Expert Mod 5K+
P: 5,287
Exactly A,
It should have been a simple process to varBookmark = .lastmodified Returnd to RS.bookmark
pulled rs![endingbalance]
rs.movenext, check eof if true then add new subtact etc...

but I think I missing something.

I'm working on process control charts... but I've an idea that I'll zip and post in a tad...
Dec 10 '13 #21

ADezii
Expert 5K+
P: 8,597
To be perfectly honest with you, zmbd, I do have a tendency to overcomplicate matters, so it probably is me. Ultimately, the OP will know what he wants.
Dec 10 '13 #22

zmbd
Expert Mod 5K+
P: 5,287
I'm basically done with this thread:

Attached is an example ACC2010 database that should provide enough example to handle the question and a vast number of ancillary questions. I expect it should open and work in ACC2007... might error, I use the themes... might not.

to use

Open form "Frm_Schedule"
This is the driving form.
In the dropdown box (this will not show in a printout :) ) you can select the loan:

The loans are sorted by customer's last name and then by loan id:
ie: FirstName LastName (LoandID n)
Mr. Zulu Able has two loans #3 and #4
#3 he has made no payments, P=50,000.00(us) T=60 therefore the schedule is straight forward.
#4 he's been a tad short on a few payments of P=40,000.00(us) T=5; thus, a few extra payments are due.

Ms. Echo Vinegar has one loan #2
She's made a few payments. P=10,000.00(US) T=36
If you will look at the payment table, you'll see in 12/2013 she made two payments the sum of which is noted in the schedule

And finally Mr Alpha Zebra has loan #1 P=500,000.00(us) T=60. He's been short a few months and over on others.

In all of these cases: TsubN is adjusted to reach an ending balance of $0.00 either by shortning the TsubN from T0 or adding to T0 as needed and IsubN is likewise adjusted such that TsubN-W/EBsubN=0.

I'm quite proud of my working man progress indicator... I just about made a loan#5 with T=700 just to watch it work... resisted the urge... besides I have a loop kill set for that amount to prevent a runaway table.

Because I don't want to force people to open the DB the main concepts follow:

tables:
tbl_loans: loan_pk, loan_fk_people, loan_principal, loan_numberofinstallments, loan_originationdate
tbl_payment: payment_pk, payment_fk_loan, payment_paiddate, payment_amount
tbl_people: people_pk, people_Firstname, people_lastname, people_email
tbl_schedule: schedule_pk, schedule_paymentnumber, schedule_duedate, schedule_startbalance, schedule_installdue, schedule_payment, schedule_excess, schedule_endbalance

relationships
people_pk > loan_fk_people
loan_pk > payment_pk

Forms:
Frm_schedule is based on a simple select query on tbl_schedule, addnew disabled, delete disabled, all controls locked except CBO in header

Unbound CBO with a beforeupdate to allow adding new and deleteing, also to confirm update. Recordsource is based on an embeded SQL relating loans and people for a humnan readable.

There are no other forms to service the tbl_loans, tbl_payments etc...

There are no other queries to service the tbl_loans, tbl_payments etc...

main driveing code on Frm_schedule:
Expand|Select|Wrap|Line Numbers
  1. Private Sub z_ctrl_cbo_customer_AfterUpdate()
  2.     Dim zdb As DAO.Database
  3.     Dim zrsloan As DAO.Recordset
  4.     Dim zrspayment As DAO.Recordset
  5.     Dim zsql As String
  6.     Dim zsqlpayment As String
  7.     Dim zprincipal As Double
  8.     Dim zinstallment As Double
  9.     Dim znumberofinstallments As Double
  10.     Dim zactualpaid As Double
  11.     Dim zpayment As Double
  12.     Dim zextra As Double
  13.     Dim zstartbalance As Double
  14.     Dim zendbalance As Double
  15.     Dim zoriginationdate As Date
  16.     Dim zduedate As Date
  17.     Dim zvalidcustomer As String
  18.     Dim x As Long
  19.     Dim zexitloop As Boolean
  20.     '
  21.     On Error GoTo zerrortrap
  22.     '
  23.     'get the customer name for the text box later
  24.     Me.z_ctrl_cbo_customer.SetFocus
  25.     zvalidcustomer = Me.z_ctrl_cbo_customer.Text
  26.     '
  27.     'invalidate the current schedule
  28.     With Me.z_ctrl_txt_validforcustomer
  29.         .Value = "Invalid... building new table..."
  30.         .ForeColor = (-2147483608)
  31.     End With
  32.     '
  33.     'Open the database for use
  34.     Set zdb = CurrentDb
  35.     '
  36.     'on load this was done; however, the user my be doing a new customer or rebuild so clear the table
  37.     zsql = "DELETE * FROM tbl_schedule"
  38.     zdb.Execute zsql, dbFailOnError
  39.     '
  40.     'update the form for user feedback
  41.     Me.Requery
  42.     '
  43.     'open the loan table and retreave the information
  44.     zsql = "SELECT * FROM tbl_loans WHERE ([loan_PK]=" & Me.z_ctrl_cbo_customer & ");"
  45.     Set zrsloan = zdb.OpenRecordset(zsql, dbOpenForwardOnly)
  46.     '
  47.     'begin setup and calculations for the first customer.
  48.     'we could set this up as a custom function within the form; however, in this
  49.     'case I don't see the advantage.
  50.     x = 1
  51.     zstartbalance = zrsloan![loan_principal]
  52.     znumberofinstallments = zrsloan![loan_numberofinstallments]
  53.     zoriginationdate = zrsloan![loan_originationdate]
  54.     '
  55.     'this is a cute trick... dateserial will not return an invalid date so in this case it returns
  56.     'the last day of the month/
  57.     zduedate = DateSerial(Year(zoriginationdate), Month(zoriginationdate) + 1, 0)
  58.     zinstallment = Format((zstartbalance / znumberofinstallments), "##.00")
  59.     '
  60.     'we're done with the loan close and release the memory
  61.     zrsloan.Close
  62.     If Not zrsloan Is Nothing Then Set zrsloan = Nothing
  63.     '
  64.     'Pay attention here, this is repeated in the loop
  65.     'Only the payments made in both the month and year (say December 2013) will be pulled and summed
  66.     'this is for the origination month... in the loop it builds for the due month.
  67.     zsqlpayment = _
  68.         "SELECT Sum(Query1.payment_amount) AS SumOfpayment_amount" & _
  69.         " FROM (SELECT tbl_payment.payment_fk_loan, tbl_payment.payment_paiddate, tbl_payment.payment_amount" & _
  70.         " FROM tbl_payment" & _
  71.         " WHERE (((tbl_payment.payment_fk_loan)=" & Me.z_ctrl_cbo_customer & ") AND" & _
  72.         " ((Year([payment_paiddate])=Year(#" & zoriginationdate & "#)) And" & _
  73.         " (Month([payment_paiddate])=Month(#" & zduedate & "#))))) as Query1;"
  74.     Set zrspayment = zdb.OpenRecordset(zsqlpayment, dbOpenForwardOnly)
  75.     zpayment = Nz(zrspayment![SumOfpayment_amount], 0)
  76.     '
  77.     'we're closing this because we'll rebuild the query and then run again for the new calculated due date.
  78.     zrspayment.Close
  79.     If Not zrspayment Is Nothing Then Set zrspayment = Nothing
  80.     '
  81.     'check for payments and either under or overage.
  82.     If zpayment > 0 Then
  83.     '
  84.         zactualpaid = zpayment
  85.         zextra = zpayment - zinstallment
  86.     Else
  87.         zactualpaid = zinstallment
  88.         zextra = 0
  89.     End If
  90.     '
  91.     zendbalance = (zstartbalance - zactualpaid)
  92.     '
  93.     zexitloop = False
  94.     Do
  95.         'and finally build the insert string.
  96.         'I could do the rs.add and so forth, and I thought about it; however, this is quick
  97.         zsql = "INSERT INTO tbl_schedule" & _
  98.             " (schedule_paymentnumber,schedule_duedate,schedule_startbalance,schedule_installdue,schedule_payment,schedule_excess,schedule_endbalance)" & _
  99.             " VALUES" & _
  100.             " (" & x & ",#" & zduedate & "#," & zstartbalance & "," & zinstallment & "," & zpayment & "," & zextra & "," & zendbalance & ")"
  101.         zdb.Execute zsql, dbFailOnError
  102.         '
  103.         'prepr for the next entry
  104.         zstartbalance = zendbalance
  105.         If zstartbalance = 0 Then zexitloop = True
  106.         '
  107.         'because the payments may span years, the last day was the last day of the last month
  108.         'and we want the last day of the next month so increment the date by 2 to get in to the next month.
  109.         zduedate = DateAdd("d", 2, zduedate)
  110.         zduedate = DateSerial(Year(zduedate), Month(zduedate) + 1, 0)
  111.         '
  112.         'and here we pull the payments again for the year and month of the duedate.
  113.         zsqlpayment = _
  114.             "SELECT Sum(Query1.payment_amount) AS SumOfpayment_amount" & _
  115.             " FROM (SELECT tbl_payment.payment_fk_loan, tbl_payment.payment_paiddate, tbl_payment.payment_amount" & _
  116.             " FROM tbl_payment" & _
  117.             " WHERE (((tbl_payment.payment_fk_loan)=" & Me.z_ctrl_cbo_customer & ") AND" & _
  118.             " ((Year([payment_paiddate])=Year(#" & zduedate & "#)) And" & _
  119.             " (Month([payment_paiddate])=Month(#" & zduedate & "#))))) as Query1;"
  120.         Set zrspayment = zdb.OpenRecordset(zsqlpayment, dbOpenForwardOnly)
  121.         zpayment = Nz(zrspayment![SumOfpayment_amount], 0)
  122.         zrspayment.Close
  123.         If Not zrspayment Is Nothing Then Set zrspayment = Nothing
  124.         '
  125.         '
  126.         If zpayment > 0 Then
  127.         '
  128.             zactualpaid = zpayment
  129.             zextra = zpayment - zinstallment
  130.         Else
  131.             zactualpaid = zinstallment
  132.             zextra = 0
  133.         End If
  134.         '
  135.         zendbalance = (zstartbalance - zactualpaid)
  136.         '
  137.         If (zendbalance < 1) Then
  138.             zinstallment = zinstallment + zendbalance
  139.             zendbalance = 0
  140.         End If
  141.         x = x + 1
  142.         '
  143.         'just in case the PC is moving a tad slow:
  144.         With Me
  145.             .z_ctrl_txt_validforcustomer.Value = ":" & String(x Mod 30, " ") & "(@_@)"
  146.             .Repaint
  147.         End With
  148.         '
  149.         'emergency stop! I ALWAYS have an emergency stop in the loops
  150.         ' a 30 yr note with monthly payments is 360 records, so if we get above double then kill the loop
  151.         If x >= 720 Then
  152.             MsgBox "YIKES" & vbCrLf & "Loop count exceeded 500 records!" & vbCrLf & "Payment schedule is invalid! Please check all loan and payment entries", vbCritical + vbOKOnly, "Loop Emergency Stop!"
  153.             zvalidcustomer = "ERROR - Exceeded 500 Record Limt - ERROR"
  154.             zexitloop = True
  155.         End If
  156.         '
  157.         'now.. if the payments are short then the balance isn't zero so
  158.         'keep adding until both the number of payments have been made
  159.         'and the balance is zero, or until the flag is set to true.
  160.     Loop Until (((x > znumberofinstallments) And (zendbalance < 0)) Or zexitloop)
  161.  
  162.     '
  163. zcleanup:
  164. 'force close and release, ignoring all errors at this point as we're either bailing or finished.
  165.     On Error Resume Next
  166.     If Not zrspayment Is Nothing Then Set zrspayment = Nothing
  167.     If Not zrsloan Is Nothing Then Set zrsloan = Nothing
  168.     If Not zdb Is Nothing Then Set zdb = Nothing
  169.     With Me
  170.         .Requery
  171.         .z_ctrl_txt_validforcustomer = zvalidcustomer
  172.         .z_ctrl_txt_validforcustomer.ForeColor = 4210752
  173.         .AllowAdditions = False
  174.     End With
  175. Exit Sub
  176. zerrortrap:
  177. zvalidcustomer = "ERROR # " & Err.Number & "< ERROR"
  178. MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source
  179. Resume zcleanup
  180. End Sub
I'm sure that there are easier methods and I welcome comments/critique, on the code provided they are made in a seperate thread linked back to this one. Please be gentile and keep in mind that I'm not a programmer. Some to the techniques I used were, well, "just because."

tada

At this point, if OP can't figure out how to do a carry forward, I have nothing more to offer.
-z
Attached Files
File Type: zip BytesThread_953326_balanceforwardandrebuild.zip (113.4 KB, 97 views)
Dec 11 '13 #23

P: 9
@ADezii: Thanks for your efforts and goodwill, i inserted the sub-routine code in a standard module and then added the call procedure in the after_update event of the sbfSchedules, but when i made changes to either fldAmountPaid or fldExtraPayment, I'm having a compile error (Method or Data Member not found) with .EOF highlighted.
NB: i was wondering on the use of the LoanID argument(LoanID 1 is explicitly stated)in the call procedure, how will this take account of other LoanID's when the focus is not on LoanID #1? Thanks


@zmbd: Thanks for your guidance and tutorials, i am studying it to adapt to my situation, especially in the carry-forward aspect. Thanks for your expertise.
Dec 13 '13 #24

ADezii
Expert 5K+
P: 8,597
I'm having a compile error (Method or Data Member not found) with .EOF highlighted.
Make sure you have a Reference set to the Data Access Objects.
NB: i was wondering on the use of the LoanID argument(LoanID 1 is explicitly stated)in the call procedure, how will this take account of other LoanID's when the focus is not on LoanID #1? Thanks
You can either process ALL LoadIDs, or read the Loan ID from the Current Record on the Form, as in:
Expand|Select|Wrap|Line Numbers
  1. Call fRecalcSchedule(Me![LoanID])
Dec 13 '13 #25

P: 9
@All:
In the Spirit of the season, Wishing all a Merry Christmas & a Prosperous New Year in advance!
@ADezii: Guess I'm back from AWOL, lol.
Cross-checked the references, references were ok, since I had a reference to MS Access library 15.0(office 2013), so I had to check code(my typing of your code) for typos and saw that I did a Dim rst As DAO.DB instead of a Dim rst As DA0.RS, corrected that and when I made changes, I'm now having a Compile error: Sub or Function not defined with Else highlighted. Pls note I had to change Line 25(relation bw variables was wrong) of your code to ![EndBal(EB)] = curNextBeginBal(NBB) - ![AmountPaid(AP)] since (![AmountPaid(AP)]=(![AmountDue(AD)]+![ExtraPay(EP)]). Also, I was thinking that since you've assigned !BB= NBB in Line 24, in line 25, NBB should be ![BB]
Thanks for your time and assistance.
Jmeni
Dec 26 '13 #26

Post your reply

Sign in to post your reply or Sign up for a free account.