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  
 
 
 
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 9/4/2014 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"
Thanks
Bryan

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.
Bryan

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: http://599cd.com/site/courselist/access/access102
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,

MICAH

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
Me.Requery
Forms!frmBookAppointment.Refresh
[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
David

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"
Else
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.


 

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