Microsoft Access 304
Advanced Access Development
Printable Invoice or Quotation,
AfterUpdate Event, OnCurrent Event, Sorting & Grouping Levels, Can
Grow/Shrink, More. 82 Minutes
- Printable Quote
- OnCurrent Event
- Sorting &
- Force New Page
Change Label Text
- Image Stretch,
- Print a Single
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
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
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
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
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.