Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
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.

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



The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/25/2024 12:39:42 PM. PLT: 0s