Filter Report
By Richard Rost
3 years ago
Filter a Report Based on a Form in Microsoft Acces
In this Microsoft Access tutorial I'm going to teach you how to filter a report based on the same filter and sort that you put in a form.
Members
There is no extended cut, but here is the database file:
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!
Prerequisites
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, Filter report, sort report, filter report based on form, filteron, me.filter, me.filteron, orderby, me.orderby, me.orderbyon, ms access how to filter a report, How to Filter Reports Using a Form, Filtering a Report On-The-Fly, Microsoft Access Filter a Report, docmd.openreport, open report with form data
Subscribe to Filter Report
Get notifications when this page is updated
Intro In this video, I will show you how to filter a Microsoft Access report based on the current filter and sort settings from a related form. You will learn how to set up a form that can be filtered and sorted by different fields, and then use a bit of VBA code to transfer those filter and sort settings to a report for consistent printing or PDF creation. This technique lets you print reports showing exactly the records displayed on your form, with just a few lines of VBA.Transcript In today's video, I'm going to teach you how to filter a report based on whatever filter you set in a related form. This is something people ask me about all the time. You have a form, like a customer list or an order list or whatever, a continuous form. You want to be able to filter or sort that form and then have a report that you can print out that has exactly the same records that you have in the form. How do you do that? Well, that's what we're going to see how to do today in today's video.
This is a developer-level video, which means there's going to be some VBA - not much, like eight lines of code max. It's not that hard. It's just knowing where to put them. So if you never programmed in VBA before, go watch this video first. This will teach you all you need to know to get started with VBA. And if you don't know about filtering forms, go watch this video too. Teach you how to filter forms. Go watch all three of these. These are free. They're on my YouTube channel. They're on my website. Go watch them. Come on back.
Here we are in my TechHelp free template. This is a free database. You can download a copy from my website if you want a copy of it. Go get a copy of it.
Now in this database, I've got a customer list form with all my customers in it. Let's say I want to be able to filter these results. Let's say filter by state equals Florida, and then maybe sort it by first name.
Now I want to print this. Now, we don't print forms; we print reports. Remember, forms are for on the screen; reports are for printing or for making PDFs or for sending pretty emails. But how do we get this set of filters into a report? It's going to involve just a little bit of programming.
First, we need a report, though. So let's copy. I've got this blank R in here. It's just a blank report. You can start from a blank report if you want to, but I already have this guy set up with my parameters. So I'm going to copy and paste it. Call it customer list R.
Then we're literally just going to design this guy and steal the elements out of the form that I want to use. So we're going to move this over here. I'm going to delete that. We don't need a report header or a report footer. I'm going to design this guy. Come over here to the report's properties, set the record source equal to where you're getting your data from, which is customer T.
If filters and order bys and stuff come in here, just delete those. Delete that. Delete that. We want to make sure there's no filter and no order by set in the report itself.
Then take whatever fields you want out of here, like first name, last name, state, whatever you want. Just make the form however you want it. Now just copy those, paste them in the detail section. We can shrink that up now.
I want the labels on the top. So click, click, click. I'm holding the shift key down by the way. Copy. Click in the page header. Paste. Font color is white, so we're just going to format that to black so we can actually see it. Or if you want pretty colors, put pretty colors in there, I don't care. Oh, wrong one. Put the background color, foreground color.
Save it. Close it. Close it. Open it up. Now if you open up your customer list now and click print preview, you get everybody. That's fine.
Let's make a button from this form to open up the report. So right click, design view. We don't use the wizard. Copy, paste. Stick this button over here. This is going to be open report.
Right click, build event. I forgot a step. I always do this. Because I want you to remember to do this too. If you come in here and you see command17_Click, don't do that. I used to do that all the time. Alex used to yell at me - my right hand man, Alex. So we're going to give this button a good name. Go to all, not command17, because you look at it later in your code and you're like, what's command17? We're going to be open report button. Give it a good name. I used to be lazy like that too and I used to not bother, but it makes a difference, just making your code more readable.
What's this button going to do? DoCmd.OpenReport. What report is it? customer list R. This is important. acViewPreview. If you don't specify preview, it's going to be spitting out your printer, wasting paper. We don't like that.
Save that. Close it. Close it. Close it. Open it up. Open a report. There's a report. We're good.
Now, the report doesn't know what to do. You could set a filter in here if you want to. I filter by Florida, open report, it's still going to give you everybody.
So what we have to do: You can't very easily control the report from the form, because once the report opens, it kind of does its own thing. It's not like forms where you can open a form and then make some changes and just load some data and restart it. It's much easier to have the report do its own jazz. Let reports format themselves.
So what we're going to tell this report is, hey, when you open up before you start doing stuff, before you start writing stuff on the page, I want you to get the filter and sort information from this form.
That means the only way you should be able to open this report is from that button.
Here's what we're going to do. Go to design view. Now, we need to get into the code behind this report.
I have a button I put up on my toolbar up here to view the code, but you might not have that. So what you're going to do is open up the properties for the form or for the report, go to event, and go to On Open.
On Open is code that's going to run when the report opens.
What are we going to do in here? We're basically going to say my filter is going to be equal to its filter. So Me.Filter - that's the report's filter - equals Forms. What's the name of the form? It's customer list F.Filter. So set my filter property equal to that form's filter property. That's really all you have to do.
Now there's some other baggage that goes with it because you have to turn the filter on. By default, the filter is not on, or it may or may not be on depending on the design properties, so we're going to force that filter to turn on now: Me.FilterOn = True.
That's it. Save it. Close this. Close it. And open it up.
This report opened up and it said, hey, give me your filter property and stuck it right in there.
But we're not quite done yet, because if we close this, close that, open up the list again, now this guy's filter has been turned off. I opened up the report; it still has that filter in it.
So what we have to do is go back into the report, design view, go back into that code.
We're going to say Forms!CustomerListF.FilterOn is what we want to set that to. Because you don't always want to turn it on. We only want to turn it on if its filter is on.
Save it. Close it. Close it. Now open it and that guy's filter doesn't turn on. I want to show you the true first, so you see you've got it right. We're only going to set it equal to Filter On if this guy is on.
So now if I come in here and I filter by New York and open it up, his filter's on, this guy's filter turns on. And if I turn that filter off and run it again, I get everybody.
Now we can also do the same trick with Order By. In fact, watch this. I'm going to copy these, copy, paste. There's Me.Filter, and there's also Me.OrderBy, it's called. So Me.OrderBy equals the form's OrderBy, and Me.OrderByOn equals the form's OrderByOn. Do the filter, do the order by; same thing.
Save it. Close it. Close that. Close that. Close this. Close him. Close her. Close me. Merry Christmas. Merry Christmas. That's from people.
Let's sort by first name. Let's sort reverse order by first. There we go. Open the report. All the people, same sort.
Let's filter and show only people from Texas, the only one. Open it up. There you go, did that. Remove the filter and let's sort by last name and Florida again. Open it up. There you go, did that.
That's it. Four lines of code and six later, I was going to do it with an If Then statement, because you could do an If and check and see if the filter's on, but I think it's better to do it this way. Just open it up and set those properties.
I think I got the code button. There it goes. That's it. Four lines of code.
This is one of the popular questions people always ask me. I have a form, I want to set it up, set my parameters, set my filter, but now how can I get a report to match that? There you go. Filter on. Order by on.
See how cool this VBA stuff really is. Once you get in there, there's so much you can do with it. I love it. If you want to learn more cool stuff like this, I've got hours and hours of really awesome developer courses on my website where I teach you all the ins and outs and everything you possibly could need to know about programming in Visual Basic for Applications with Microsoft Access. So check it out.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.
Quiz Q1. What is the main goal of the technique demonstrated in the video?
A. To filter a report to match the filter and sort applied to a related form
B. To print reports without using filters or sorting
C. To apply conditional formatting to reports based on form values
D. To export forms as PDFs directly
Q2. In Access database development, why do we print reports instead of forms?
A. Reports are designed for printing, while forms are for on-screen interaction
B. Forms cannot be printed under any circumstances
C. Forms automatically update reports when printed
D. Reports can only show data from tables, not from forms
Q3. What is the recommended approach for applying the same filter from a form to a report?
A. Read the form's filter and set the report's filter property to match it using VBA
B. Set filters manually on the report each time
C. Use macros to synchronize filter settings between the form and the report
D. Create a duplicate table for filtered data
Q4. Where should you place the VBA code that synchronizes the filter and sort between the form and the report?
A. In the report's On Open event
B. In the form's On Load event
C. In the table's After Update event
D. In a standalone module
Q5. What VBA code is used to set the report's filter to match the form's filter?
A. Me.Filter = Forms!CustomerListF.Filter
B. Forms.Filter = Me.Filter
C. Me.Filter = Me.Filter
D. Me.Filter = Report.Filter
Q6. Which property must also be set to TRUE to apply a filter in an Access report?
A. Me.FilterOn
B. Me.ReportActive
C. Me.Visible
D. Me.FilterActive
Q7. What should you do to make your VBA code more readable when working with controls like buttons?
A. Give buttons meaningful names instead of default names like Command17
B. Use only default control names for consistency
C. Avoid naming buttons entirely
D. Only use numbers in button names
Q8. If you want to synchronize not just the filter but also the sorting from the form to the report, which properties must you set in VBA?
A. Me.OrderBy and Me.OrderByOn
B. Me.SortBy and Me.SortOn
C. Me.FilterSort and Me.SortFilterOn
D. Me.SortField and Me.SortType
Q9. Why is it important to check whether the form's filter or sort is actually on before setting those properties in the report?
A. To avoid applying empty or incorrect filters and sorts to the report
B. It is not important; the properties are always set
C. Reports do not support filtering
D. Forms automatically reset filters, so it does not matter
Q10. When should the user open a report if they want the filter and sort to match the related form?
A. Only by using the button provided on the form
B. By manually opening the report from the navigation pane
C. Automatically when the database opens
D. From the table design view
Q11. What happens if you open a report directly without passing the filter and sort from the form?
A. The report shows all records with no filter or sorting applied
B. The report will filter based on the previous session
C. The report shows a blank page
D. The report exports as a CSV file
Q12. What is the primary function of the On Open event in a report?
A. To run code when the report is first opened, such as applying filters and sorts
B. To display the page header
C. To delete records from the table
D. To close the report automatically
Q13. Which method is used in VBA to open a report and preview it on the screen?
A. DoCmd.OpenReport with acViewPreview
B. DoCmd.OpenForm with acViewPrint
C. DoCmd.SendObject with acFormatRTF
D. DoCmd.OutputTo with acOutputReport
Q14. When copying fields from a form to design a report, where are labels typically placed for a standard report layout?
A. In the page header section
B. In the detail section
C. In the report footer
D. In the group footer
Q15. Why is it particularly advisable to avoid default control names like Command17?
A. To make the code easier to read and maintain
B. Because they cannot be referenced in VBA
C. They make the database run slower
D. Access does not allow default names when using VBA
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
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 is all about filtering a report to match the filter and sort settings you have applied to a related form in Microsoft Access. This is a frequent request from users who want the ability to print out a report that contains only the same records they are currently viewing in a filtered or sorted continuous form, such as a list of customers or orders. I am going to walk you through the process of making the records in your report correspond exactly to the ones shown in your form, based on whatever kind of filtering and sorting you have set up.
Before we get started, I want to point out that this lesson is at the developer level and will require a minimal amount of VBA programming – just a few short lines of code. If you have never worked with VBA before, I recommend reviewing some of my introductory tutorials on VBA and on form filtering, which you can find for free on my YouTube channel and website. Once you feel comfortable with those basics, come back to this lesson.
In my free TechHelp template database, which you can also download from my site, I have a customer list form that displays all customer records. Suppose you want to filter this list, perhaps showing only customers in Florida and sorting them by first name. If you want to print this filtered, sorted list, remember that forms are intended for on-screen use only. Reports are designed for printing, creating PDFs, or sending emails with nicely formatted data. So the challenge is to get your report to reflect the filter (and sort) settings you have applied to your form.
First, you need to create a report. You can use a blank report or copy an existing one if you already have a template set up. Set the report's record source to your data table – in this case, CustomerT. Make sure to remove any filters or sort orders that might be set in the report's properties, since you want the report to take on its filter and sort dynamically.
Next, design the report by adding the fields you want to display, such as first name, last name, and state. Copy these controls to the detail section. If you want labels at the top, you can copy them into the page header and adjust the formatting so they are readable and look the way you want.
Once the report is set up, save and close it. Now, when you open this report from the Navigation Pane, you will see all records, which is expected because there is no filter applied yet.
To open the report directly from your form and apply the same filter and sort order, you need to add a button to your form. Place the button where you like, give it a clear and meaningful name like btnOpenReport, and set up its On Click event to open your report in Preview mode. Using Preview is important so you do not accidentally print out every customer and waste paper.
At this point, clicking the button will open your report, but it will still display all records. You may have filtered your form by state or sorted it, but the report does not yet know about these settings.
Here's the key step: You need your report to read the filter and sort from the form when it opens. The best place to do this is in the report's On Open event in VBA. When the report opens, you want to set the report's filter property to match the form's filter property, and also turn filtering on. This ensures the report applies exactly the same constraints as the form.
Because it is possible that no filter is active on the form, it is a good idea only to set the report's FilterOn property to True if the form's FilterOn property is also True. The same principle applies for the OrderBy property – set the report's OrderBy and OrderByOn properties based on the form's settings. This way, whatever sort order or filter you have set in the form is automatically reflected in the report each time you open it using the button.
Now, when you filter by a particular state or sort by a specific field in your form and then click your button, the report that opens will show just those records in exactly the same order and with the same filtering as what you had on screen.
This technique only requires a few lines of VBA code but adds a significant amount of flexibility and user-friendliness to your Access applications. It is a much-asked-about feature by developers and users alike.
If you're interested in learning more about VBA programming for Access, I cover many advanced techniques and best practices in my developer-level courses, available on my website.
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 Creating a report that matches a filtered form in Access
Copying and designing a report based on an existing template
Setting the report's record source to a table
Removing default filters and sorting from a report
Copying fields and labels from a form to a report
Adding a button to a form to open a report
Writing VBA code to open a report in Print Preview mode
Setting up the VBA On Open event of a report
Using VBA to apply a form's filter to a report
Using VBA to apply a form's Order By to a report
Synchronizing filter and sorting between form and report
Ensuring filters and sorting only apply when active on the form
Copying and using VBA code for Order By properties
Testing report output with different filters and sorting |
