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
 
 

< Previous: Access Expert 8

Next: Access Expert 10 >

Access Expert Level 9

Expert Microsoft Access Tutorial - 1 Hour, 45 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 8 left off. In this class we will continue to learn how multiple forms can link together to share data. We will work with customer and order discounts, sales tax rates, and creating a printable invoice report. Topics include:
 
  - Sharing Data Between Forms
  - Customer & Order Discount Rates
  - Default System Values Table
  - Multiple Sales Tax Calculations
  - SQL ALIAS
  - Printable Invoice Report
  - Inserting Subreports
  - Problems with Can Shrink
  - Open Report to a Specific Record

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.




 

Access Expert Level 9
Description: Access Expert Level 9
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 8 strongly recommended
Running Time: 1 Hour, 45 Minutes
Cost: $24.99


This class picks up where Expert Level 8 left off. We will start by reviewing my solution to the homework assignment you were given in the last class. You were to use the discount rate on the customer form to set a discount rate on the order form, which in turn sets the discount rate for each line item on the order. This is an exercise in making multiple forms work together to share data.

 

Next, you will learn how to set up a similar relationship to calculate sales tax. Each customer can have their own sales tax rate (some are tax exempt, others out of the state, etc.). Each order can have its own sales tax rate based on the customer. This will set the sales tax for the line items. We'll also set up a system default values table so we can set the default sales tax rate for new customers.

 

To add another level of complexity, some products are taxable (computers) while others are not (a gallon of milk). We need to make our database smart enough to realize when to not calculate sales tax based on the product selected. You'll also learn about SQL ALIASes, and how to use the IIF function to display a "T" for taxable in the product combo box.

Now, keep in mind that even if you're NOT ever going to work with sales tax in your database, these techniques are useful for many different types of situations. This is just the example that I've chosen. You should learn this stuff!

 

Next we'll spend a good deal of time putting together a printable invoice report. You'll learn how to insert a subreport, work with report headers/footers and page headers/footers, create an order query with additional customer data, and more.

 

Continuning with our invoice report, we'll add the company info to the header. If we're missing a company name, for example, we want to use the Can Shrink property to get rid of that empty space - but there are some problems we have to learn how to address first. We'll learn how to deal with adjacent and overlapping controls in our reports. You'll see how to use a query criteria to open a report to a specific record so you can print just one specific invoice.

 

This is the ninth class in the Access Expert series. This class is crucial if you want to build an order entry system and calculate sales tax, but even if you think you're NEVER going to need to work with this stuff, the concepts taught in this class will help you regardless of the type of database you're building. Of course, if you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Access Expert Level 9

00. Intro (8:39)

01. Customer Discount Rate (20:26)
Discount Rate for Orders
Discount Rate for Order Details
Different Validation Rules
Recalculate Sales Tax
Tab Cycle Property
LineTotal

02. Multiple Sales Tax Rates (17:42)
SalesTaxRate for Customers
SalesTaxRate for Orders
SalesTaxRate for Order Details
Values Propagate Down Forms
Disable Close Button
Disable Control Box
System Default Values Table

03. Non Taxable Products (12:01)
IsTaxable for Products
Show IsTaxable in Product Combo
SQL ALIAS
IF Product Tax Exempt
Another IIF Example
04. Printable Invoice 1 (20:27)
Order Detail Subreport
Report Header / Footer
Page Header / Footer
Order Info with Customer Data
OrderQ
Customer Bill To Info
Insert Subreport

05. Printable Invoice 2 (19:58)
Company Info in Header
Missing Company Name Gap
Can Shrink Problem
Adjacent Control Problem
Overlapping Controls
TRIM Function
Open Report to Specific Record
Query Criteria
Company Return Address Info

06. Review (6:08)

 


 
Keywords: Printable Invoice, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, discounts, discount rate, validation rules, sales tax, line total, disable close button, disable control box, SQL ALIAS, IIF Function, order details, page header, page footer, report header, report footer, subreport, can shrink, trim function, query criteria
 
 

Student Interaction: Microsoft Access Expert 9

Richard on 7/11/2013:  Microsoft Access Expert Level 9 is 1 hour, 45 minutes long and continues to focus on building our Order Entry System. In this class we will continue to learn how multiple forms can link together to share data. We will work with customer and order discounts, sales tax rates, and creating a printable invoice report, and lots more. Topics include: - Sharing Data Between Forms - Customer & Order Discount Rates - Default System Values Table - Multiple Sales Tax Calculations - SQL ALIAS - Printable Invoice Report - Inserting Subreports - Problems with Can Shrink - Open Report to a Specific Record Click here for more information on Access Expert Level 9, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 8. The class following this is Access Expert Level 10.
Michael on 7/11/2013: Dear Rick

Are you sure you mean August and not July (in two weeks)?

Michael

Reply from Richard Rost:

You are correct - that should read JULY 25th... duh. :)

Chad Visker on 7/16/2013: Is there a handbook for Access Expert 9?

Reply from Richard Rost:

Yes. Alex emailed it to me, I just have to get it online. I should have that done tomorrow.

Larisa Kiseleva on 7/16/2013: Is your calculation from query like total amount (sells price + taxes) is saving data into table, or just show in form and query result?

Reply from Richard Rost:

Calculated query fields do NOT save the data in the table, and they don't NEED to. You can generate these values on the fly. This is almost always the way you want to do it. There are some rare exceptions, and I'll talk about them in the next class.

Bruce Reynolds on 8/12/2013: I really like the concept of building a variables table for sales tax rates. This can be applied to many other types of other problems that share the same requirement.

Reply from Richard Rost:

Certainly.

Bruce Reynolds on 8/12/2013: Do you have any lessons covering the VAT tax?

Reply from Richard Rost:

Sorry, no. We don't generally have that here in the United States, so I haven't covered it at all. It shouldn't be too hard to figure out, however the rules are different for different countries.

Bruce Reynolds on 8/12/2013: The key independent variable which determines tax rates is the geographical location of the customer. In this scenario, someone has to conduct the appropriate research to then input the applicable tax rate in the customer table.

Theoretically, one should create a master table based on zip code that has the sales tax rates. Then, create a one to one or one to many relationship between the zipcode in the Sales tax table and the zipcode in the customer table. This way, the key independent variable (location) is driving the sales tax rate, instead of a person figuring it out and inputting the sales tax rate manually.

Reply from Richard Rost:

Here in the US, your sales tax is determined by the state you're in, but only if you have a physical business presence there. For example, I'm in New York State, so I only have to collect sales tax to customers from NY. If someone from California buys my products, I don't have to charge them sales tax. They're responsible for paying it themselves to their own state (called a Use Tax).

Furthermore, my county (Erie) has a set sales tax rate, which is 8.75%, so I charge this to everyone from NY no matter where they are in NY. The "point of sale" is my address. Even though different counties have different tax rates, that's not my problem UNLESS I have a physical business presence in those counties.

So... if you have multiple stores, then YES, you need to track the sales tax rates for those stores. Otherwise, you only need to worry about ONE tax rate, and whether or not the customer is in your state.


Bruce Reynolds on 8/12/2013: The copy and paste feature between the form and the report is a real time saver.

Reply from Richard Rost:

Yes, it certainly is!

Bruce Reynolds on 8/12/2013: So, case sensitivity is not an issue for the related field between the regular report and the sub-report?

Reply from Richard Rost:

Nope. Access (and VB in general) isn't case sensitive at all... which really messes me up when I build something in C++ or JavaScript, which ARE case sensitive.

Brauna Rosen on 9/25/2013: Hi Richard,
When I insert a sub report into a report, I can see it but when I open the entire report using a button to open, it vanishes. Can you give me an idea of why that might happen?
Thanks,
Brauna

Reply from Richard Rost:

I really can't think of anything aside from the obvious (visible property NO, for example). I've never seen this happen before. Is it possibly forced down to page 2 and you just don't see it?

Ramona Woitas on 12/4/2013: Expert 9 at 14:53 = I have page 1 at the bottom of my screen however I can click on the right arrow and it will show me the same invoice again. In fact I can see 10 of the same invoice.

I have no idea what I have done wrong.

Ramona Woitas on 12/4/2013: Expert 9 Lesson 5 at 2:57.
When I am in design view and I want to click on the print preview icon, I get a this message:

Enter parameter value
Forms!OrderF!OrderID

I'm not sure what I did wrong. it used to work.

Fernando Figueroa on 12/5/2013: Rick,

For some reason my DB is going berserk and opening the printable report when clicking anywhere in the OrderF. I've deleted the button and tried changing some of the settings but have had no luck on getting the OrderR to stop popping up. Any suggestions?

SHIVJI PATEL on 3/3/2014: I want to use system default for employee name. So when start Main menu select his name and he opens continues open forms. I gave default value of main menu employee name in that form. So gets data related to that employee only. But while query design if I put default value of main form in criteria than it does't filter. so in Query criteria for filter we have to put only name for filter ? we can't filter by default value ?
vicki Hudson on 3/20/2014: I copied and pasted the Description label to put my PCResale.Net address in but I cannot get a new line after I type PCResale.net. If I press the new line enter key it pus the orange box around the field. I am unable to add a new line. Why?
vicki Hudson on 3/20/2014: opps Time stamp 7:42
Alan Lipps on 6/19/2014: Any ideas why I am getting a message stating "You must enter a value in the 'OrderDetailT.OrderID' field. I am trying to enter a new order, using the OrderF, for Richard Rost after following Lesson 1 in Expert 8. I was trying to change the Discount Rate to 10%. Once I click OK, the message disappears, but if I select a Product, and then try to move down to enter another product (i.e., write the first order item to the OrderT) I get a message stating "You cannot add or change a record because a related record is required in table 'OrderT'."
Alan Lipps on 6/19/2014: I just figured out that entering a description, or changing the discount rate, is required before the order ID updates from "New" to a number. That solves that problem. The Autonumber (or new record)does not update until some action occurs on the OrderF regardless of what happens on the Order Detail subform.
Lynda Chase on 8/6/2014: This doesn't relate to this particular lesson, but rather all the lessons, is there a glitch with Access when it comes to designing - I find it really difficult to select and move, expand etc any objects when in design view, its like I have to keep trying over and over again - is there a setting that I may have wrong on my pc or something?

Reply from Richard Rost:

Lynda, I'm not exactly sure what you mean. I haven't noticed any glitches like this in Access.

Lynda C on 8/7/2014: Hi Richard, basically, when i am in design view, if i try and select a group to move or click on something to adjust the sizing, i often have to do it several times before it will actually 'hold' the item i want - sorry i realise this is about as clear as mud, don't really know how else to describe it

Reply from Richard Rost:

I would really have to see what you're talking about to tell you what the problem is. I'm not aware of any glitches like this. Moving from Access 2003 to 2007 was a bit jarring as they added the wacky "layout view" which I NEVER use, but aside from that, design view (once you remove the automatic layouts) works the same way.

Lynda C on 8/7/2014: Hi Richard, Well after much frustration and googling, it seems my $100 mouse is a total waste of money, a $10 mouse works perfectly - go figure, so please ignore my query - it was the mouse that was the problem :o)

Reply from Richard Rost:

It's always the simplest solution. Check your mouse for dust or lint around the optical eye. Also, make sure your battery is fully charged and your USB connection to the base is good.

asabur on 1/25/2015: I wish to see number to text (string) in form and report
exmpl: 100 = One Hundred only
thanks

Reply from Alex Hedley:

You'll learn that in the Access Payables Seminar:

From the Outline
"Yes, we'll even use a function to turn $18.97 into "Eighteen Dollars and Ninety Seven Cents.""

Tim Florio on 8/10/2015: Greetings Richard, I've spent the last couple mornings trying to resolve this issue without any luck. As far as I can tell I copied and pasted the "DiscountRate" text box on the CustomerF on to the OrderF and OrderDetaiF. I changed the Control Source to =Forms!CustomerF!DiscountRate and =Forms!OrderF!DiscountRate. When I open the OrderF from the CustomerF the DiscountRate From CustomerF is populated into the DiscountRate on both the OrderF and OrderDetailF but they are not editable. I checked the properties of the text boxes and "Allow Edits" is yes and the text boxes are not locked. I don't want to move forward until this is corrected as it may pose a problem later in course. Help!!!!

Reply from Alex Hedley:

Would you not need the DefaultValue to be this instead of ControlSource?

Cheryl Hokanson on 8/27/2015: Richard,
I'm trying to include Barcodes in a working database. I have a subreport barcode, OrderID/ProductID on a order ticket. The first one is positioned correctly on the ticket but the other subrport barcodes all stack up on each page not in the position that I placed them in design view.
What do you think?

Reply from Alex Hedley:

If you go to Design View have you pulled the bottom up to the bottom of the last Field?

Cheryl Hokanson on 8/29/2015: Alex,
The answer came to me in the wee hours of the morning. I had the master and child link set to OrderID, this would only give me a barcode for one order but the ticket is for 'the many' which is found on the OrderDetailID. I changed and walah....each ticket had its own barcode.


Reply from Alex Hedley:

Great stuff, thanks for letting me know, this could help out others if they come across the same problem.

jose acevedo on 11/5/2015: I am trying to print one invoice access expert 9 4 and 5 I watch the video 100 time and still I do not get it... when I print all the file from form print not from the report... help please

Reply from Alex Hedley:

Hi Jose, I'm not sure I understand your question, are you wanting to only print 1 record?

Virginia Mergl on 5/18/2016: Hi, regarding Round function would be possible force Access to round UP, for example 4.45 I want to be 5
Thank you

Reply from Alex Hedley:

See this tip on INT, ROUND, FIX.

 

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