|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Export Reports to PDF By Richard Rost 
6 years ago
Export Microsoft Access Reports to PDF with One Click VBA Pamela from Fort Worth TX (a Silver Member) asks, "My secretary needs to export my customer balance report each month to a PDF file so she can email it to corporate. She's not an Access Wiz. Is there any way this can be done automatically?" MembersI'll show you how to export individual customer reports to PDF with one customer per file, with unique filenames based on their CustomerID.
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! Links
IntroIn this video, I will show you how to export a report to PDF with a single click in Microsoft Access. You will learn how to quickly set up a customer report, add a button to your main menu form, and use a simple line of VBA code to automate the PDF export process, making it easy for anyone to generate reports for email or sharing.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I am going to show you how to export your reports to PDF with one click.Today's question comes from Pamela in Fort Worth, Texas, a silver member. Pamela asks, my secretary needs to be able to export my customer balance report each month to a PDF file so she can email it to corporate. He is not an Access whiz. Is there any way this can be done automatically? Well sure, Pamela, you can teach anybody how to export a report from Access with a couple of clicks. But if you want to do it with just one click, with a button on your main menu form for example, you have to learn one line of VB code and it is not hard at all. I am going to show you in just a minute. Here is my basic Access template that I use for teaching a lot of my classes and videos. I do not have any reports in here though, so let us throw together a real quick customer report. Here are my customers. I have a bunch of them here. Let us say the customer report has to go off once a month to corporate and the secretary wants to just be able to generate it with one button. Let us make the report first. Real quick: create, report design. I am going to come in here, open up the properties for the report, set the record source to CustomerT. I do not like using wizards. This is just as easy. CustomerT is where we are getting our records from. Add existing fields. Let us say this report just needs a CustomerID, FirstName, LastName, and I am holding down the control key, by the way, so I can pick multiple fields. Email and let us say their credit limit. I just want these fields. Click, drag, drop it over here. This is our real quick and dirty customer report. I am going to bring that bottom in the detail section up just a little bit like that so it all fits on the screen. A couple of quick options for you: I like to go into the detail section properties. Detail section properties, go to All. I like to say force new page after each section. That means each customer will be on their own page. I hate that alternating background color, so I am just going to turn both of these to white. I am going to click on the dot, dot, dot, and pick standard white. That is all F's like that. Copy and paste. I do not like that alternating background color unless I have got a real thin banded report. I am going to close that. Let us save this. Control S, save as MyCustomerR. Close that down. Now when I open up MyCustomerR, you can see there it is. Now this opens up in a preview. If you want to see what it is going to look like when it is exported to a PDF, go to Print Preview. There you go. That is what it will look like. You can scroll through the pages; each customer is on his or her own page. We want to send it to corporate. We could put this on the main menu to open the report very easily. There is a wizard for it. If you do not want to go through programming, here is the wizard. Drop the command button there. Report Operations. Send Report to File, hit Next. Which report do you want? MyCustomerR, hit Next. Text down there, we will put Customer Report. You can give it a different name if you want. Command13 is fine for now, and then hit Finish. Save this form. Close it and then open the main menu back up again. Click on our Customer Report. Now the OutputTo window pops up. It is going to ask you what format you want. There is PDF right there. Hit OK. It is going to ask where you want it to go. The default is my OneDrive Documents folder, and it will export it as MyCustomerR. Hit OK. That is it. You are done. It is like three steps, but even that sometimes is a lot for some people. Can we do it with just one click? Sure we can. Here is how. Let us get rid of this button here. Goodbye. See you. Let us just copy one of these other buttons or drag and drop a new button from the command bar up top and just do not run the wizard. I am going to slide this down here. Export Customer PDF. Right click, go to Build Event. You might be asked what kind of builder you want if you have never taken any of my classes before. Pick the Code Builder. Now I am inside of the Visual Basic Editor, inside Command14_Click. Here is your one line of code. Ready? DoCmd.OutputTo OutputTo space. What do you want to output? It is a report, so come down here and pick acOutputReport space. What is the object name? MyCustomerR. That is my report. It is right over here. We just made it. Output format. Now, they do not give you a list, which I do not like. Microsoft needs to do that. But what you need to type in is acFormatPDF. Then comma. Output file. Where do you want it to go? Well, I am just going to put it on my desktop. So figure out what your desktop folder is. Mine is C:\\Users\\Richard\\Desktop. That is where the file is going to go. You could put it anywhere you want to. You could put it in your database folder, you name it, your My Documents folder. Then give it a name. Call it Customers.pdf or whatever you want to call it. That is it. There is your one line of code. That is all you need. Save it. Close it. Let us close this form and reopen it just to be safe. Hit the button. You will see the little window flashes and then look on your desktop. I have got a Customers.pdf file right on my desktop. It is that simple. It is one line of code that you can make a button. You click on it and, boom, your PDF file is created. Now all she has to do is email and attach that form. Open that up and there is my PDF file. Nice and simple. Want to learn more? There is an extended cut version of this TechHelp video available for members, where I will show you how to export specific customer records into individually named PDFs. We will put an Export PDF button right on the customer record in the customer form. Click on that and it will create Customer1.pdf. Go to someone else you want to export, Customer7.pdf. So you can go through and export all the customers that you want to export their individual records. Then you can do whatever you want with them - email or print them or whatever. How do you become a member? Hit the Join button down below the video window. Silver members and up get access to my extended cut TechHelp videos, and there are lots of them right now. As of today, we have got, what do we have in there, 26 different videos. Lots of different stuff in there, and I am adding at least two or three new ones every week. Click on that Join button and you will see all the different levels that are available. Silver members and up get access to all the TechHelp videos. Do not worry, I am going to keep making these free TechHelp videos. As long as you keep watching them, I will keep making them. Make sure you click on the Like button to like this video and share it if you think anybody that you know will benefit from it. And of course, do not forget to subscribe. Subscribe to my channel, click on the little bell. You will get email notifications whenever I release a new video, and that is free, of course. Make sure you click the little Show More link down below the description. That will open up and give you some more links to different videos and other resources. If you have not yet tried my free Access Level 1 class, it is free. It is three hours long. Give it a try. If you like Level 1, Level 2 is just one dollar, and again, that is free for members. Want to see your question answered in a video just like this one? Hop over to my TechHelp page and you can submit your questions there. For everybody here at AccessLearningZone, my name is Richard Rost, and thank you very much. I hope you learned something, and we will see you next time. QuizQ1. What was Pamela's main request regarding exporting reports in Access?A. She wanted to know how to print reports directly from Access B. She wanted her secretary to export a customer balance report to PDF with one click C. She needed to import Excel files into Access D. She needed to create a chart in Access reports Q2. What is the traditional way to export a report to PDF in Access without programming? A. Right-clicking the report in the Navigation Pane and selecting Export B. Using a wizard to add a "Send Report to File" command button on a form C. Going to File, then Save As, then choosing PDF D. Dragging and dropping the report into a PDF folder Q3. Which of the following is the correct one line of VBA code to export an Access report to PDF? A. DoCmd.PrintOut B. DoCmd.ExportToPDF C. DoCmd.OutputTo acOutputReport, "MyCustomerR", acFormatPDF, "C:\\Users\\Richard\\Desktop\\Customers.pdf" D. DoCmd.RunReport "MyCustomerR", acFormatPDF Q4. When using the OutputTo method to export a report, what parameter specifies the output format for PDF files? A. acFormatTXT B. acFilePDF C. acFormatPDF D. acPDFOutput Q5. Where did Richard save the generated PDF in his example? A. In the root of drive C:\B. In the database folder C. On his desktop D. In his Documents folder Q6. What benefit does adding a VBA button with OutputTo provide over using the wizard-generated button? A. It allows exporting with one click and no further prompts B. It supports exporting reports in Excel format only C. It sends the report directly to a printer without viewing D. It auto-emails the report without user input Q7. What feature was adjusted in the report to ensure each customer starts on a new page? A. Set the Can Grow property to Yes B. Used group headers for each customer C. Set the Force New Page property in the detail section D. Changed the default magnification Q8. What alternative did Richard mention for exporting individual customer reports? A. Creating a query for each customer B. Using a macro for each record C. Placing an Export PDF button on the customer form to generate PDFs like Customer1.pdf, Customer7.pdf, etc. D. Sending all customer records in one large PDF Q9. According to the video, what is a recommended first step before creating a PDF export button with VBA? A. Delete all previous reports B. Create the report you want to export C. Export a table first D. Set up email integration in Access Q10. What does Richard suggest viewers do to access extended cut videos and more tutorials? A. Subscribe to the channel only B. Email him directly C. Become a Silver member or higher by joining via the Join button D. Watch ads on his videos Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-A; 7-C; 8-C; 9-B; 10-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. SummaryToday's video from Access Learning Zone focuses on how to export your Microsoft Access reports directly to PDF with just one click. I recently received a question from Pamela in Fort Worth, who asked whether her secretary could easily export a customer balance report to a PDF each month without needing to be highly skilled in Access.It's actually pretty easy to teach someone to export a report from Access using the standard menus. However, if you want to make this process even more efficient and allow the user to do it with a single button click on your main menu form, a small amount of VBA code will handle it for you. Let me walk you through the process. Starting with a basic Access template, I noticed there were no reports set up yet, so I created a simple customer report. This report includes the essential fields: CustomerID, FirstName, LastName, Email, and Credit Limit. I set the record source to the CustomerT table and arranged the report so each customer appears on their own page by adjusting the detail section properties and removing any distracting alternating background colors. Once the report was designed, I saved it as MyCustomerR. Opening the report in Print Preview confirms that each customer appears on their own page, which is exactly what we want for sending out to corporate. The next step is making the exporting process as simple as possible. If you want to use the built-in wizard, you can add a button to the main menu form. The wizard helps you set up a button that lets you choose the report, decide the format (PDF in this case), and select where to save the file. For some users though, even this two- or three-step process can feel complicated. To streamline things, you can remove that wizard-created button and instead add a new button without using the wizard. Then, in the button's event builder, switch over to the Visual Basic Editor. In the button's click event, you need to write just one line of VBA code using the DoCmd.OutputTo method. Specify the report you want to export, the format as PDF, and the destination path and filename—such as Customers.pdf on your desktop. Once that's in place, save your changes and close the form. The next time you open your main menu and click the new Export Customer PDF button, Access will create the PDF on your desktop instantly. This means the secretary only needs to click one button to generate the file and then attach it to an email—nothing more to worry about. For those interested in taking this further, I have an extended cut available for members. In this longer version, I walk through the process of exporting individual records as separate PDF files. I'll show you how to add an Export PDF button directly onto the customer form itself, so clicking it creates a uniquely named PDF for that particular customer, such as Customer1.pdf, Customer7.pdf, and so on. This is perfect when you need to generate documents for multiple customers individually. If you're interested in accessing extended cut videos like this one, you can sign up as a Silver member or higher. Members get access to a steadily growing library of in-depth TechHelp videos covering all kinds of useful Access techniques and solutions. Of course, I'll continue to release free videos on my channel as well, so everyone can learn something new. Remember to like and share the video if you found it helpful, and subscribe to my channel so you get notifications when new content is posted. You'll also find more helpful links and resources in the description on my website. If you have a question you'd like to see answered in a future video, visit my TechHelp page and submit your query there. 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 ListCreating a basic customer report in AccessSetting the report record source to a table Adding fields to a report using Add Existing Fields Adjusting the report layout and details section Configuring the report to force a new page per record Changing report background color properties Saving and naming a new report Previewing a report using Print Preview Using the command button wizard to export a report Exporting a report to PDF using built-in wizard Customizing the export button text Placing the exported PDF in a specific folder Manually adding a button without the wizard Opening the VBA editor for a button click event Writing DoCmd.OutputTo VBA code for PDF export Specifying output format and file location in VBA Testing the PDF export with a single button click |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access export access report to pdf convert access report to pdf PermaLink Export Reports to PDF in Microsoft Access |
