Payment Calculator Form in Access
Input loan amount, annual interest rate, number of years, and number of payments per year
Launch Excel and use its PMT function to calculate the periodic payment. In the case of a standard loan, this is the monthly payment. Return the result to a control on the form.
Download
Download Access database application with form that uses Excel's PMT function when the Calculate button is clicked.
DATABASE_Excel-Pmt-Function_s4p.zip
(62K, unzips to 600 kb ACCDB Access database)
Be sure to save the accdb file from the zip as its own file. Access doesn't work right inside a zip file.
This Tool may only be used freely -- you may not modify it and sell it, or use its ideas to make or change a commercial product. Please share your modifications back to the community. Thanks!
If you've never used Access, no problem! You probably have it in your Microsoft Office suite, so the file should open when you double-click its name.
In Excel, as you type a formula, you are prompted for arguments, and you can pick cells. How do those translate to what is needed if you are in Access? And how do you calculate the function arguments from the data that you have? What is something needed is missing? How can you know what the problem is? By using VBA, we can trap problems before run-time errors happen. Data validation is done so that code can step in and make things more friendly if there are problems.
If data is okay, we assign values and calculations to variables. To enhance performance and readability, the code uses With and End With blocks. Big discussion on this -- so hopefully it will be clear as mud in the end (smile).
In our PMT example, the data entry form has required and optional data that can be specified. The Access form:
- Requires:
- Loan Amount
- Annual Interest Rate
- Number of Years
- Number of Payments Per Year
- Optionally, these may also be specified:
- future value (default is zero, 0)
- type - when payments are due
The PMT function need calculations from the above inputs -- see how this is done for:
- periodic interest rate
- number of periods
- present value
Go to the design view of the form and inspect properties. Launch the code builder to trace what happens when the Calculate button is clicked.
Using VBA, we can validate data so that if the value is null (missing!), the user focus will be set to the control that needs to be filled, the user gets a message, and the code exits. Otherwise, that value or a calculation involving it, is assigned to a variable.
See how Excel can be referenced so that its functionality can be used. Here is the statement that runs Excel's PMT function, using variables to represent the arguments. It returns the result to the curPayment variable:
curPayment = oExcel.WorksheetFunction.Pmt _
(sgRate, iNPer, curPV, curFV, iType)
WHERE:
oExcel is the Excel application object
Pmt is the PMT Worksheet Function
sgRate, iNPer, curPV, curFV, and iType are variables that hold values for each of the function arguments
We also go into a discussion about using With Me.someObject to enhance performance and readability. The term 'object' hangs a lot of people up -- hopefully this lesson can help make things more clear. An object is something. A ball is an object with methods such as toss and bounce; and properties such as size, color, and weight.
Resources
Free Book on Access: Access Basics
Free Tutorials: Video Tutorials