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
 
 
Courses - Microsoft Access 304
Description: Advanced Access
Running Time: 82 minutes
Pre-Requisites: Access 303 very strongly recommended
Previous Lesson: Access 303
Next Lesson: Access 305
Main Topics: Print Invoice, Quotation, AfterUpdate, OnCurrent, Sorting, Grouping, Force New Page, Image Stretch, Can Grow, Can Shrink, Open Specific Record in a Report
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 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 304
Advanced Access Development

Printable Invoice or Quotation, AfterUpdate Event, OnCurrent Event, Sorting & Grouping Levels, Can Grow/Shrink, More. 82 Minutes
 

AC304 Major Topics

  • Printable Quote or Invoice
  • AfterUpdate Event
  • OnCurrent Event
  • Sorting & Grouping Levels
  • Force New Page After Section
  • Dynamically Change Label Text
  • Image Stretch, Zoom, Clip
  • CanGrow, CanShrink Properties
  • Print a Single Invoice

Access 304 continues our advanced Access database development with VBA programming. We continue work on our sales and order-entry system. This lesson will focus on creating a printable report.

We don't need a separate table and form to create a quotation versus an invoice. They're both essentially the same thing - except one the customer is expected to pay for. So let's add a field to tell the two apart.

 

Next we'll add a toggle button to our order form so we can switch between quote and invoice, but with a twist. Let's use an AfterUpdate Event to change the caption of the button from "quote" to "invoice" and change the color when its value changes.

 

We'll also learn how to manually work with the numbered colors that Access has in the control properties.

 

Next you'll learn about the OnCurrent Event - an event that runs when you move from record to record, and how you can use it to control your own events.

 

Now the button color/caption will change when you move between orders too... not just when you click on the toggle button.

Next we're going to work on our printable invoice report. First, I'm going to make one big master query that has all of the information in it that I'm going to need: orders, order details, customer info, employee info (sales rep), etc.

 

This will make one big monster report with everything in it.

 

Now don't worry, we'll make sense of all of this madness. We're going to learn how to create Sorting & Grouping Levels to break up this information.

 

We'll create an Order Grouping Level to keep all of the "order" information together at the top of the invoice - then all of the line item details can go in the detail section.

 

Next we'll arrange all of the controls in our Detail section.

 

We want a new page after each invoice so that a customer doesn't get a piece of someone else's invoice... so we'll learn about the Force New Page After Section property. We'll also see how to adjust page margins for that "perfect" fit.

 

Next we'll add a label to the report so that it can say "Invoice" or "Quotation" depending on which type this order is. Of course, we'll add code to the Build Event of the section to change it accordingly.

 

Notice now when I generate a bunch of orders, the label will change depending on which type each is:

 

We'll add our logo to the top of the invoice.

 

Next we'll make a nice big PAID logo that will only show up if the Invoice is Paid. Oh, and I'll talk about why it's necessary to sometimes hide fields or checkboxes on your reports if you need their values.

 

We will then talk about the Can Grow and Can Shrink properties of both text boxes and sections. This allows your report items to take up as much (or as little) space as they need... and eliminates unnecessary whitespace.

 

Next we'll make a button to print an invoice. Unfortunately you can't print just a single invoice unless you know the special VBA code to open a report and show a single record.

 

Access 304 Outline
 

1. Quotation or Invoice
Is this a quote or an invoice?
IsQuote field
Add Browse All Orders button
AfterUpdate Event
Event when a field is updated
Change button color and caption
Access numbered color codes

2. On Current Event
Event when user moves between records
Add IsQuote to Order List
Filter By Selection
Add sum of orders to order list footer

3. Printable Invoice, Part 1
Create a Master Invoice Query
InvoiceQ
Sorting & Grouping Levels
Keep all Order information together
Group By OrderID

4. Printable Invoice, Part 2
Arrange controls in Detail section
Force New Page After Section property
Invoice Footer Totals

5. Printable Invoice, Part 3
File > Page Setup > Margins
Resize invoice layout
Quote or Invoice Label
Dynamically change label in Build Event
Create page header
Add company logo to top of invoice
Stretch, Zoom, and Clip modes for graphics

6. Printable Invoice, Part 4
IsPaid Label
Hiding fields on a report
Can Grow, Can Shrink Properties
Create a Print Button on the Order Form
Printing just a single invoice

 

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 304

Richard on 1/1/2007:  Creating a printable quote / invoice. Changing properties with VB code, OnCurrent, CanGrow/Shrink, OpenReport
leroy diaz on 3/11/2008: I was wondering you made a invoice for all the orders but what if you wanted to show all paid orders in an invoice and unpaid with the grand total in the footer how would you do that? i think you should put this in a lesson.
Richard Rost on 3/13/2008: That's coming up in the next class. In Access 305 we make the Accounts Receivable report. Now if you want to take that data and stick it in the footer of the customer's invoice, you could DLOOKUP (Access 302) the total unpaid orders and stick it in there, or use a subreport (something I haven't shown you yet because I don't like to use them - they're like subforms only very messy).
Walter Pohle on 5/26/2008: On invoice report is there
any way to Shrink and Grow
city,state & zip so that they are closer together
time stamp 5:27 on 304 vide
6

Richard Rost on 6/13/2008: Walter, I believe I cover this in a subsequent lesson. You just need to combine them together into one field using simple STRING CONCATENATION. I do it in a query, but you can do it right in the report:

CityStateZIP: City & ", " & State & " " & ZIP

 George on 11/29/2009: Using your tutorials as a guied which by the way are fantastic, I also included a "discount percent" field. Everything works just like I want it to work thanks to you. However, if I go back to look at an order say a month ago it shows me the info which includes the discount amount as well. If I change the discount amount to another amount it reflects on the older invoices as well. Is there a way so that when something changes say discount or tax amount that it doesn't interfere with something in the past? Would very much like to hear your thoughts on this
Thanks Rich

Len Jolly on 12/11/2009: Hi Richard
Ive just tried to open the invoice report for the first time and it will not open. I get the message "The specified field'OrderT.OrderID' could refer to more than one table listed in the FROM clause of your SQL statement" I cannot find where I have gone wrong, I need help please.
Regards
Len

Richard Rost on 12/13/2009: George, you will have to store that discount amount with each order. It's the same thing I do with discounts in my database in class - you have to store each discount (whether a percent or dollar amount) with each order. You can't just make it one blanket field for all orders, otherwise changing one will change them ALL.
Richard Rost on 12/14/2009: Len, usually this happens when you have the same field twice in your query or report. Do you have two tables that both have "OrderT.OrderID" in there? Not just OrderID... because we do want that in there from two different sources (OrderT and OrderDetailT). Check it again, and when in doubt, just start the report over from the beginning.
Cheri Parrag on 1/7/2010: lesson 304, part 6, 5:30, you use the can shrink and can grow properties on a field box. Can you also apply this same setting to the field heading so that if an item doesn't have a certain attribute, the field name AND value would disappear? I saw something about twips when I googled this question...? Thanks!
 George on 1/15/2010: Richard, One tiny problem, well, not a problem but something I've noticed and can't seem to correct. Let's say I put three items on the order using the order form, Item 1, Item2 and Item3 in that order. Why does the report show it reversed: Item3, Item2 and Item1? I've tried correcting with sort in the report but nothing seems to work
Richard Rost on 1/15/2010: George, that is strange. I can't see why a sort won't fix the problem. If you can't sort it in the report, try sorting in the underlying query.
PA on 10/12/2010: In this class you said that could be good to save a copy of the CompanyName in the OrderT in case the name change in the future, how can we do that? Are you going to show it in a future lesson?

Reply from Richard Rost:

It's the same concept that we covered back in Access 303 when I showed you how to store two copies of the address.

Kevin O'Malley on 5/27/2011: If you hold down the shift key while using the line tool it will come out straight.
PAUL TIBI TENDO on 1/7/2012: excellent courses.
Michelle Carmichael on 8/2/2013: Access304 5:00

Hi, Richard.

I'm confused about the reason for creating the ChangeIsQuoteToggle sub. Why is this better than having the code in the Form_Current sub? This seems to just add an extra unnecessary sub to the VB editor.


Reply from Richard Rost:

You need to have it in two places: the AfterUpdate happens when you click the button, the other when you move from record to record.

Ramona Woitas on 12/4/2013: I tried to add a notes field to my InvoiceDetailReport (subreport) in the sub form footer beside the ext price, sales tax, and invoice totals. But the notes field did not appear.

Then when I went to print the invoiceR, it showed the notes field that I tried to enter, on the report.

I have searched all queries, reports, forms, etc. and the notes field does not appear to be anywhere. I even tried to delete the note fields that pertained to my invoice.

How do I get rid of the notes fields that I cannot see.

Alex Hedley on 12/16/2013: There should be a list of controls on the Report in the Property Sheet.
Drop that down and look for Notes.
It will then highlight the control and you can delete it.

vicki Hudson on 10/29/2015: How do you make the boxes around fields not appear on the report?

Reply from Alex Hedley:

You can remove the Border on that field in the Formatting.

vicki Hudson on 10/29/2015: I think I got it. I went in desgin view, format tab, select all, properties tab, select Border Style change to Transparent. I couldn't find this solution anywhere. :-)
Love love love these classes

Emad on 2/14/2016: Hello, I have Access 2013, Toggle Button problem
Problem #1 The Blue color does not work with me but Red Color is working, so what do you think the problem is?
Problem#2 I put the toggle button in Form Footer, If I hit the button On in first recorded, it will remain always on on the other records too?! kindly, how to solve that?
My code
Private Sub IsQuoteToggle_AfterUpdate()
If IsQuoteToggle Then
IsQuoteToggle.Caption = "Quote"
IsQuoteToggle.ForeColor = vbBlue
Else
IsQuoteToggle.Caption = "Invoice" 'if you add . after the Filed name VBA will popup the Control Properise
IsQuoteToggle.ForeColor = vbRed
End If
End Sub

Reply from Alex Hedley:

If you debug.print IsQuoteToggle what is returned?

Dennis Owens on 7/17/2016: Access 304 - 1 Quote Invoice
Hi, I have Access 2016, I too am having Toggle Button problem Problem #1 The Red color (Quote)does not work with me but the Blue Color (Order)is working. Problem#2 If I select either of the two choices and save and exit, if I re-enter that order the button read "Is Quote?. It doesn't remember what I had selected. If I hit the button On in first recorded, it will remain always on on the other records too?! On in first recorded, it will remain always on on the other records too? However, I can see the difference between the two button selections. What do you think the problem is? What am I doing wrong?
My code

Private Sub IsQuoteToggle_AfterUpdate()
If IsQuoteToggle Then
IsQuoteToggle.Caption = "Quote"
IsQuoteToggle.ForeColor = vbBlue
Else
IsQuoteToggle.Caption = "Invoice"
IsQuoteToggle.ForeColor = vbRed
End If
End Sub

I am enjoying these lessons. Can't wait for the release of Advance ?

Reply from Alex Hedley:

It's been a long while since I've done this course,
IsQuoteToggle relates to the IsQuote Field
Does that value get updated in the table when you click on it and save the record?

Did you add the after update to the Form_Current() event?

Private Sub Form_Current()
IsQuoteToggle_AfterUpdate
End Sub


There was even a new sub created called ChangeIsQuoteToggle().

Dennis O on 7/22/2016: Thanks, I got it working.

Reply from Alex Hedley:

Could you share how in case others have the same problem.

Brauna Rosen on 7/26/2016: I have been asked to add a "past due" amount on an invoice. Can you suggest a way to bring in it? I thought of an after update event in the billing form using sql to select it but not really sure how.
Thanks

Reply from Alex Hedley:

Access 205

Dennis Owens on 7/27/2016: Access 304 Lesson 6 Invoice: I have made 5 IF Then statements with yes/no I tried to make another the same way as the rest. Spelling is correct. However, this time I get the following error message.
"Compile error:
Expected: Then or GoTo"
What am I doing wrong with this one?


Reply from Alex Hedley:

Can you share the code?

 

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