Access 2007-2019
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  
 
 
 
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 11/24/2019 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

 
 

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
Change Email
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