| |
| |
|
Courses - Microsoft
Access 324 |
| Description: |
Advanced Access
Recordsets |
| Running Time: |
84 minutes |
| Pre-Requisites: |
Access 323 very strongly recommended |
| Previous Lesson: |
Access 323
|
| Next Lesson: |
Access 325
|
| Main Topics: |
Loan Calculator, Amortization, PMT, Me.Filter, On Not In List Event, NewData
|
| Versions: |
This course is valid for Access 2000 through 2003.
If you are using Access 2007 or 2010, you will still benefit from this course.
You will find that the concepts are the same, but there are cosmetic differences between the versions.
|
|
Order before 5/20/2012 to
get a FREE upgrade to our
Access 2010 version!
Click here for details
|
|
|
|
|
| |
|
We'll begin by building a loan calculator
where you can enter in the price of the loan, the down payment, interest
rate, and number of years. Access will then be able to tell you what
your monthly payments will be. We'll first see how this works in
Excel.
Then we'll build an Access form to
calculate the same basic results.
We'll make a "Create Payments" button
which will use a Recordset to fill a subform with all of the payments
we'll have to make over the life of this loan, including payment amount
and due date.
We'll use a form footer total to be able to see
the current and total balance of the loan.
We'll add the ability to track your payments
on the loan.
Next we'll move to a very popular topic that lots
of students ask me about: how to add an item to a combo box. To do that,
we have to learn how to program for the Not In List event. We'll
start with a simple list of customers with states.
We also need a simple State table.
We'll make a Customer form with a combo
box to pick the state.
If the user types in a state that we didn't have
in the State table, we'll give him the option to ADD it to the
table and save it in the customer record.
It looks easy here, but there's actually a LOT
of programming behind that one little Yes/No option. You'll see how to
do it in class.
Access 324 - Course Outline
1. Loan Calculator, Part 1
Calculating Loan Amortizations
Enter In Data For Loan
Price Of House
Down Payment
Interest Rate
Term of Loan
Calculate Amount Financed
Calculate Monthly Payment
Using the PMT Function
PMT in Excel
Calculate Total Payments
Calculate Interest Paid
2. Loan Calculator, Part 2
Build the Loan Form
Build the Payment Form
Use a RecordSet to Create Payments
DateAdd Function
Me.Requery to See Subform Updates
3. Loan Calculator, Part 3
Payment Total SUM
Label to Filter Payments
Show Current Payments
Show All Payments
Me.Filter
Me.FilterOn
Turning the Filter On and Off
Check For Existing Payments
DLookup Any Existing Payments
Delete Query to Erase Schedule
4. Loan Calculator, Part 4
RecordSet to Add Manual Payments
Change RecordSource Better Than Filter
5. Loan Calculator, Part 5
Fixing the Missing Penny Fraction
6. On Not In List Event, Part 1
Customer Table
State Table - Storing Text
Add Different State - Not In Table
AdSource Table - Storing a Number
Limit To List Property
7. On Not In List Event, Part 2
NotInList Event
NewData Parameter
Response Parameter
acDataErrDisplay
acDataErrContinue
acDataErrAdded
Ask User If They Want To Add Data
Recordset to Add Value To Table
Using InputBox for Additional Fields
|
| |
Student Interaction:
Microsoft Access 324
|
Richard on 1/1/2008:
Loan Calculator, PMT function, On Not In List Event |
Harry Mullin on 3/20/2009: Is there a way to suppress the delete query warning (ie action query) for a particular code procedure without unchecking "Action Queries" under Edit/Find in the options menu?
I tried using:
CurrentDb.Execute "ClearScheduledPaymentsQ", dbFailOnError
but got a "too few parameters, expecting 1" error.
Is there a way to get the execute statement to work?
|
Harry Mullin on 3/21/2009: I would definitely like to see how to pop up a form and use data entered to populate a combo after NotInList event fires.
|
Richard Rost on 3/23/2009: Harry, just execute a "Docmd.SetWarnings FALSE" command before running your query, and then be sure to turn them on after you're done.
As far as the specific error you're getting, do you have any parameters in that query? If so, you might have to supply those values with a Form (Forms!Formname!Field) because Db.Execute might not like actual parameters.
|
Richard Rost on 3/23/2009: Harry, your vote is registered. Anyone else?
|
michael haag on 7/6/2009: I agree with Mr Mullin 100%. I'd also like to see a pop up form so data could be added to the entire form.
|
michael haag on 7/6/2009: I'd also like to see the little hyperlink looking "Add New" which is in some MS downloadable temples. This would then open the form. I think the "Add New" hyperlink looks very professional. Thanks
|
Terry Hopper on 7/28/2009: I would like to see the more complex way of updating the multiple fields of a table and then fire off the event to continue you speak of in lesson #7.
|
Nathan K on 9/29/2010: So far I must say I am impressed with the quality of your lessons. I have done the access 101, 102, and a portion of the 324. I have been using access off and on for several years and even the basic level course have taught me a few tricks. I agree that it would be great if you could expand on the not in list function to allow for opening the data entry form. Can you also point me in the right direction on what lesson you start with the access VB code portion of the courses. Thanks Nathan
|
Kenneth Lange on 11/7/2010: Excellent Seminar on Loan Amortization in Access 324!
How could this be converted for an ARM Adustable Interest Rate, and also for yearly changes in ESCROW? Maybe even break down the monthly payment into Principal & Interest payments.
Reply from Richard Rost:
Kenneth, that would take me quite some time to explain (more than I could go into here) but it's an EXCELLENT topic for a future lesson.
|
Len Jolly on 12/2/2010: Hi Richard
I'm on 324 time index 02.48. In my table I have the InterestRate field set to Single,percent and 1 decimal place, but if I put say 9% into my form or table it comes up 900%! I can correct the calculation in my LoanQ, but want to get it right in the first place. What am I doing wrong please. Thanks
Len
|
Kenneth Lange on 12/30/2010: Hello, I enjoy learning from your video seminars very much. I am trying to figure out the Best method of also calculating ESCROW in a Mortgage, an ARM Mortgage, and also trying to show the amunt of each payment that goes to the Principal & Interest on Each payment during the Amotizaton Schedule.
Do you have any comments how I may be able to do these things, as I am still Very New to access. I am using Access 2010 and I have learned UCH from your Seminars! Thanks!!!
Reply from Richard Rost:
I haven't covered any of this in my ACCESS classes. I did cover building a basic loan/mortgage calculator in Excel 104, however it didn't go over all of those details that you're looking for.
I could have sworn I covered building a loan amortizer in one of my Excel lessons, but a quick check of my outlines shows I did not. I'll make sure to cover this in an upcoming lesson - or maybe I'll make a short seminar on just doing that.
|
Shams Momin on 4/1/2011: Hi Richrard I Create Loan Amotization Db by fowlloing you and I type vb code as yors bellow
Option Compare Database
Option Explicit
Private Sub Command36_Click()
CreatePayments
End Sub
Private Sub CreatePayments()
' CHECK FOR EXISTING PAYMENT FIRST!!!!!!
Dim StartDate As Date
StartDate = InputBox("Start Date", , Date)
Dim db As Database
Dim rs As Recordsets
Set db = CurrentDb
Set rs = db.OpenRecordset("LoanPaymentT")
Dim X
For X = 1 To NumberOfMonths
rs.AddNew
rs!LoanID = LoanID
rs!Paymentdate = StartDate
StartDate = DateAdd("m", 1, StartDate)
rs!paymentamount = MonthlyPayment
rs.Update
Next
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Me.Requery
End Sub
But I got this Error Compile Error: Variable not Defined
Reply from Richard Rost:
Which variable is not defined? Does the debugger stop on a specific line. Remember, if you have the line "option explicit" at the top of your VBA window, you HAVE to DIM each of your variable.s
|
Shams Momin on 4/3/2011: Hi Richard On your Loan Amortization Lesson Is there way to show also monthly Principal and Inerest amount as well as monthly Payment. Because monthly principal and Inerest get change every month
Reply from Richard Rost:
Yes, there is. I plan to cover this in a forthcoming lesson.
|
Lilly Nguyen on 4/4/2011: Please help!!!! I copied PC sales database to my computer and when i click on any form the is an erro messege came up "access db object or class does not support the set of events". what can i do to solve this problem. I have also tried to created a blank new database and import all tables and forms.. still not work. please help!!!!
|
eddyrg on 4/14/2011: Hi,
I am using Access 2002
The IfNotInList event doesn't work as givin in the course.
For as far I am able to see there are 2 lines giving a trouble (allthough Access only giving 1 at a time and doesn't show the next problem before sorting out the first)
first line with problem
Dim db As database
second line
Set rs = db.openrecordset("StateT")
Can help someone help me out?
On typing the code there apears a list of VBA codes which I don't manege to figure out. The VBA help I've got doesn't allow me to search by topic;
Thankx
Reply from Richard Rost:
Access 2002 uses ADO as the default recordset type, so you have to make sure you have a reference to DAO in your "references" section (as I talk about in Access 320) and make sure it has a higher priority than ADO.
I just looked at your account and I see you skipped Access 320. This is why almost all of my courses have pre-requisites... because they're based on the material in the previous classes. Access 320 lays down the foundation for all of the recordset material in 320 to 329. Skipping that class causes you to miss a LOT of important information.
As a quick fix, open up any form in your database. Go to design view. Click on View > Code. This will open the VBA editor. Go to Tools > References. Make sure "Microsoft DAO x.x Object Library" is checked and that it has a higher PRIORITY (use the arrows) than "Microsoft ActiveX Data Objects." Save, close, and reopen the database, and now all of my recordset code should work as shown.
And I strongly recommend you take Access 320 before 324. Like I said, it has all of the material that EXPLAINS what's going on in the rest of the 32x classes.
|
Dana on 4/27/2012: I would also like to see more options to a not in list options although I can think of many ways to utilize what you have just shown me... Knowledge is POWER!!! thank you for sharing
|
|
|
| |
|
You may want to read these articles from the 599CD News: |
|
|
|
