Lessons

Updates

Check out this new video on Variable Scope

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Access Developer 4

Age	Subject	From
4 months	Developer 4 Lesson 2	Ken Wykoff
4 months	Access Developer Lesson 4	Ken Wykoff
9 months	Notes Expanding and Shrinking	Ruth Muthoga
2 years	add tax to OrderDetailT	John Gemayel
2 years	Add tax to order	John Gemayel
2 years	Invoicing from a Lead Source	Arlene De Klerk
4 years	Developer 4 Lesson 1	Luke Worzask
4 years	Developer 4 Lesson 1	A Glenn Yesner
8 years	Microsoft Access Developer 4	Kenny N

Subscribe to Access Developer 4
Get notifications when this page is updated

Intro

In lesson 4 of my Microsoft Access Developer series, you will learn how to add and edit product notes on order details in your Access database, update your commission system to support tiered commission rates based on order amounts, and finish building a form to record and track commissions for your sales reps. We will walk through real examples and work step by step to enhance your order and commission tracking features using VBA programming.

Transcript

Welcome to Microsoft Access Developer Level 4 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This class follows Microsoft Access Developer Level 3. It is recommended that you have taken the beginner, expert, and advanced series before beginning the developer series, and of course, have taken levels 1 through 3 before this one.

In this class, I will be using Microsoft Access Version 2016, but everything covered should work in Access 2010 and 2013 as well, and probably 2007 also.

My courses are broken down into various groups. There are beginners, experts, advanced, and developer. The beginner level classes are for people who have never used a database before. Expert gets you into things like relationships and expert form building. Advanced mostly covers macros - a very short series - and then developer is where we are at now, where we are covering VBA programming.

Each series is broken down into different levels that are each at least an hour long. This is developer level 4.

I recommend that you build the database with me in class while following along with the videos. If you want to grab my database off the website, there is the address. You can log on. You will need the password that you got when you registered for this class in order to download the database.

Now let's take a look at what is covered in today's class.

In lesson one, we are going to begin by adding notes to our order details in our order form. We have the product, the unit price, and the quantity, but we have notes on our products and I want to be able to bring those notes into each order so we can print them out on our invoices and such. Or have the user be able to edit those notes. Right now, we are not bringing them in or giving our people a way to edit that. So in this lesson, we are going to learn how to do that.

In lesson two, we are going to update our commissions system. Currently, an employee gets one flat commission rate, so 3 - that is all they get. In this lesson, I am going to show you how to set up a table so that an employee can get commissions based on the order amount of each sale. For example, you can also tell something, if $400, you get the $3.5 commission; if he sells the $500 order, he gets a 4 commission. You can use this on a per-order basis, or using the same techniques, you can easily change this to adjust as monthly commissions or as annual commissions based on a list of values, not one static amount.

In lesson three, we are finishing up our commissions by order form. We are going to pick the sales rep, pick the order ID. The system will calculate the order amount, bring it up from the order, calculate the commission rate based on the table we created in the last order, pull up the order date, and the paid date is going to be defaulted to today. It will calculate the commission, we can add some notes, and then log it to our commission table and then update the entire form.

Quiz

Q1. What is the prerequisite for taking Microsoft Access Developer Level 4?
A. Completing only the beginner series
B. Completing developer levels 1 through 3 and earlier series
C. Jumping directly into Developer Level 4
D. Only knowledge of basic databases is needed

Q2. Which versions of Microsoft Access does this course primarily reference?
A. Only Access 2007
B. Access 2016, and it should work with 2010 and 2013, and probably 2007
C. Only Access 2013 and 2016
D. Only Access 2019

Q3. What major topic does the Developer series primarily focus on in these courses?
A. Building simple tables
B. Advanced macro design
C. VBA programming
D. Importing/exporting data

Q4. What is suggested for students to do while watching the course videos?
A. Only take notes and watch
B. Memorize all form fields
C. Build the database along with the instructor
D. Skip lessons that seem difficult

Q5. What needs to be done to download the database for the course from the website?
A. No registration required, just download
B. Email the instructor for access
C. Use the password received during registration
D. Pay an extra fee for each download

Q6. What is the objective of Lesson One in this course?
A. Designing a login form for users
B. Adding notes to each order detail in the order form
C. Developing a monthly sales report
D. Creating relationships between tables

Q7. According to the video, how is the commission system enhanced in Lesson Two?
A. Employees receive only one flat commission rate
B. Commissions are calculated based on product category
C. Employees earn commissions based on the order amount using a table of rates
D. Commissions are removed entirely

Q8. What is one benefit of the advanced commission system described?
A. Commissions stay fixed for all employees
B. It can adjust commissions on monthly or annual bases using a list of values
C. It only works for single sales, not recurring ones
D. Sales reps cannot view their commission history

Q9. What does Lesson Three focus on completing?
A. Modifying the notes table
B. Creating a commissions by order form and finalizing the commission automation
C. Redesigning the entire database interface
D. Linking Access with Excel

Q10. What is defaulted to today's date in the commissions by order form, as per Lesson Three?
A. The order date
B. The product shipping date
C. The commission paid date
D. The delivered-to address

Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Microsoft Access Developer Level 4. I am your instructor, Richard Rost. This course builds on Developer Level 3, and I strongly suggest that you have already completed the beginner, expert, and advanced series before starting the developer series. Naturally, you should also have completed Developer Levels 1 through 3 before working through this course.

For this class, I am using Microsoft Access 2016, but the material applies equally well to Access 2010 and 2013, and is likely compatible with Access 2007 as well.

My Access courses are organized into several tracks. The beginner series is aimed at those who have never used a database before. The expert series takes you further by covering topics such as relationships and advanced form building. The advanced series is relatively brief and focuses primarily on macros. Finally, the developer series, where you are now, covers VBA programming so you can really customize your databases.

Each level in the series is at least an hour long and is broken down by topic. Developer Level 4 is today's focus.

I always recommend following along as we build the database together in class. If you would like, you can also download a copy of my database files from the website. Just log in with the password you received after registering for the class.

Now, let me give you a rundown of what we will cover in today's class.

In the first lesson, we are going to enhance the order form by adding notes to the order details. You may already have products, unit prices, and quantities listed, but we want to be able to include notes for each product. This way, whether you want to show notes on the invoice or let users edit them, you'll have that flexibility. Currently, the system lacks the ability to bring in product notes or let users update that information, so that's what we will be addressing.

In lesson two, we will update the commissions system. At the moment, every employee just gets a single, flat commission rate such as 3 percent. I will demonstrate how to set up a table so commission rates can vary depending on the size of each order. For example, if the sales amount is $400, the employee might earn 3.5 percent, and for $500 orders, perhaps 4 percent. This new setup will allow you to handle commissions on a per-order basis, and you can also adapt this technique to calculate commissions based on monthly or yearly totals, using a list of rate values instead of a fixed amount.

In lesson three, we will finish up the commissions by order form. Here, we will let you select the sales representative and the corresponding order ID. The system will automatically calculate the order amount, retrieve that information, and look up the correct commission rate using the table we set up in the previous lesson. It will also show you the order and paid dates, with the paid date defaulting to today. The commission amount will be calculated, you can enter notes if needed, and then the information will be logged into the commission table and the form will refresh with the updated data.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Adding editable product notes to order details
Displaying product notes on order forms
Printing product notes on invoices
Creating a commission rates table based on order amounts
Assigning commission rates by order amount thresholds
Calculating commission rates dynamically per order
Integrating commission selection into order entry
Automatically retrieving order details for commissions
Defaulting paid date to today in commission records
Calculating and logging commissions to a commission table
Refreshing the commissions form after record updates