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  

News      User Comments     History     Notify Me

7/11/2013 2:43:33 AM
Microsoft Access Expert 9
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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

Permanent Link
Course Link: Microsoft Access Expert 9
Keywords: access expert 9 discounts sales tax alias printable invoice report can shrink specific report
Page Tag: whatsnew
Post Reply

I am trying to print one invoice access expert 9 4 Comment from jose acevedo @ 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?
Show Just This Thread        Post Reply
Barcodes Comment from Cheryl Hokanson @ 8/29/2015
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.
Show Just This Thread        Post Reply
Barcodes Comment from Cheryl Hokanson @ 8/27/2015
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?
Show Just This Thread        Post Reply
DiscountRate Comment from Tim Florio @ 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?
Show Just This Thread        Post Reply
Number to Text Function Comment from asabur @ 1/25/2015
I wish to see number to text (string) in form and report
exmpl: 100 = One Hundred only

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.""
Show Just This Thread        Post Reply
glitch with Access Comment from Lynda C @ 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.
Show Just This Thread        Post Reply
glitch with Access Comment from Lynda C @ 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.
Show Just This Thread        Post Reply
glitch with Access Comment from Lynda Chase @ 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.
Show Just This Thread        Post Reply
Comment from Alan Lipps @ 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.
Show Just This Thread        Post Reply
Comment from Alan Lipps @ 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'."
Show Just This Thread        Post Reply
Comment from vicki Hudson @ 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  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?
Show Just This Thread        Post Reply
Comment from SHIVJI PATEL @ 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 ?
Show Just This Thread        Post Reply
For some reason my DB is going berserk an Comment from Fernando Figueroa @ 12/5/2013

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?
Show Just This Thread        Post Reply
Expert 9 Lesson 5 at 2 57 When I am in design vi Comment from Ramona Woitas @ 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

I'm not sure what I did wrong.  it used to work.
Show Just This Thread        Post Reply
Expert 9 at 14 53 I have page 1 at the bottom of Comment from Ramona Woitas @ 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.
Show Just This Thread        Post Reply
Subreport vanishes Comment from Brauna Rosen @ 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?

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?
Show Just This Thread        Post Reply
Case Sensitivity Comment from Bruce Reynolds @ 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.
Show Just This Thread        Post Reply
Copy paste Comment from Bruce Reynolds @ 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!
Show Just This Thread        Post Reply
Sales tax in the States Comment from Bruce Reynolds @ 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.

Show Just This Thread        Post Reply
Do you have any lessons covering the VAT tax Comment from Bruce Reynolds @ 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.
Show Just This Thread        Post Reply
I really like the concept of building a variables Comment from Bruce Reynolds @ 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:

Show Just This Thread        Post Reply
Is your calculation from query like total amount Comment from Larisa Kiseleva @ 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.
Show Just This Thread        Post Reply
Is there a handbook for Access Expert 9 Comment from Chad Visker @ 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.
Show Just This Thread        Post Reply
Microsoft Access Expert 9 Comment from Michael @ 7/11/2013
Dear Rick

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


Reply from Richard Rost:

You are correct - that should read JULY 25th... duh. :)
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 5+1:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



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

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled

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