Print Multiple Invoices
By Richard Rost
3 years ago
Select Multiple Invoices to Print at the Same Time
In this Microsoft Access tutorial I am going to teach you how to select multiple invoices to print at one time. This will allow you to batch print invoices or select all of a particular customer's invoices to be printed.
Benjamin from Longview, Texas (a Platinum Member) asks: I've been using your Invoicing database for quite some time now and it's very helpful. Thank you. Is there any way that I can print multiple invoices at one time. We enter invoices throughout the week and I like to mail them out every Friday. It would be nice to just hit the print button and it sends them all to the printer instead of having to print them individually.
Members
Members will learn how to have the report ask if you want to mark all of the invoices as printed when you close it, display a friendly message if there are no invoices marked to be printed, quick print a single invoice, and will learn how to display Page X of Y across multiple groups.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, mark to be printed, force new page, reset page number for each group, reset number of pages for groups, group page numbering, set page number by group, mark all as printed, on no data event, nodata, report close event, page number reset for group, page x of y over a group in a report
Intro In this video, you will learn how to print multiple invoices at once using Microsoft Access. I will show you how to update your invoicing database to select and batch print invoices, including adding a field to mark invoices for printing, updating the invoice report with grouping by order, and resetting the page number for each invoice. We will also cover how to use an update query and create a button to mark invoices as printed after they are processed. This tutorial is aimed at users with a good understanding of Access fundamentals.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, I'm going to teach you how to select multiple invoices to print at one time. This will allow you to batch print invoices or select all of a particular customer's invoices to be printed and stuff like that.
Benjamin from Longview, Texas, one of my Platinum members, asks: "I've been using your invoicing database for quite some time now and it's very helpful. Thank you." You're welcome. "Is there any way that I can print multiple invoices at one time? We enter invoices throughout the week and I'd like to be able to mail them out every Friday. It would be nice to just hit the print button and it sends them all to the printer instead of just having to print them individually."
Yes, Benjamin, we have to make a couple of changes to the invoice report in the TechHelp database, but it's definitely doable. Let me show you how.
This is going to be an expert level video. There is no programming involved, but it's a little bit beyond the basics.
What do you have to know to get started? First, if you have not yet watched my invoicing video, go watch this. It's where I show you how to build the order entry system and the invoice report. That's what this video is based on. If you haven't watched this yet, go watch it right now.
Go watch my grouping video. This is how you turn on grouping levels in your Microsoft Access Reports. We're going to have to group them together in order to print them out in batches. This is very important. Go watch this video too.
If you don't know how to make an update query, go watch this video next. Essentially, when we're all done printing our invoices, we're going to click a button. It's going to say, "All right, mark all of the invoices that we just printed as having been printed." We'll use an update query to do that.
These are all free videos. They're on my website. They're on my YouTube channel. You'll see links down below. You can click on them. They'll be down in the description under the video. So, go look for them there.
Go watch those videos and come on back.
Here I am in my TechHelp free template. This is a free database you can download off my website if you want a copy. I've made a couple of minor modifications to it since I recorded the invoicing video, but nothing major. It's still the same thing.
Here's your customer. Here's your orders. If you want to print this invoice, you click the Invoice button. There it is. It goes to Print Preview. Close it. And then that's pretty much everything.
Now, in order to batch print these, we're going to have to change some things around. Right now, what happens is when you make an invoice, there's a query, the OrderInvoiceQ, that basically looks at the OrderID for the currently open order.
If you go into the OrderInvoiceQ, which is what the report is based on, remember this from the video, if you scroll all the way over here, here's where it gets its criteria from. The OrderID of the current order on Forms!OrderF!OrderID, which is this guy.
So when this query runs, it brings up just that OrderID. In this case, one. But what we need to do is change that criteria to allow the user to pick any number of invoices that he wants to generate.
So we'll add a Yes/No field to the order table. Go to OrderT. Right click, Design View, right down here. We'll call it ToBePrinted. We'll make that a Yes/No value. And I'm going to default that to Yes. So, when we generate new orders, we put new invoices in the system. By default, they'll be marked to be printed until we actually run a print job. Then when we're done, we're going to click a button that's going to mark them all as having been printed. We'll get to that later.
Save that. Let's go to the Datasheet View and just mark a few of these to be printed. This one, that one, this one, just a couple random ones. Okay. Now close it.
Come back in the form, go to the order form, and we're going to add a check box right there. Same thing. In fact, I'm going to borrow this guy, copy, paste, slide it up here. We'll just call this... We could just put down the label here, "To Print" or whatever. You don't have to put the whole thing. All right, that's "To Print."
We have to change its properties. Double click on it. Go to "All," change this from IsPaid to ToBePrinted. Copy and paste over the name too.
Now we've got it on the form as well. If you open up the order form, you'll see that one is to be printed and that one is not to be printed, for example.
Now we're going to go to that OrderInvoiceQ and we're going to change this so that, instead of looking at that OrderID, it's going to look at the ToBePrinted field. So I'm going to get rid of this, delete that column, and bring in ToBePrinted. Make the criteria True. There's already a ToBePrinted in this query because I brought in the OrderT.*, so let's not show it. That hides duplicates. I talked about that in the other video, too.
If you run this now, you'll see that we have all of the orders that are marked to be printed. See this column here? ToBePrinted. That should all be True. And, yeah, if you want it also to be paid... If it's paid or not, that's up to you. You can print all the ones that are paid, or not paid, or whatever. You can add more criteria if you want. I'm just going to print them all.
Save this query now and close it.
Now if I go to print an invoice now, look what I get. I get everything all grouped together in one invoice. All of the items for all of those invoices are together in the same report.
Let me... besides this, let's see. Look at that. There's two whole pages on it. $43,000 of the stuff. Why is that? Well, because the way that this order report is designed... Let me shut this down for just a second here.
The way it's designed is everything is grouped together. All of the details are coming in from this entire query. So what we have to do is we have to add a grouping level and group this by what field? What do you think? Each one should be a unique order. So all of order 1 shows up together, then all of order 2's items show up together, and then all of order 5's items, and so on. So we have to add a grouping level to group this based on whatever field you want to group on. In this case, OrderID.
Let's go back and turn that grouping thing on. I also go to Report Design, Group and Sort. We want down here, we're going to add a group and I cover this in the grouping video, right? Add a group. We're going to group on OrderID. Hit the "More" button over here.
We do want a header section and we also want a footer section. Change that to "With a Footer Section." So now we have a group header. There's the OrderID header and we have a group footer. There's the OrderID footer.
So what we're going to do now is we're going to take everything out of the report header and this page header, and we're going to stick that in the order header. We want this to repeat at the top of each order. It'll have the customer information and the order information and this on top of each page. Then the Detail section will stay as it is, and then the report footer stuff is going to go in the group footer up here.
It's a little confusing because, yes, the group footer is actually going to be above the page footer in Design View. But when the report generates, it flips it. It's a little counterintuitive, but it just takes a little getting used to.
So first thing is we can actually close this now. Don't hit that, that will delete the group. I hate the way that this is, that they look like each other. I'm just going to close this to close that group pane. It takes up a lot of space.
First thing is we're going to cut all of this stuff out up here. Put your mouse over here on the ruler, click and drag. I'm going to grab all of those controls. Cut them out, Ctrl+X. All right, we can shrink up the report header now and then make the OrderID header bigger. Click in here, you'll see this is highlighted now, and then paste.
Maybe slide those over just a little bit like they were. Make that a little taller now.
We're going to take this page header and we're going to cut this out of here, Ctrl+X. Shrink that up. We're going to paste it down here. It's going to come in up top like that. That's okay. Click and drag and slide them down. This is why in my full courses, I take a lot of time going over how to move these controls around and cutting and pasting them. There's some repetition, but you really get the feel for how this stuff works. A lot of it is just practice. You've got to play with it.
This is going to repeat at the top of each order. Down here now, the stuff that's in the report footer, our total and our notes, we're going to cut that out too. Select all that, cut it, shrink that section up, come into the OrderID footer, and paste it in there. Then slide it over like that.
In fact, if you want to at this point, you can even turn off the report header, the page header, and the report footer.
Let's save it, close it. Now, let's take a look at what happens when we print one of them now.
It's looking better. What do we have here? We've got one invoice here, that's order 1. Then right below it, we've got order 2, it's a different invoice. Let's go to the next page. There's the next order, 5. It's looking better.
The next thing I want to do is I want to force a new page after each order. So I'm going to come back into Design View. After each OrderID footer, I want to force a new page.
Open up the properties here. Double click. Find the Force New Page. Make sure you're in the section, the GroupFooter1 section, and change that to After Section. That says, after this section draws, force a new page.
Save it. Close it. Open it back up again. There we go. Even better now. There's one invoice. If you go to the next page, there's the next invoice, and the next invoice, and the next invoice.
You can probably guess what my next change is - these alternating colors. We didn't need to worry about it before, but we've got to turn those off. So in here, in this section, because they're new sections, open them up. Alternating back color is going to be set to None for the header and for the footer.
Close it. Save it. Ready? Here we go. Looks good. Looks good. Looks good. Now we've got a bunch of different invoices in here. We've got all the ones that are marked to be printed.
Now, a couple little things. First, on the bottom, page number, it still says Page 1 of 7. If you're going to print these out for each of your customers, you don't want your customer getting Page 4 of 7. Your options basically are, you can delete that off of there or you can reset that page number. But resetting that page number involves one line of VB code. I promised there wouldn't be any programming, any coding in this video, so do you want me to show it to you or not?
It's bonus time. It's bonus code time. If you don't want to have to deal with any programming, just skip this part. But if you're curious and you want to see how this works, go watch my Intro to VBA class first. It's 20 minutes long, it's free, and it'll teach you all the basics, everything you need to know to get started with VBA. It's literally one line of code to get this to work.
But if you don't want to deal with this, all you have to do is just delete that field. You won't get a page number on your thing either, you don't deal with it.
Here's the one line of code that you need. The first thing you're going to have to do is you're going to have to come in here and get rid of the "of" part of it. This will, unfortunately, only work with just the page number. The total number of pages - so it says Page 1 of 2, 1 of 3, and so on - that is possible, but that's a lot more in-depth. It requires a lot more code, and I will show that in the extended cut for the members. For everybody else, I'm going to show you just how to put the single page number there.
Up in the page - excuse me - in the OrderID header, we basically have to say, "Hey, reset the page number to one every time this thing is drawn." So right click here, go to "Build Event." That'll bring up your VB editor.
Literally, all you have to say in here is:
Page = 1
That's it. You're done. Page is a variable representing the page number and you can change anything you want to.
Now what happens is, close this, save it, open it back up again, and now every time that group header runs, it's resetting the page number. If I go to the next page, it still says Page 1, Page 1, Page 1. Now, if you do have a really long invoice... Let's take this guy's invoice here. Copy, I'm just going to make it longer, paste, paste, go to page 2 here, paste, go back into here.
It's still on page 1 here. Let's make it even longer. Copy, make sure it's got two pages worth of stuff, and paste. Invoice. It's nice and long now. Let's go to page 2 and you can see this says Page 2 now, and there's your total. Now, when I go to the next one, it resets to Page 1 because the order header was drawn again. That's what that build event does. So when the GroupID header runs, it resets the page number to one.
Keeping track of that page total number of pages is a lot more involved. Again, extended cut.
So now we've got our invoice. We can come in here, click Invoice, and it'll generate all of our invoices. There they are. Looks good.
Now, when we close this, we need a way to mark them all as having been printed, because I don't want to have to come back in here in the order table and go click, click, click, click. So, we can use an update query to do that. That's why I wanted you to go watch the update query video.
So, let's create Query Design. Bring in your order table. We're going to bring in the ToBePrinted field. We don't need to bother with the criteria. Just change this to Update Query. Update To is going to be False. This will just take everybody in the database and reset them to False. If you want to put criteria True in there, that's fine. It might speed up the query a teeny tiny bit if you have tens of thousands of records. Generally, it won't.
We can save this update query as OrderMarkedPrintedQ or UpdateQ or whatever you want to call it.
Now, you can either run this manually if you want to, or you can make yourself a button to do it. You can make the button without code if you want to. Form Design, grab a button, drop it on the form. It's going to be Miscellaneous, Run Query, then pick your query, this guy.
I'm going to cancel this, though, because I don't like using the wizard. I don't like the embedded macros. Just finish running through that, or you can use one line of VB code.
Mark Invoices Printed. There's the caption on our button. I know I said there would be no code. I can't help it. Once you learn a little tiny bit of programming, it makes things so much better. Make your button, give it a good name: MarkInvoicesPrintedButton, whatever you want to call it. Right click, Build Event. Right in here, do:
DoCmd.OpenQuery "OrderMarkedPrintedQ"
That'll run that query. You can even say:
MsgBox "Done"
Save it. Close it. Yes, for those of you who know SQL, you can do the SQL right inside the code if you want to without even making a query.
Now, in your main menu, Mark Printed, done. Now, go into your order table and they're all marked Printed.
That's it. Now you can, if you want to, you can make yourself an order list. Like, I've got the Customer List here. You can make a list of all of your orders. I have that in the member database. But you can go through now and, if you want to print a bunch of orders, like for this customer, mark that one to be printed, go in here, mark that one to be printed, close that. Go into, let's see, Jordy's invoice if he's got one, yeah, he's got one. Mark that to be printed.
Now, when you're ready to print, just go into any one of them. You can actually make a "Print All These Orders" button right on your main menu if you want to print all pending invoices, it's the same thing, just open this report. DoCmd.OpenReport and it will have all of these right in here. There's one, two, and three.
When you're all done, close them up. Mark Printed, and you're done.
If you want to learn more about this stuff, I cover building a printable invoice report in a lot more detail in my Access Expert Level 9 class. I actually show you another method too, which involves subreports, which is another way to do it. Lots of different ways to do this. My full database in my full course covers lots more stuff too, like sales tax, discounts, all that kind of stuff. We go into a lot more detail in the full course, of course.
Now, if you want to join the 21st century and you're done mailing invoices, and you want to send them by email, I have a free TechHelp video that shows you how to send emails. I show them one at a time in the free version, and in the extended cut for members I show you how to send mass email using Microsoft Outlook.
You can do the same thing. You can use the techniques that I showed you in this class and send a batch of them out instead of just one at a time. I also have a full Email Seminar that shows you pretty much everything you need to know about sending any kind of email, including building your own email server in Access in the Email Seminar. So check that out too.
What are we going to talk about in the extended cut? First off, instead of having to make a separate button to mark everything printed, we're going to have it so that when you close the report, it says, "Oh, you're done. Would you like to mark all the invoices as printed?" Then, if there are no invoices to be printed and you try to print them, it'll give you a friendly message. Right now, if you try to print and there is nothing marked to be printed, it will give you an error message, which I didn't cover, but trust me, it will.
We'll see how to quick print a single invoice. Then, the big thing, I'll show you how to do Page X of Y across multiple groups. In other words, if you've got two or three-page invoices in there, I'll show you how to make it say "Page 1 of 2" inside the groups inside your reports. It requires a bit of code, but we'll go over it in the extended cut for members.
Silver members and up get access to all of my extended cut videos, and Gold members get access to the code vault and can download these databases, so check it out.
This has been your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends.
I'll see you next time.Quiz Q1. What is the main objective of the video tutorial?
A. To teach how to add sales tax to invoices
B. To teach how to batch print multiple invoices at once
C. To create individual customer reports
D. To email invoices to customers
Q2. What new field is added to the Orders table to help with batch printing invoices?
A. DatePrinted
B. PrintStatus
C. ToBePrinted
D. ReadyToPrint
Q3. What type of field is the ToBePrinted field in the Orders table?
A. Text field
B. Date/Time field
C. Yes/No (Boolean) field
D. Number field
Q4. By default, what value is assigned to the ToBePrinted field when a new order is created?
A. False
B. Blank
C. No value
D. True
Q5. What must be changed in the OrderInvoiceQ query to select multiple invoices for printing?
A. Add a parameter for CustomerID
B. Set criteria to look at the ToBePrinted field instead of OrderID
C. Set criteria to all records
D. Add totals to the report
Q6. What is the purpose of adding a grouping level in the invoice report?
A. To sort the invoices by customer name
B. To calculate totals for each order
C. To group invoice items by each OrderID, making each invoice separate in the batch
D. To change the font style for invoice headers
Q7. Where should the customer and order information controls be moved in the report layout for batch printing?
A. Report Header
B. Detail section
C. Group Header (OrderID header)
D. Page Footer
Q8. What property is used to start a new page after each order is printed in the report?
A. NewPage property in the detail section
B. Force New Page set to After Section in the OrderID footer
C. Insert Page Break control in the report header
D. Page numbering in the page footer
Q9. Why is it important to turn off the alternating back color in the invoice header and footer for batch printing?
A. Because it makes the report print faster
B. To keep the appearance consistent for each invoice section
C. To highlight unpaid invoices
D. To prevent background images from printing
Q10. If a customer receives a batch of invoices, why might you want to reset the page number for each order?
A. So each customer's invoice starts at Page 1
B. To keep printing costs down
C. So invoices are sorted more easily
D. For easier updating of the database
Q11. What VBA code is used to reset the page number to 1 for each invoice group?
A. PageNumber = 1
B. Page.Value = 1
C. Page = 1
D. ResetPage = True
Q12. What is the purpose of using an update query after printing invoices?
A. To delete old invoices from the database
B. To mark printed invoices' ToBePrinted fields as False
C. To calculate invoice totals
D. To update customer addresses
Q13. After running the update query to mark invoices as printed, what value is set for the ToBePrinted field of those invoices?
A. True
B. Null
C. False
D. Blank
Q14. How can you automate marking invoices as printed after a batch print job?
A. Manually check each checkbox in the Orders table
B. Use an update query, optionally triggered by a button or VBA code
C. Email the invoices and mark as printed when sent
D. Delete records from the Orders table
Q15. What is one method discussed to avoid using programming to mark invoices as printed?
A. Remove the ToBePrinted field
B. Add a macro to the customer form
C. Use a button to run an update query without code
D. Use an embedded image in the report footer
Q16. According to the video, what should you do if you want to create a button to print all pending invoices?
A. Create a macro that loops through each order
B. Open the report based on the ToBePrinted=True criteria
C. Send each invoice to a different printer
D. Email all invoices in the database
Q17. What happens if you try to print invoices when none are marked to be printed, according to the video?
A. Only paid invoices print
B. A friendly error message is shown (in the improved version)
C. Nothing happens or an error occurs
D. The system crashes
Q18. According to the video, what additional feature is available for members in the extended cut?
A. Automatically emailing invoices after printing
B. Tracking inventory quantities for each invoice
C. Calculating Page X of Y for each invoice group
D. Importing invoices from Excel
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-C; 7-C; 8-B; 9-B; 10-A; 11-C; 12-B; 13-C; 14-B; 15-C; 16-B; 17-C; 18-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will cover how to select and print multiple invoices at once in Microsoft Access. If you have been creating invoices throughout the week and want an easier way to batch print them on a specific day rather than handling them individually, this guide will walk you through the necessary modifications.
Many users create invoices throughout the week and find it tedious to print them one at a time. Instead, it is much easier to print multiple invoices together, either for a single customer or for everyone at once. To accomplish this, we need to make certain changes to the typical order and invoice setup in Access.
This tutorial is aimed at users who are comfortable working at an expert level with Access, although there is no programming involved in most of the process. Before getting started, it is important to have a good familiarity with building the original order entry system and invoice report. If you're not sure how to do that, make sure to review the video on building the invoicing database. Understanding how to enable grouping in Access reports is also crucial as we will need to group orders to facilitate batch printing. Additionally, knowing how to use update queries will help when marking invoices as printed after processing them.
All of those foundational lessons are available for free on the website and YouTube channel, so once you have reviewed them and are comfortable, move on to the steps below.
Begin with your database. If you are using my free template, it should look familiar. The current process prints a single invoice based on the OrderID of the order currently selected in the form. This is set up in the OrderInvoiceQ query, which restricts the report to one order at a time using the OrderID field.
In order to print multiple invoices, we need to update the criteria so that users can select any number of invoices to print. To achieve this, start by adding a Yes/No field named ToBePrinted to your order table. Set its default value to Yes so that all new orders are marked for printing by default. After printing, you can update this to No, indicating the invoice no longer needs printing.
Switch to datasheet view and select a few orders to be printed by checking the new field. Return to your order form and add a corresponding checkbox for ToBePrinted, so you can easily mark invoices for printing directly from the form. Make sure the checkbox control uses the correct field and label it as you wish, perhaps simply as "To Print."
After setting up the field and the form control, modify your OrderInvoiceQ query to use the ToBePrinted field as its criteria. Remove the old OrderID criteria and set the query to return only those orders where ToBePrinted is True. If you want, you can also add other conditions, such as whether an invoice is paid. Save your changes to the query.
Now, if you print the invoice report, you will see every invoice that is marked to be printed. However, you will notice all the items are grouped together in a single list. To resolve this and have each order printed separately, add a grouping level to your report, grouping by OrderID. Enable both a group header and a group footer for OrderID. Move the existing controls from the report header, page header, and report footer into the appropriate group header and footer. This step is important so that each invoice appears separately within the batch.
With the grouping in place, test your report. Each order now generates its own section within the report. To ensure each invoice starts on a new page, set the Force New Page property to "After Section" for the group footer. This will insert a page break after each order, preventing overlap and making it easier to separate and mail invoices.
Another aesthetic issue is the alternating background colors that appear due to the new sections. Disable the alternating back color for the group headers and footers to produce a cleaner printout.
With everything in place, you now have a batch-printable invoice report based on all orders marked for printing. There's one more detail to address: the page number footer. By default, the report may show page numbers like "Page 4 of 7," which is not ideal for customer-facing invoices. You can remove the page count altogether or reset the page number for each invoice. Resetting the page number does require a single line of VBA code placed in the group header's event. If you are not comfortable with code, simply omit the page count. If you're interested in learning how to add code, I recommend watching the free Intro to VBA video on the site.
Once your invoices have been printed, you want to mark them as printed to avoid repeating the process for the same orders. Create an update query that sets ToBePrinted to False for all relevant orders. You can run this query manually or automate it with a button on your form. Even though this can be done without code, using a single line of VBA code to run the query can make the process even more efficient. Provide clickable feedback for the user, such as a message box that says "Done" once the operation is complete.
From this point, you can further streamline your workflow by adding functionalities such as an order list to manage the statuses of your invoices, or a button to print all pending invoices directly from the main menu. If you want to send invoices by email instead of printing them, there are free videos available that demonstrate this, with more advanced techniques for mass emailing shown in member-only extended cuts. The Email Seminar is also available for those interested in comprehensive coverage of email automation from Access.
In the extended cut for members, I show how to mark invoices as printed automatically when the report is closed, add friendly messages if there are no invoices to print, demonstrate quick printing a single invoice, and explain in detail how to handle page numbers that display as "Page X of Y" within each invoice. This involves a more sophisticated coding approach that I cover step by step.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Adding a Yes/No ToBePrinted field to the orders table
Defaulting the ToBePrinted field to Yes for new invoices
Adding a ToBePrinted checkbox to the order form
Binding the checkbox to the ToBePrinted field
Modifying the invoice query to select records with ToBePrinted=True
Removing OrderID criteria from the invoice report query
Adding grouping by OrderID to the report
Moving report and page header controls to the OrderID group header
Moving report footer controls to the OrderID group footer
Forcing a new page after each OrderID group in the report
Turning off alternating background colors in report sections
Resetting page numbers per invoice using VBA code
Creating an update query to mark invoices as printed
Adding a button to run the update query and mark invoices as printed
Linking the button to the update query using VBA
Using MsgBox to show confirmation after marking printed
Demonstrating the workflow for batch selecting invoices
Printing all selected invoices as a single batch |
