Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Before we wrap up this course, I thought it will be helpful to outline a couple of the macros that you can build that will help you connect your forms to your reports. As we have seen in several places throughout Access, sometimes the wizards only present you with the most common options. To get access to all the available options, you will need to dig into the designers. The same applies to the button wizard. The actions available in the button wizard only give you a few basic options, to move beyond that, we need to construct macros ourselves. So let's add some of these reports that we have created in the last two chapters, to the Main Menu that we created earlier in the course.
I will right click on the Main Menu and switch into Design View. We will go up to that Button command and we will add a button to our form. I will click here to add it and I'm going to go to Report Operations. The first one I want to do is Mail Report. I will go ahead and say Next. The report I want to mail is that Orders By State, I will go ahead and click Next. And for this button I wanted some Text on here. I'm going to put Email Orders Report, go ahead and say Next, it will accept that name and say Finish. So there's one button. Let's go ahead and add one more.
I will click on the Button control and add that down to my form, the wizard will start and I am going to go to Report Operations again, and this time I am going to choose Send Report to File. Go ahead and say Next, the report I want to send is my Lifetime Sales report. I will click on Lifetime Sales and say Next. The text for this instead of just Report to File, we will say Export Lifetime Sales. Go ahead and say Next and Finish. So now I have got two buttons here, well let's take a look at what the wizard made for us. I will click on this first one the Email Orders one.
We will go to the Event tab here. The On Click event is this Embedded Macro and I will click the Build button to edit it. I will see that the wizard created this macro for us that that includes this EmailDatabaseObject action. It filled in a couple of these options for us, for instance, I have the option to include a report in the Orders By State report. But I also have some additional choices. I can specify an Output Format. And if I click on this, then the drop-down menu here I can choose a specific file format so that Access doesn't ask me every time I press the button, what type of file I would like.
I am going to choose PDF. I can specify a recipient so I could type in the To box here. And I am just going to send this to Test@test.com. I can specify carbon copy or blank carbon copy recipients, I can type in a subject here, Here is the Orders by State report. And I can add Message Text in to the body, The report is attached. This last one here, this Edit Message one is currently set to Yes. If I leave it set to yes and press that button, Access will take this report, it will convert it to a PDF file.
It will open up Outlook on My Computer, it will create a new e-mail, address it to this person, put in the Subject line and the Message Text, and attach that PDF file. And then it will sit there and wait for me to send the email. If I change Edit Message to No here, when I click on that Send Email button on my Main Menu, all of that will package up, get attached to the email, get addressed and it will be send off with no further interaction on my part. So I won't even see that email go out, it will just be gone. Let's go ahead and change that back to Yes. So I don't accidentally send any emails.
We will go ahead and close this and we will save the macro. So now that macro is providing more functionality than what the wizard gave for us. Let's take a look at the other one, this Export Lifetime Sales. The On Click event is this Embedded Macro again. We will look at the builder for that. This macro includes the ExportWithFormatting action, and again I have got a couple of options filled in. If I click here, I can select some additional options. Once again, I can specify a file format. Maybe I want to export this in a Excel file. I can also specify an Output Location, so if I want to specify an archived folder on a network or some place on my local computer where I always want to save it, I could type that in here, so Access won't even asks me for it when I click the button.
It will just put it right in that spot. I am going to leave it blank for now, and click close. I will save the changes, so again, now this button is doing something more useful than what the wizard left us off with. Let's go ahead and format these a little. I will click here and here, we will go to Format, I will change their Quick Style to maybe this black and we will go ahead and Arrange them. I will Align them to the Left side, I will take their spacing and Decrease Vertical and maybe a couple of times. That's actually going to take too long so I am going to press the button move it up here.
Now I will select both of them and maybe I will drag them down here. So now I have got a couple of additional buttons that I have added to my Main Menu. I can go take a look at it. I will press to switch to Form View and now if I press this Email Orders Report button, we will see what happens. The order gets packaged up and in just a moment, we shall see Outlook pop-up, there it is, my email is formatted, the file is attached and it's ready for me to press Send. So while using the Button Wizard, was able to get us started, digging into the full macro editor, was able to provide further functionality.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 73920 Viewers
80 Video lessons · 129433 Viewers
52 Video lessons · 63776 Viewers
59 Video lessons · 49544 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.