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
Option Compare Database
Option Explicit
Private Sub btnMonthlyPayment_Click()
On Error GoTo btnMonthlyPayment_Click_Error
Dim ccyLoanAmount As Currency
Dim dblInterestRate As Double
Dim ccyInterestPaid As Currency
Dim ccyPayment As Currency
Dim ccyMonthlyPayment As Currency
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
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.
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 (^-^)
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.
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!
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:
The User clicks the Add Payment Button, enters a Date and Amount.
Some form of Validation is performed on these entries.
Assuming Validation passes, a new Record is created in the Payments and Schedules Tables.
Regarding the Schedules Table, the newly added Record's Beginning Balance becomes the Ending Balance of the Current Record - (Amount Paid - Extra).
The Amount Due remains constant and is added to the New Record.
The Ending Balance and Extra Fields of the newly added Record are set to 0.
The Schedule Sub-Form is Requeried to reflect the Newly Added Record.
@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.
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 (®-®)
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.
@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
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.
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
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:
Sub-Routine Definition:
Expand|Select|Wrap|Line Numbers
Public Sub fRecalcSchedule(lngLoadID As Long)
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim intRecCtr As Integer
Dim strSQL As String
Dim curNextBeginningBalance As Currency
intRecCtr = 0 'Initialize
strSQL = "SELECT * FROM Schedules WHERE [LoanID] = " & lngLoadID & ";"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
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.
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...
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.
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:
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
Private Sub z_ctrl_cbo_customer_AfterUpdate()
Dim zdb As DAO.Database
Dim zrsloan As DAO.Recordset
Dim zrspayment As DAO.Recordset
Dim zsql As String
Dim zsqlpayment As String
Dim zprincipal As Double
Dim zinstallment As Double
Dim znumberofinstallments As Double
Dim zactualpaid As Double
Dim zpayment As Double
Dim zextra As Double
Dim zstartbalance As Double
Dim zendbalance As Double
Dim zoriginationdate As Date
Dim zduedate As Date
Dim zvalidcustomer As String
Dim x As Long
Dim zexitloop As Boolean
'
On Error GoTo zerrortrap
'
'get the customer name for the text box later
Me.z_ctrl_cbo_customer.SetFocus
zvalidcustomer = Me.z_ctrl_cbo_customer.Text
'
'invalidate the current schedule
With Me.z_ctrl_txt_validforcustomer
.Value = "Invalid... building new table..."
.ForeColor = (-2147483608)
End With
'
'Open the database for use
Set zdb = CurrentDb
'
'on load this was done; however, the user my be doing a new customer or rebuild so clear the table
zsql = "DELETE * FROM tbl_schedule"
zdb.Execute zsql, dbFailOnError
'
'update the form for user feedback
Me.Requery
'
'open the loan table and retreave the information
zsql = "SELECT * FROM tbl_loans WHERE ([loan_PK]=" & Me.z_ctrl_cbo_customer & ");"
Set zrsloan = zdb.OpenRecordset(zsql, dbOpenForwardOnly)
'
'begin setup and calculations for the first customer.
'we could set this up as a custom function within the form; however, in this
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
@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.
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:
@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
Can anyone help?
I query a database and return a result on the column "reference".
There might be 7 listings. Each row is displayed in a table, with
links through to a detail page. I am working...
I have a main form with navigation buttons on it and a label showing
for example Record 1 of 15 using recordsetclone on it and eveything
works fine. When I move through the records the record...
Hi All,
Can anyone direct me or provide advice on how I can assign a null value to a
date variable in vb.net.
Basically what I'm doing is that I'm looping through a recordset where I
have three...
I can't assign a MS SQL Server table record value to a simple VB variable, should be an easy thing. Sample SQL Server table has the data in the record as a char(30) string, the column for that record...
Hi
How can i put save record and next record in one button.
in my save record i put the calculation of the text boxes. i have a total field that the sum of the other field is in there and it wil be...
Hello every one.
Can someone help me regarding how to move to previous record and next record through hyper links. One can have the example of the below mentioned block:
<< 1 2 3 4 5 6 ..........
I have a list of records in a subform that a user can either edit or
delete. This is an unbound form. If the user deletes a record, I
want to refresh the form, and then position the cursor on the...
Hello Ladies/Gents, im in need of some serious help here.. I have a tool that allows users to view information on a form and make changes to recordsets. This tool has buttons that allow a user to...
Hello,
Well here goes, I am using Microsoft Access to design a user interface for SQL Server database, I am currntly using the project method. I have manged to link it up and get my form to show...
Hi,
Hope you can assist...
My table has two OLE/BLOB fields. 1 x OLE (Photo) and 1 x OLE (Signature).
1. With WebCam, I save my image - "Works Fine":
Private Sub Capture_Click()...
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
9
