Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 
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 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

NOTE: This class just covers the basics and calculates monthly payments. If you're looking to generate a complete amortization schedule (with principal, interest, start & end period balances, and the works) be sure to see my MICROSOFT ACCESS LOAN AMORTIZATION SEMINAR.

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
 Jim Gray on 5/25/2012: Richard,

My database is for an ambulance service. When I enter in a run into that table one of the pieces of information is the patient that comes from the patient table. I use a combo box to select the patient from. I would like to use the on not in list event to add a new patient record if it does not already exist. I was thinking that I could just use a recordset and several input boxes as you suggest to get this patient added but being able to have the patient form pop up would be much neater. I guess I would like to add my name to those that want to see how you would do that.



Reply from Richard Rost:

OK. In the mean time, a temporary solution is:

1. If the patient isn't in the list, make a button to open your patient form
2. When the patient form closes, issue a Forms!RunForm!ComboBox.REQUERY

That should give you a quick way to add a patient without needing On Not In List. You just need to put an "add patient" button next to the box.

James G on 5/25/2012: Richard,

Thanks as always for your help. This should work just fine for my puposes. The combobox.requery is something I don't remember seeing yet, just the refresh and requery forms which I have used a lot. Again thanks for your help.

Reply from Richard Rost:

You can requery the list of items in a combobox or listbox by saying:

Forms!FormName!ComboBoxName.REQUERY

I sometimes create a quick data entry form - like just name and email or whatever essential items you need. You can even make that a MODAL, POPUP window so the user HAS to type in data or cancel it. Then, in that little form's ONCLOSE event run your REQUERY event for the combo box.

Works like a champ. You know what... I'm just getting ready to record Access lessons for today, so I'll take a few minutes and make this into a short TIP video for you. Give me a couple hours. :)


Jim on 5/25/2012: Thanks so much Richard, I look forward to seening. Happy Memorial Day weekened!

Reply from Richard Rost:

Same to you!

Richard R on 5/25/2012: Here you go: Add to Combo Box
shamsMomin on 11/5/2012: Rick How to disply Each Month Interest and Principal amount in Loan payment Form

Reply from Richard Rost:

Shams, I need more information. Tell me how your form is set up. What kind of data are we dealing with?

Shams Momin on 11/5/2012: Rick I am building loan Calaculater same as you build in access 324 but I need more detail in Payment sub form with each month payment also i want to show each month interest and principal in sub form as well as in payment table.

Reply from Richard Rost:

Sounds like it's going to be a little more detail than I can go into here. I'll put it on my list for future possible updates for the class.

Connie Harvey on 12/22/2012: Hi Richard,
Have you put up anything on showing the breakdown of interest and principal yet?

Thanks,

Reply from Richard Rost:

Not yet. Another student has asked me to make a more in-depth lesson on amortization, and I'm planning to do it VERY soon. I'm hoping next week... if not right after the 1st.

Aunali Bhalloo on 1/5/2013: Thank you.
The video was of great help and had added extra skills. Very well presented.

Debra Edelman on 3/11/2013: I don't understand why the label caption was saved when working with filters but not when working with recordsets. It doesn't seem that there should be any connection.

Reply from Richard Rost:

Not sure I follow... can you elaborate, please?

Debra Edelman on 3/13/2013: Maybe I missed something but in when you used filters to change the records shown, you said that it saved the label name. But when you used recordsets, it didn't. But the rest of the code was exactly the same so what caused the label to save with the filtered version and not with the recordset version. Or did I miss something?

Reply from Richard Rost:

Basically, FILTERS are not reliable. They don't always keep their settings. Recordsets are always reliable.

Debra Edelman on 4/2/2013: I had placed more than one record in my loans table. In the form, when I use the "Show Current" and move to another record, that feature is no longer available.
Debra Edelman on 4/16/2013: If you use this form with more one record, the requery function causes the form back to the first record. Also, the log payment doesn't change according to which record you are on.

Reply from Richard Rost:

Yes, Debra, if you use the REQUERY command, it will cause the form to jump back to the first record. I'll have to look at what you mean about the log payment problem. Honestly, it's been 5 years since I recorded this video, so without re-watching it I can't recall the lesson off the top of my head. I will be revisiting this when I re-record the lesson for Access 2013 though... very soon.

Jyotsana Jaswal on 5/19/2014: Hi As requested by everyone above for expanding over adding name and last name to list by a pop up form. Have that been done yet. If yes, please let me know the lesson number. As I also need to do similar for my project.
Margarita Apostolova on 2/22/2015: Hello Rick!
Thank you for the good training, it is so easy to follow, easy examples that we can incorporate in our own work. Your explanation is great and very clean!. I would say, the classes are neither short or long. At least I cannot work to more than 1 a day!

Now, I'm writing in this form because my question is not connected with the lesson, and it is general for MS Access 2013.
I'm working all lessons on 2013, but I have all previous versions as well. The purpose is clear, need to learn the newest version anyway.
Now... the last several small databases I created in 2013, I cannot open in any way with 2007. I found the link:
https://support.office.com/en-us/article/Convert-a-database-to-the-accdb-file-format-098ddd31-5f84-4e89-8f44-db0cf7c11acd?CorrelationId=0022ab04-8ddd-42c2-a9aa-64b4cae865c3&ui=en-US&rs=en-US&ad=US#__use_access_2007

But it is not helpful at all. We have the same file format for all last 3 versions, In your examples we use only simple tables/query/forms with some vba code. Nothing new that I would consider for "new feature". Or am I wrong?
I can open the LoanAmortizer and NotInList databases (which I created) only with 2013 and I do not have the option to convert them down to 2010 or 2007. Either the attempt to go to 2003 was unsuccessful. Any thoughts?
I'm talking to save and open just as a regular database access (accdb) format.
Thanks,
Margarita

Reply from Alex Hedley:

One option is to create a blank db in the older version then import the objects into the new version.

Gary C on 12/8/2016: 12/08/2016
Debra I ran into the same problem. I used Me.refresh instead of me.requery and it worked perfect

Gary C on 12/8/2016: Debra, It's 3 1/2 years later but just in case you find this --- I wrestled with the same problem for about an hour before I could even find out what was happening. The code behind the payment button ended with [ Me.Requery ] I changed it to [ Me.Refresh ] and I got the results I wanted. The focus stayed on this record and updated as needed! You have probably discovered this by now.
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP