Embedding macros into buttons

Macros can activate all kinds of abilities in your database, and the most common way to trigger them is by pressing a button. Earlier we saw how we can use the Button Wizard in a form to choose from a predefined list of actions. Using the full Macro Designer we can get much more control over the functions that our buttons perform. Let's add some buttons to enhance the functionality of one of our Report objects. I am going to scroll down on my Navigation pane and find the Products Sold by Department Report; I am going to right-click on it and open it into Layout View. Now right now this report is showing all of our Products listed by Department and then within each Department we have the Category breakdowns.

Let's add some buttons here right on our report to be able to filter out to specific categories that we might be interested in reviewing. I am going to click up here on the title bar here where it says Products Sold and then I am going to go to the ARRANGE tab and choose to Insert a row below, this will be where we'll put our buttons. Now when we do that the layout grid adds a new row down below. I can click on it to see where it is. It highlights cells when you've selected them with this orange border, but when they're not selected it has a gray dashed border, it's really hard to see. so let me change the background color here so we can see it on the screen.

I'll click back here to select this gray bar in the back, I'll go up to the FORMAT tab, go to Shape Fill and I'll change it to white, now we can see the gray borders here. Now I need to create the areas where I want to place my buttons. I am going to click on this large cell here, go to the ARRANGE tab and I am going to split it horizontally, that will chop it into two pieces. Now I'm going to need to chop up these two pieces into two more pieces. So I'll select this first one and split that one and select this one over here and split that. Now I've got some areas for my buttons.

I have one here, here, here, here and here. Now I am going to go back to the DESIGN tab and in this Controls group I am going to grab a button object. I'll grab it here and then I'll go down to this first cell and I'll click to add a button. Now I want to double-click on it to edit its text. I'll double-click again to highlight it and this one is going to say All. This will be the button that will return us to an unfiltered state. If we need to we can resize the button by clicking on the bottom and dragging down just a little bit so we can see the whole thing. Okay, let's add in some more buttons.

We'll click another button object and click into the next cell, then I'll double-click and this one is going to filter to adjust our Textile category, I'll type in Textiles. All right, let's go ahead and deselect it and select the next cell. I'll add another button here, I'll double-click and this one is going to filter just our Home category. Okay we've got two more to do, I'll click on this one, I'll add a button there, double-click to edit the text and this one is going to be our Furniture category.

And finally I'll click in the next cell, choose a button and then add it here, double-click and this will be our Lighting category. So now I have all of my buttons. Now I need to tell these buttons what they're going to do when we click on them. To do that, I'll open up the Property Sheet. In the Property Sheet we have a tab for Events. The Events tab controls all of the ways that we can control various actions with our buttons. I am going to choose the Textiles button first and you'll notice the Property Sheet changed here, now it says Command60 which is the name of this button.

And in the On Click Event, this is what happens when you click on the button, we're going to create an embedded macro. Rather than create a standalone macro that takes up space over on an Navigation pane on the left, we can create a macro whose code is saved directly inside of this button object. I'll click on the Build button over here on the right. Access then needs to know how we want to construct the macro, I'll choose to use the Macro Builder. Our other options are the Expression Builder or Code Builder which will allow us to enter Visual Basic code. Go ahead and select Macro Builder and say OK.

that will take us into Macro editing session and I can see that I am inside of that Command button in the On Click event here, on the tab. Now the action that I want to choose is an ApplyFilter action. I'll scroll down the list and find it here. The ApplyFilter action will filter my data that's being fed into the report. I don't have a pre-made filter here so I need to enter in an SQL filter in this Where Condition. But we have this Build button way over here on the right, I'll click that and that will bring us the Expression Builder. You might remember the Expression Builder; we take a look at this in the chapter on queries.

So now I need to construct my filtering criteria. I can go into my database objects here, into my Tables here, into my Products table I'll double-click on Category to add that to the syntax and now we just need to say what I want the category to equal when I press that button. That was the textiles button so the ( Product)!(Category) is going to equal (=) and then in quotation mark "Textiles". Go ahead and say OK, that is our filtering criteria. I can go ahead and close the Action and save any changes and now that button is done, you can see the On Click Event has changed to the (Embedded Macro), let's do one for the Home.

I'll click here on the Home button, on the On Click event in the Property Sheet I'll click Build; I'll open up the Macro Builder and say OK. Once again, we're going to choose apply filter. This time I am just going to type App and it jumps right to ApplyFilter. I'll press Enter, for the where condition I'll go to my Builder, I'll dig into my database object here, into Tables, my Products Table and I'll double-click on the Category Expression Category here, = "Home", go ahead and say OK and we'll close the Macro.

Save the changes and there is those two. I am not going to do Furniture and Lighting, I think you can see the pattern here, but I do want to do the All one, that one that's slightly different. Here we're going to go to the On Click event, Build; Macro Builder once again, we'll say OK. We're going to use the same ApplyFilter, the Where Condition here and then once again click on the Build button on the far right. I'll go into my database, choose from the Tables and the Products table and we'll get the Category field.

I am going to say equals (=) here. And then what I want the Category to be equals to is True. I am not saying here that I want the Category to be the name True, but what I'm saying since I am not wrapping it in quotation marks, is that there is a category of some sort. If there is a category then I want to display it, this will show me all of my categories. Go ahead and say OK, I'll close the Macro Editor, Save the changes again and there is my first three buttons, let's go ahead and test them out. I'll switch my view into Report View by clicking the top half of the View button and I am going to click on the Textiles button.

And you can see that my categories have filtered to just the Textiles. I'll click on Home and you'll see now I am looking at just my Home categories within each department. I'll click on the All button to return to a unfiltered state. Now the nice thing about this report is that if you remember when we constructed it, is that we created some calculated statistics down here at the bottom. Right now what I'm looking at all products, I can see that I've got eight products in the Bath department here, there is the number 8. The total number of sold was the 612 and this is the total dollar value that they've contributed.

If I filter it by clicking on the Home button for instance, you can see now that these statistical lines update. Now I've got three categories in the Bath department, for a total number of sold of 69 and there is their value. If I switch to Textiles, you can see there they update again. So rather than clutter up our Navigation pane with lots of one-time use macro objects, we can often create embedded macros that are attached directly to the button that activates them. And using a macro to filter our data on the fly makes our Reports and Forms even more flexible.