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

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.

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