Access 2007-2016
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  
Courses - Microsoft Access 306
Description: Advanced Access
Running Time: 83 minutes
Pre-Requisites: Access 305 very strongly recommended
Previous Lesson: Access 305
Next Lesson: Access 307
Main Topics: Sales Commissions, DMAX, Me.Requery, InputBox, Nested IIF, Passwords, Overtime
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 4/28/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details


Access 306 continues our advanced Access database development with VBA programming.

This lesson focuses calculating employee commission rates and pay rates. Sounds easy? Well we will learn about all kinds of new concepts like DMax, Me.Requery, InputBox, nested IIF functions, and much more.

We will calculate an employee's commission paid based on his commission rate using an IIF function:


We'll learn how to use DMAX to calculate the commission that he should be paid - based on his sales - according to our commission table.


We'll create a form to just show Paid Commissions, Unpaid Commissions, or both. We'll learn the Me.Requery command to refresh the form results when the box is checked or unchecked.


We'll create a report we can print out to see all of the commissions our sales reps need to be paid.


We'll make a button that we can then click on to mark all of the commissions as PAID, and we'll throw in a little "Are You Sure?" message box prompt.


We'll create a button on our Main Menu form with a PASSWORD on it. This way someone has to type in the password in order to open the employee commission form or report.


Next we'll work on employee pay rates. We'll learn how to calculate regular and overtime hours, which will let us calculate regular and overtime pay.


Lesson Description Time
00 Welcome 3:43
01 Employee Commission Rates based on single rate, based on different rate per employee, based on order amount. Nested IIF Functions. 19:42
02 CommissionLookupT, paying employees commission based on order amount and separate rates for each employee. DMax. Using DMax with DLookup to pull a commission rate based on employee and largest order amount. (Note DMin is same as DMax but looks up the smallest value) 13:18
03 Commission Form. Me.Requery on opening a form (getting no records because underlying query is based on a checkbox on the form). 8:31
04 Commission Report to group commission amounts by employee. Update query to mark all commissions paid and track date paid. Using InputBox to ask for a password when a button is clicked. 18:39
05 Employee PayRateT. Calculating employee pay rates based on employee and dates worked. Another DMax, DLookup example. Calculating regular and overtime hours and total pay rates. 15:23
07 Review 3:05
  Total Running Time: 83 minutes



Try a FREE Demo Lesson


Student Interaction: Microsoft Access 306

Richard on 1/1/2007:  Employee Commission & Pay Rates, Nested IIF functions, DMAX, Me.Requery, InputBox, Passwords, Overtime Hours
BRYAN binkerd on 5/14/2009: I think the showings of the mistakes and how to correct them are great, however, I might recommend that if you added that portion to a troubleshooting chapter, it would allow the user to learn the "perfect world" scenario and to recap the user's knowledge and awareness of the troubleshooting techniques.
PLEASE do not take it this the wrong way. If it is a pain, then leave it as is. I find the mistakes very useful and have caught some of the mistakes on my own. (Standing up and bowing for the loud applause) sad thing is it was only 2 out of the entire series. Keep up the great work
(Chanting "Go Rick, Go Rick, it's your birthday"

BRYAN binkerd on 5/14/2009: Help for the dummy,
I used names rather than IDs to store values. Is there a parameter value such as LIKE "*dummy*" for text rather than ID references.

Richard Rost on 5/19/2009: Bryan, yes, you can use the LIKE keyword in a query just as you have it there. I cover it way back in Access 102:
Ruth Spohr on 11/21/2009: I agree with Bryan about a trouble shooting chapter so the lessons are clearer. It also becomes difficult to refer back to the handbooks when you must wade through incorrect methods to find the correct information.
Richard Rost on 11/25/2009: I actually get a lot of compliments on the fact that I do cover several wrong methods showing how to arrive at the right ones. I like to show people the common mistakes that they're likely to make because I know I've spent many hours being frustrated by just such mistakes. Don't you feel this is valuable?
 Chris on 3/16/2010: Richard, the fact you do show the alternate methods and the foibles is what makes your courses so valuable. Seeing the mistakes and your comments takes the material to a whole new level. This is because we are gaining two things: Your notable experience and new knowledge on how to start from an empty database and develop an app that is as complex as this working example. I too have struggled through the interpretation of reading material and have found they cover most of the material; but they all have holes in the material. I have so far enjoyed the opportunity to go right from Acess 101 to Access 306. This material has changed my view of "How Can I Create my Project?" to "I Can Create my Project!". Thank you.

Reply from Richard Rost:

Thanks for the kind words, Chris.

Mubeezi Micah on 11/19/2010: Dear Richard,

At 13:55 a VBA command does not run because we did not Dim our variable (My Reply). However at 18:16, when we use a variable my password without "diming" it, the VBA command runs perfectly. At first i thought it would bring an error but when i tried it on my database, it worked well without "Dim MyPassword". Why does it work in this particular case?
Help me and clarify. Thank you,


Reply from Richard Rost:

You only NEED the Dim command if you have "option explicit" specified at the top of your VBA code. If that's there, VB will force you to EXPLICITLY declare all of your variables. If not, you don't have to. It's GOOD practice to leave this setting ON because it avoids mistakes in your code due to misspelled variables. However, it slows down the development process because you have to take the extra time to manually Dim all those variables. Your call.

David on 5/31/2011: me.requery and me.refresh
What is the difference and when do I use each?
I have a main form based on a query amalgamating three tables with the main form having the autonumber ID and the two minor tables have a numberID equal to the mainform's autonumber.
I click on a button on the main form to open one or other of the minor forms at its related record (IDs related) to enter occasional details in the minor form (which is why it is a minor form and the fields are not part of the main form).
I want to update the totals on my main form following such entries and the fields in the underlying query to recalculate. at present I use the following expression on the "after update" event
[frmBookAppointment] being the main Form.
It does not seem to work until I close and re-open the main form - should I use recalculate instead of refresh on the main form?
Some of the entries on the minor form affect the calculations on the main form.
Where am I going wrong?
Many thanks

Kevin O'Malley on 6/24/2011: Hi Richard, I have recreated the DMax formula faithfully but I do not get neither a value or an error message for TeirAmount when I run the query. I am using 2007. How do I spot the problem?
Kevin O'Malley on 6/24/2011: I could not get the TierAmount DMac function to work until I changed SalesRepID in the CommissionLookUpT to EmployeeID. After that the function worked perfectly.
Joni Moore on 9/23/2013: At the very end when you discuss a quick way to add a password input box, how do you make the password not visible - just masked with *** instead?

Reply from Richard Rost:

Just set the INPUT MASK property of the field to PASSWORD.

Joni M on 9/27/2013: Richard... sorry, I'm confused - again :-)
This is the code I wrote:
Private Sub btnInvoices_Click()
Dim MyPassword As String
MyPassword = InputBox("Enter Password", "Password")
If MyPassword = "mypassword" Then
DoCmd.OpenForm "frmInvoices"
MsgBox "Your request has been canceled or you have entered an invalid password. Please try again or contact your System Administrator.", vbCritical, "Sorry..."
Exit Sub
End If
End Sub

On other fields there would be an INPUT MASK option under the Data tab of the Property Sheet, but buttons don't have this option. Is there a set of code I could use instead?

Reply from Richard Rost:

Sorry, I didn't catch that you're using an actual InputBox function for this. No, there's no way to mask the input in this case. What I have done in the past is to create my own little logon form with a single text box (password) and an OK button. Make it popup and modal, and have the OK button check for the password.

Anna K on 9/28/2015: Hi Richard/Alex
I am trying to build a payroll databaase. I am having trouble with pay rates. Would access course 306 help me? Or is it out of date? I have purchased all of your access beginner and expert classes.
I have different payrates for all of my employees and different payrates for different times of the day and days week
Could you recommend a class that could help me?
Anna Keane

Reply from Alex Hedley:

Looks like it would cover it.
Although its an old course there is no reason why it wouldn't work in updated versions, the UI may look a little different but the concepts would remain the same.

Spiros Poulis on 4/22/2016: I am trying to figure out how to amend your example if the commission is progressive. For example for $650 the commission is (50*2%+50*2.5%+400*3%+150*3.25%). Is it possible or i must use multiple IIFs()?

Reply from Alex Hedley:

Anything is possible :)
You can nest IIFs but it can get messy.

simon b on 4/23/2016: Best way forward is to write a custom function to perform the task and call it from your query, this is less prone to error that multiple IIF statements and easier to update as things change

Reply from Alex Hedley:

Good idea


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


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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