a specialized reportNow that we've covered how to print general reports for lists, statistics and summaries we'll take a look at a more specialized kind of report: the sales invoice.
Of course you could print an invoice by doing a screen capture of the invoice form as you create it but that's not always practical and it usually is not acceptable as a business document.
What you want to do is take the information that is on the form and produce hardcopy of it. Usually what you see on the screen as you create the form is not what is required on the printed version that the customer gets. And, of course, the actual format of a printed document is always different from the screen version.
building the invoice documentFirst, let's look at the form that was created to capture the sales transaction. The Invoice form as it was built in a previous lesson contains all the information needed to record the sales of parts and labor to a particular customer for work on his vehicle. The Invoice looks like this:
We'll build a rough version of the invoice report. It will be left to you to format it properly using the techniques we learned in previous lessons.
Since the query "InvoiceQuery" contains all the information needed for the heading of our invoice we'll use that as our starting point. But we'll have to put different constraints on it later so, we'll make a copy of the query and rename it: "InvoicePrintQuery".
Now, we simply Create - Report from this new query and then we roughly format the result so that it fits on the page (you have 8 inches to work with, remember).
Then you insert a SubReport control in the Details section, after the header information.
The SubReport Wizard will automatically activate and will ask you to specify the source of data and the links between objects, as shown in the diagram.
Your invoice will look a bit weird when you look at it in Design mode. With the Properties window open you can see all the properties that have been assigned to the SubReport. Take note of the link fields - if you have a problem later, there is a good chance that that is the source of the problem.
In Report View it looks a lot better and once you've done the formatting, inserted pictures and color and so on, it will begin to look like a business document.
If you've followed the instructions, you now have a working invoice to print.
But there's one thing that the previous picture doesn't show: when you run the report, you get a print of all the invoices in the system!
That's not very convenient. What you have to do is narrow it down to one invoice - the one that you're looking at in the Invoice form.
Here's how it goes:
- you have a customer in front of you and you pull his invoice up on the screen;
- you hit a button on the invoice form that says "Print" and an exact copy of that invoice pops up on the printer;
- you hand it to the customer and take his money.
So, we'll do that in the InvoicePrintQuery. We'll create a criteria that says:
select only the invoice where invoice_number in the query is equal to the invoice_number in the invoice form that is currently open on the screen.
That is the one condition: the invoice must be open in order to be allowed to print it. In other words, you will not be allowed to print an invoice directly from the tables.
To set it up in the query, use the Expression builder.
We're not quite done yet. There are no totals on the invoice.
To get the sub-total from the subform to the invoice we use the Expression builder again.
Note that the Invoice form is always open, still. Once the sub-total is transfered, the other fields are easily calculated.
Finally, one last step to complete the process.
Go back to the Invoice form and create a button to print the form that you see.
Use the Wizard to specify the action that the button will execute.
If you download the most recent version of MikeGarage from the Download area, you'll see that all these objects are now included.
However, it is left up to you, for your experience, to complete the invoice report to your needs.