Introduction

Welcome! Customer Discounts & Taxes

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 9. In this course we will focus on working with multiple forms in Microsoft Access, specifically setting up customer discount rates and sales tax rates that flow from customers to orders and order line items. We will discuss building a system where each customer can have a different discount and tax rate, mark specific products as taxable or non-taxable, and adjust related calculations. We will also walk through creating a printable invoice, discuss using report features like subreports and the Can Shrink property, and cover the recommended approach for following along and getting course help.

Navigation

Prev: X09
Next: Lesson 01

Keywords

Access Expert, order entry form, customer discount rate, propagate discount, sales tax rate, tax exempt customers, taxable products, non-taxable items, printable invoice, subreports, combo box taxable, can shrink property, set default tax rate, line item

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Expert Level 9 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to continue our focus on multiple forms and how they work together. We are going to start out by setting a customer discount rate on the customer form, which will then propagate down to the order form when you create a new order for that customer. The customer has a 25 percent discount, and any new orders that you put in the system for that customer will also get a 25 percent discount. That will also then propagate down to the line items on the order. We will see how all that works.

Then we will see how to do something similar with multiple tax rates. We will set it up so each customer can have their own different tax rate. Some customers may be tax exempt, so their rate would be zero. Others may be in a particular county that has an 8.5 percent sales tax. Others might be a 7.5 percent sales tax. The sales tax rate will then also propagate down to the order, which will determine whether each item on the order is taxable and what the rate is.

Then we will take into consideration that certain products may or may not be taxable. For example, a loaf of bread you would not pay sales tax on, whereas a new computer you would. We will add taxable or not taxable to the product combo box, so you can see it right there, and then we will adjust our calculations accordingly.

Even if you are not building an order entry system for your own use, all the techniques that I am going to show you, having values work between forms and propagating across different records, this is very important stuff, regardless of what kind of database you are building. Having your tables and your forms being able to work together is extremely important.

Then once we are done with all that stuff, we will start building a printable invoice, so we can actually print something out to give to our customer, or send it to them as a PDF file. We will learn some new report techniques, including inserting subreports into our main reports.

This class is a continuation of the Access Expert Level 8. We learned a lot of the stuff that we are going to continue working with in this class. The products and vendors form, calculated query fields are very important. The initial sales tax calculations that we are going to edit in this class. The IF function immediate IF, which we are going to again continue working with today, rounding values, and we built the initial order form and subform in Access Expert Level 8. If you have not watched that class, I strongly recommend you do so before continuing with today's lessons.

This class was designed to be used with Access 2013. If you are using 2007 or 2010, you should not have any problems following along as the three versions are almost identical. If you do happen to come across something that is significantly different, please email me and let me know. If you are using Access 2003 or earlier, you should be able to follow along with the lessons covered today. Most of the material is pretty much the same. There is no exact match for this class in my older lessons. The material that I am covering today has come from several different lessons and there is some new stuff added in. I used to not cover order entry at all until my Advanced Lessons Access 301. However, now I have decided to move some of the material into the Expert Series. So this is kind of a patchwork of material covered from various other classes. But you should be able to follow along. The menus are a little different, but the rest of the stuff is all the same.

You are strongly encouraged to build the database that I build in class as it is the best way to learn. However, if you are lazy like me, go ahead and download it from my website at accesslearningzone.com/databases.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert Series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced Lessons cover working with macros, automation, and many more advanced features. The Developer Lessons get into programming with Visual Basic for Microsoft Access. Each of the series is broken down into different numbered levels, starting with Level 1. The beginner series, for example, had Levels 1 through 9.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here, you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson, and feel free to post your own questions and comments as well. If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

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

In Lesson 1, we are going to take the order entry form that we started building in Access Expert 8, and we are going to add on a customer discount rate. So each customer can have their own discount rate, which propagates down to their orders, and that propagates down to all the line items on the orders. Then we will recalculate the sales tax and figure out a new grand total.

In Lesson 2, we are going to set up multiple sales tax rates, so each customer can have their own sales tax rate, whether they are taxable or not, or if they are in a different location. Each order can have its own sales tax rate, you may have a tax exempt order, and each item can have its own sales tax rate, you may have a tax exempt product like a gallon of milk. We will see how to set up system defaults so new customers get whatever default tax rate you have set up without embedding that value inside of your tables and queries.

In Lesson 3, we will learn how to make certain products non-taxable. So in addition to the customers having their own tax rate, each order having its own tax rate, each item having its own tax rate on the order, we can also say that if the product itself is not taxable, like a loaf of bread, then do not charge sales tax.

In Lesson 4, we are going to take our order entry system that we are building and create a printable invoice.

In Lesson 5, we are finishing up our printable invoice. We will take a look at some problems with the Can Shrink property to get rid of blank spaces for missing data, and we will learn how to open a report to a specific record so we can print just one specific invoice.

Intro

Quiz

Q1. What is the main focus of Microsoft Access Expert Level 9?
A. Working with multiple forms and having values propagate between them
B. Building web-based databases
C. Learning VBA programming
D. Tracking accounts payable and receivable

Q2. When setting a customer discount rate, what happens when you create a new order for that customer?
A. The discount rate must be entered manually for each order
B. The discount rate is automatically applied to the new order
C. No discount rates can propagate to an order
D. Only the tax rate applies automatically

Q3. What feature allows for different customers to have different sales tax rates?
A. Using a fixed tax rate for all orders
B. Assigning a tax rate to each customer that propagates to orders and items
C. Only applying a tax rate to products
D. Allowing users to select tax rates from a dropdown on each order

Q4. How does Access handle exempt and non-exempt products in the context of sales tax calculations?
A. All products are always taxable
B. The product combo box includes a taxable or not taxable status, which adjusts the calculations
C. Users are required to remember and manually exclude non-taxable products
D. There is no way to mark products as non-taxable in Access

Q5. What is the significance of having tables and forms work together in Access?
A. It makes the database look more professional
B. It allows for the automatic sharing and propagation of values between related records and forms
C. It increases the file size of the database
D. It makes it harder for users to input data

Q6. What is a new reporting technique taught in this class?
A. Embedding Excel charts in reports
B. Inserting subreports into main reports
C. Exporting reports only to HTML
D. Creating pivot tables inside forms

Q7. What is recommended before trying to apply these techniques to your own projects?
A. Apply concepts directly without watching the lesson again
B. Build the same sample database used in class
C. Read only the written manuals
D. Skip the sample and work on your own database immediately

Q8. How are sales tax rates assigned to customers by default?
A. Users must enter them manually every time
B. Using system defaults for new customers without embedding the value in tables and queries
C. Randomly selecting from existing tax rates
D. Assigning the highest possible tax rate

Q9. What role does the Can Shrink property have in printable invoices?
A. It makes all text larger on reports
B. It removes blank spaces caused by missing data
C. It adds a watermark to invoices
D. It encrypts customer data

Q10. What is the suggested way to learn from these video courses?
A. Just watch the videos and take written notes
B. Watch the lesson once, then build the database along with the replay
C. Only read the course transcript
D. Try to apply techniques directly without practice

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

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

Welcome to Microsoft Access Expert Level 9 from AccessLearningZone.com. I am your instructor, Richard Rost.

In this lesson, we continue to focus on how to work with multiple forms in Access and have them interact with each other efficiently. The first topic we cover is managing customer discount rates. For example, you might have a customer who always gets a 25 percent discount. I show you how to set this discount rate on the customer form so that whenever you create a new order for that customer, the discount automatically applies to both the order and all of its line items. This ensures the discount is consistent throughout the entire order process.

Next, I explain how to handle multiple tax rates for your customers. Some customers might be tax exempt, while others are subject to different rates depending on their location - such as an 8.5 percent or 7.5 percent sales tax. Each customer can have their own tax rate saved on their profile, and when you create a new order, that rate is carried down so that items are taxed correctly according to the customer's profile.

After that, we consider the tax status of individual products. Certain products, like a loaf of bread, might not be taxable, whereas electronics or other goods are. I guide you through modifying the product combo box so you can easily see and set whether a product is taxable. We then update our calculations to make sure only taxable items are included when calculating sales tax on orders.

Even if you are not building an order entry system, the techniques taught in this class are essential for any database structure where information must flow and update seamlessly between tables and forms. Ensuring your forms and tables can interact smoothly is a crucial database skill that will help you regardless of your specific application.

Once we finish building the order management features, I move on to demonstrating how to create a printable invoice. This will allow you to provide your customers with either a printed or PDF copy of their order, using reports in Access. Along the way, you will learn how to insert subreports and use some new report design techniques.

This course builds on what was covered in Access Expert Level 8. We rely on the products and vendors forms and the calculated query fields from the previous class. The sales tax calculations we originally created there will be extended. You should also be familiar with the IF function (immediate IF) and rounding values, as these are all carried forward into this lesson. If you have not taken Access Expert Level 8, I recommend going back and completing it before continuing here.

This class is designed for Access 2013, but you will be able to follow along with versions 2007 or 2010 because they are almost identical. If you are using Access 2003 or earlier, much of the core content still applies, but the menus and layouts may be different. The material in this lesson is a mix of several earlier lessons, with some new features added in, and it represents a more current approach to order entry than what was found in my older 301-level advanced classes.

The best way to learn is to build the sample database right along with me as you watch. However, if you prefer, you can also download the finished database from my website. All of my tutorials are grouped into Beginner, Expert, Advanced, and Developer tracks. The Expert Series is geared towards users who are already comfortable using Access and want to learn more advanced techniques. The Advanced and Developer levels go even deeper, covering things like automation, macros, and VBA programming.

I also offer a variety of seminars on targeted topics, such as building web-based databases, creating calendar-style forms and reports, securing your databases, using images and attachments, managing work orders, accounts payable, and learning SQL. Details about these seminars are available on my website.

If you have any questions about the material in today's lessons, I encourage you to use the student forums. When watching through my video player or online theater, the forum for each lesson will appear alongside the class videos if you have an internet connection. Here you can see what other students have asked and share your own questions or comments. If you are not watching online, you can always visit the forums later via accesslearningzone.com/forums.

For the best results, I advise that you watch each lesson all the way through without stopping to try the examples. Once you have watched the lesson, replay it and build the same database yourself, following each step as I do it. This method helps you fully understand the process before applying it to your own database projects. If you get stuck, watch the video again or ask for help in the student forum. Remember, keep an open mind. Access might appear complicated at first, but with a little patience and practice, you will find it becomes much more approachable.

Here is a summary of what we cover in each lesson of this course:

Lesson 1: Enhancing the order entry form from Expert 8 to allow each customer to have their own discount rate, which flows down to their orders and line items, updating sales tax and grand totals accordingly.

Lesson 2: Setting up multiple sales tax rates so customers, orders, and products can all have their own rates. We will also look at system defaults so that new customers automatically get a preset tax rate without hard-coding this value in your tables or queries.

Lesson 3: Modifying the system to allow certain products to be marked as non-taxable. If a customer orders a non-taxable product, such as bread, no sales tax is applied regardless of the order or customer settings.

Lesson 4: Creating a printable invoice based on the order entry system.

Lesson 5: Finalizing the printable invoice. I will show you how to handle issues like blank spaces from missing data using the Can Shrink property, and demonstrate how to print a report for a specific invoice record.

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

Setting customer discount rates on customer forms
Propagating customer discount to new orders
Propagating order discount to line items
Multiple tax rates per customer
Assigning tax rates to customers
Handling tax exempt customers
Propagating tax rates to orders
Determining taxable status for order items
Making specific products taxable or non-taxable
Updating product combo boxes to show taxable status
Adjusting sales tax calculations based on product tax status
Building a printable invoice in Access
Inserting subreports in main reports
Fixing Can Shrink property issues in reports
Printing reports for a specific record (single invoice)

Article

Welcome to this tutorial on working with multiple forms in Microsoft Access, focusing on how data can flow between them, specifically in an order entry system. This article will guide you through the process of setting up customer discounts and tax rates so that values automatically populate onto orders and their line items. We will also look at handling product-specific tax rules and how to create printable invoices. If you are new to these ideas, do not worry - the step-by-step approach will help you understand the underlying concepts and techniques.

Let us start by considering a scenario where every customer can have a different discount rate. For example, you might offer a regular customer a 25 percent discount on every order. When you select that customer on a new order form, the discount rate should automatically carry over from the customer record to the new order, and every line item on the order should also reflect this discount. This way, you maintain consistency and save time manually re-entering the same information.

To implement this in Access, first make sure your customer table includes a field for DiscountRate. On your customer form, you should have a place to enter or view the discount rate for each customer. When you create a new order for a customer, you want the order form to automatically retrieve the DiscountRate from the selected customer and store it in the order record. This ensures that the correct discount is applied without any extra effort.

To achieve this, you will usually use VBA (Visual Basic for Applications) code behind the order form. Suppose your order form has a CustomerID combo box and a DiscountRate field. When the customer is selected, you can use code like the following to look up and set the discount rate. Here is an example that you might use in the AfterUpdate event of your CustomerID combo box:

Me.DiscountRate = DLookup("DiscountRate", "CustomerT", "CustomerID=" & Me.CustomerID)

This tells Access to look up the DiscountRate value from the CustomerT table where the CustomerID matches the one selected, and sets that value on the current order. Now every new order you create instantly gets the customer's discount. You can use the same principle for other repeating customer-specific values, such as a default sales tax rate.

Speaking of sales tax, businesses often need to accommodate different sales tax rates for different regions or customers. Some customers are tax exempt, while others pay rates based on where they live. To handle this, add a SalesTaxRate field to your customer records. On your order form, use the same DLookup method to pull in the correct tax rate when a customer is selected. For example:

Me.SalesTaxRate = DLookup("SalesTaxRate", "CustomerT", "CustomerID=" & Me.CustomerID)

For customers who are tax exempt, you would set their SalesTaxRate to zero. If you have customers from various counties or states with different tax rates, simply set their individual rates as needed. This ensures that each order is charged the correct sales tax depending on the customer.

Now, let us take it one step further. Sometimes, only certain products are taxable. For instance, basic groceries might not be taxed, while electronics are. You can add a Taxable Yes/No field to your Products table and display this as a checkbox in your product combo box. When adding items to an order, check this flag to determine how to calculate tax for each line item.

In your order details (sometimes called order lines) table, store the Taxable value for the product when you add the line item. This way, even if the product's tax status changes in the future, each order will still reflect how the tax should be applied at the time of the order. In the calculation for the line item sales tax, you can use the Immediate If (IIf) function to decide whether to apply tax. For example:

LineItemSalesTax = IIf([Taxable]=True, [LineTotal]*[SalesTaxRate], 0)

This calculates tax only when the product is taxable. LineTotal is the price for that order line, SalesTaxRate is taken from the order.

Bringing all of these pieces together, when you create a new order, Access should automatically populate both DiscountRate and SalesTaxRate from the customer. As you add products to the order, each line will check if the product is taxable, and calculate sales tax appropriately.

Once your order entry and calculation systems are working, the next step is to create a printable invoice. Build a report in Access based on your order and order details tables. To ensure that products, discounts, and taxes are all displayed correctly, set up the necessary fields in your report and use subreports if you need to display line items within the main invoice. This layout allows you to print or save invoices for your customers, showing all order details and final totals.

Sometimes you may want to print just a single invoice for a specific order. To do this, add a button on your main order form that opens the invoice report filtered to the current order. You can use VBA like this on a button's click event:

DoCmd.OpenReport "InvoiceR", acViewPreview, , "OrderID=" & Me.OrderID

This opens the report showing only the invoice for the order you are currently viewing. It is a simple way to select and print the correct record.

If you want to clean up blank spaces for missing data on your reports, use the Can Shrink property in Access report design for each report section. Setting Can Shrink to Yes ensures that sections with no data collapse to avoid empty spaces on the printout.

Throughout this process, remember that you can reuse these techniques in many other types of databases, not just in order entry systems. Whenever you need to move values between tables and forms, such as copying shipping addresses, recording special instructions, or setting custom rates, these Access features and VBA scripts help you control how data flows and is calculated throughout the application.

If you run into trouble or want to add even more complex features - like system defaults, error checking, or automation - build on these foundations. Be sure to test everything after each change, especially calculations, to ensure the correct discount and sales tax are being applied across all new entries.

By following these steps, you can create a responsive, automated order entry system in Microsoft Access, ready to calculate discounts, handle variable tax rates, obey specific product tax rules, and generate professional invoices for your customers.

Primary Topics

multi-form value propagation, customer discount rates, per-customer tax rates, order-level calculations, product taxability, invoice reporting

Secondary Topics

system defaults for new customers, subreports, report formatting