Filter Subform
By Richard Rost
2 months ago
Filter a Subform Using a Combo Box in Microsoft Access In this video, I will show you how to set up a form in Microsoft Access where you can filter a subform displaying contact history simply by selecting a customer from a combo box. We will walk through building an unbound parent form with a combo box, embedding a contact subform, and using both non-VBA and simple VBA methods to filter the displayed contacts based on the selected customer. We also discuss some limitations of each method and how you can achieve more flexibility with a small amount of VBA. Wendy from Springfield, Massachusetts (a Platinum Member) asks: I've been using your TechHelp Free Template and I really like the contact management part of it, but I'm wondering if there's a quicker way to view a customer's contacts. Right now I have to open the customer form and then go into the contacts from there. Is there a way to build a form where I can just pick a customer from a combo box and immediately see all of that customer's contacts so I can quickly switch between customers? MembersIn the extended cut, we will cover how to address issues that occur when the user clears the customer combo box or when adding a new contact through the subform - specifically, how to set the default value for new records depending on where the form is embedded and several different scenarios where the default value can come from different places. I will show you how to handle all of these situations to ensure your contact form works smoothly in each case. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, microsoft access form filter subform based on combo box, customer contact history, combo box filter, after update event, contact management form, link master child fields, vba form filtering, relational combo box, subform filter property, unbound parent form
Subscribe to Filter Subform
Get notifications when this page is updated
Intro In this video, I will show you how to set up a form in Microsoft Access where you can filter a subform displaying contact history simply by selecting a customer from a combo box. We will walk through building an unbound parent form with a combo box, embedding a contact subform, and using both non-VBA and simple VBA methods to filter the displayed contacts based on the selected customer. We also discuss some limitations of each method and how you can achieve more flexibility with a small amount of VBA.Transcript Are you jumping through hoops just to see a customer's contact history? Are you opening three or four different forms every time you want to check the last thing you talked to them about?
If so, you are working way too hard and I am going to show you an easier way.
Welcome 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 quickly and easily filter a subform based on a combo box in a parent form.
Today's question comes from Wendy in Springfield, Massachusetts, one of my platinum members. Wendy says, I have been using your TechHelp free template and I really like the contact management part of it, but I am wondering if there is a quicker way to view a customer's contacts. Right now I have to open the customer's form and then go into the contacts form from there. Is there a way to build a form where I can just pick a customer from a combo box and immediately see all of that customer's contacts so I can quickly switch between customers?
Well, yes, Wendy, that is what we are going to do in today's video. We are going to make a combo box, you pick a customer from the list, and then it will filter the subform based on whatever customer you pick.
Now this will be a developer-level video. What does that mean? Well, that means there is going to be a little tiny bit of VBA. Literally, all we need are two lines of code, but you have to know where to put them, and that is what I am going to show you today.
But if you have never done any VBA programming before and you are curious, go watch this video. It will teach you everything you need to know in about 20 minutes to get you started.
I am going to show you how to do this without any VBA, but knowing a little bit of VBA gives you some more control. We will talk about this in just a few minutes.
This is the database we will be using in class. It is my TechHelp free template with the customer and the contact management part. You can watch how that was built in this video.
We are also going to build a relational combo box. That is where you can base a combo box on another table or query. We are going to make a customer combo box. It is based on the data in the customer table. So go watch this video. You should know how filtering works.
Then we are going to use the after update event to fire that event to do the filtering after the user updates that customer combo box. So go watch all of these videos. These are all free. They are on my YouTube channel. They are on my website. But watch those and then come on back.
All right. So here I am in the TechHelp free template. This is a free database. You can grab a copy off my website if you want.
Right now, to look up a customer's contacts, let's say you want to look up Jane Cobb. You either go to the customer form and find Jane Cobb or search for them, or go to the customer list and look for them in here.
You can search. You can fill out the rest of Jane. There is Jane right there. So we can open up Jane. Now that we have found Jane, we can go into the contacts. There is this contacts history.
But I want an easier, faster, and quicker way. I want to be able to open up a form, pick Jane from a combo box and boom, there are all of those contacts. If it is something that you use all the time, you can easily and quickly switch between different contacts and different customers.
Now we already have a contact form, this guy. If we open it by itself, you will see it has all the contacts for all the people in it. What breaks it down and filters it normally is when we go into the customer form, this button does the filtering and only shows you stuff for that customer.
We also have another form here, the customer with contacts. That is got that embedded here. But we still have to do some work to find Jane up here. So let's make another form, just an unbound parent form that all it has in it is a combo box. When we change the value in that combo box, it shows us the records for the contacts for that customer.
So let's start with a blank form. I am going to use my single F here; it is just a blank single form.
We are going to copy this guy. Copy, paste, control C, control V. We will call this my contact list F. All right. Contact list F. Let's design this guy. We can get rid of these fields. Since this form is going to have no data in it, there are no records to pick in this form itself. It is just going to have an unbound combo box in it.
Let's go into the forms properties. Go under format. We can turn off the record selectors, the navigation buttons, the scroll bars, all that stuff. Change the color. Do all that stuff if you want to.
Now, in here, all I really want is a combo box to pick a customer. That is easy to do. In fact, in one of the previous videos, we built this guy here called customer LFQ. That just simply puts last name and first name together in a field, just like that with a little concatenation: last name, comma, first name. So we can use this guy to build our combo box.
Form design. Find the combo box. The wizard starts up. That is a good wizard. Get the value from a table or query. Pick queries. Pick that customer LFQ. Next. What fields do you want? Bring them both over. Next. What do you want to sort it by? Well, that LF, that last name, first name. Next. How do you want it to look?
Now since this is based on a query, we do not have that little checkbox here to hide the ID field. So you just have to grab this little slider here and just like that make it so that that has a width of zero. Hit next. Make the field from that query that has the value that you care about. The ID is the one I care about; it is not going to be bound anywhere, so it really does not matter. Hit next. What label would you like? Let's put in here customer. Then we are done.
Let's make this a little darker so we can actually read it. Let's make it black, standard colors. We will make this a little bigger. That a little bigger. Save it. Close it. Open it.
There we go. Contact list with a combo box where I can quickly find Jane Cobb. Now let's drop that subform right here.
We are going to right-click, design view, find your contact F. That is this guy. We are going to drop it right here as a subform. Click, drag, drop. Done. I am going to delete that little label that comes in with it too. Slide it over here. You can make it bigger if you want to. You can make it smaller. You can make it red. You can make it blue. Just don't make it late for dinner.
Save it. Close it. Open it again. Oh, wrong one. Open it. There we go. That is a bit too small, so make it bigger. Let's make it bigger. In fact, I am going to make it bigger like this.
Come here. Give me, and I am going to go up to arrange and then pick anchoring and then stretch down and across. Save it. Close it. Now look at it. Watch what happened. Ooh. Ah. It gets bigger. That is anchoring. I have a whole separate video on anchoring. Watch that video where I talk about anchoring.
You can make it bigger like that just by dragging it.
If you are enjoying this video, if my videos help you, hit that like and subscribe. It helps me, it helps the channel, and helps keep these videos free.
Now, one of my pet peeves with the combo box wizard: it is a good wizard, but it does not let you name the box as part of the wizard. So let's make this customer combo. It is customer combo box.
Let's save that.
Now, normally when you drop a subform into a parent form, you use the link master fields, link child fields. If Access sees that you have a field up here that matches a field in there, usually it makes those for you. But it did not in this case.
Now, we can use this if you want to. This is the non-VBA method that I mentioned earlier, and I am going to show you this in case you do not care about VBA, you do not want to be a programmer. That is fine. So you can do that. You could put the link master fields and link child fields in here. We are going to link the master field. It is going to be the customer combo, and the child field will be customer ID; that is a field down here. That red box there, that is actually a hidden box.
Save that. Close it. Close it. Now, if you open it up, it appears blank, and that is because the value in this right now is a zero; there is nothing in there, or it is null, I forget which one. But if you drop this down and pick somebody, it filters it because of that link master field, link child fields property. That is fine; that is a perfectly good solution.
But the one reason I do not like that is because sometimes on this contact list, you might want to see all of the contacts in here for everybody. You could sort this and get a chronological list and all that stuff. So there is another way that gives you a little more flexibility.
So what we are going to do is I am going to go and turn that property back off again. We are going to just get rid of these. So there is your non-programmer method. For those of you who do not care about VBA, you are done.
But if you want to learn a tiny little bit of VBA, watch this now. This is a little bit more flexible because when you open this form normally, you can see everything in here. You can sort this. You can even make it so that these are sorted normally by date descending, so you can see the last couple of contacts up here. You can add the customer name in here if you want to see who it is. So it gives you a little more flexibility.
What I want now is when I drop this box down and pick somebody, at that point, I want to filter this box using this subform's filter property. How do we do that?
We are going to go into here, open up the properties for this box, and go into events and find the after update event. When this box is updated, I want to do some stuff. Hit the dot dot dot button. That brings up the VBA editor. Let me resize it. I am using dark mode. I have a whole separate video on that too.
So what are we doing here? When the combo box is updated, I want to change the filter property of the contact form. Now let's check the name of that form real quick. Come in here and just click on this guy. See, it is contacts. Because what happens is sometimes it gets the caption property of this guy. Its caption, let me see what its caption is under format. See, its caption is contacts. I do not like that.
So let's go back to the subform object itself. Make sure you click on it; you get this border outline. The border is the outline; that is the subform object inside the parent form. Now I want to make sure its name is contact F, not contacts. Watch for that. If your form does not have a caption, it will default to the name of the form. Otherwise, it puts the caption property in there. Another one of my pet peeves.
Now back in our code, here I can say contact F. That is the subform object dot form. Now we are going to the form collection, the collection of properties for this form. It has a filter property equals customer ID equals customer combo. So I am setting the filter property of the form. What form? The contact F subform. You just have to kind of remember that; that is how it works. Set it equal to customer ID equals, and then whatever value is in my customer combo.
Once we set the form's filter property, we have to turn the filter on: contact F dot form dot filter on equals true. That is it. That is all you need.
Save it. Debug, compile once in a while. Close it. Close it. Close it. Open it. It starts off with everybody. Drop the box down, pick Jane Cobb and it filters based on whoever you picked. You can do multiple people if you want to pick another one at the same time. We could do a video on that. There is something called a multi select list box you can use where you can pick multiple people. I have videos on that. Go check them out on my website.
This is all you need for something simple.
Now this works great for doing what we wanted to do. We just want to open a form and be able to quickly switch between different customers and find their contacts.
But what happens if the user blanks the customer combo box? You get an error message.
What happens if you want to add another contact on here? Let's say you are on Jane Cobb and we talked about Rita. Those of you who know Firefly know what I am talking about.
Now if I go away and then come back, where is Jane Cobb? There you are. It is not on there. Why is Rita not on there? If you look in the contact table, there it is. It is up top. It has a zero because the subform didn't know where to get the new default value from.
So how do we solve those problems? We will talk about that in the extended cut for the members. Silver members and up get access to all of my extended cut videos, and of course, there are hundreds of them by now. We have a lot of different options to pick from because that default value for that new record can come from several different places. It could come from this combo box. If this form is embedded in this, in the contact list form, it could come from the customer form. Now it knows to get its default value from this form. It could also be embedded inside this and need to use the link master and child fields. So there are several different situations where this guy could get its new default value from.
We are going to talk about all those situations in the extended cut. Check it out. You will find a link down below.
If you are not a member yet, what are you waiting for? Join. Everybody gets some free training and you get lots of extended cut videos. Gold members get a code vault and all kinds of stuff.
If you like learning with me, if you like this VBA programming stuff, this newfangled stuff, it is not really new. VBA has been around for decades, but it is still great and powerful, just like us. It does all kinds of cool things. I have lots of developer lessons on my website, just finished 53. There are a few videos to keep you busy.
I have this thing called a search seminar, which is several hours long, and I teach you all the different ways you can do all kinds of cool searching and sorting and filtering and custom SQL lists and all kinds of stuff inside your database.
If you really want to learn about searching and sorting and filtering and all those things with moving molecular structures and moving parts, go check this out too.
But that is going to do it. That is going to be your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I will see you next time, and members, I will see you in the extended cut.Quiz Q1. What is the main objective of the video tutorial?
A. Building custom reports in Access
B. Filtering a subform based on a combo box selection in a parent form
C. Sending emails automatically from Access
D. Creating dashboards in Access
Q2. What kind of form does the instructor recommend creating for quickly viewing a customer's contacts?
A. A bound form linked to the contacts table
B. An unbound parent form with a combo box and subform
C. A split form showing both customers and contacts
D. A navigation form with embedded macros
Q3. What does a "relational combo box" refer to in this video?
A. A combo box that displays formatted phone numbers
B. A combo box based on data from another table or query
C. A combo box with default values hard-coded
D. A combo box that opens a hyperlink
Q4. Which Access property allows you to connect a subform to a parent form for filtering without VBA?
A. Record Source and Control Source
B. Link Master Fields and Link Child Fields
C. AutoSize and Anchor
D. Modal and Popup
Q5. In the manual (non-VBA) method, what fields are used to link the combo box to the subform?
A. CustomerCombo and ContactDate
B. CustomerCombo and CustomerID
C. CustomerName and ContactSubject
D. CustomerEmail and ContactID
Q6. What is the main benefit of using a little VBA code for filtering the subform compared to the link master/child fields approach?
A. It makes the form run faster
B. It adds more flexibility, such as viewing all contacts when no customer is selected
C. It encrypts your data
D. It allows multi-user editing
Q7. Which event is used to trigger the filtering of the subform using VBA?
A. On Open
B. After Update of the combo box
C. On Current
D. Before Insert
Q8. What are the two lines of VBA code primarily responsible for in this solution?
A. Changing subform record source and sending emails
B. Setting the filter property of the subform and enabling the filter
C. Validating email addresses and closing the form
D. Sorting records by contact date
Q9. What issue might occur if the user clears the value in the combo box after filtering the subform?
A. The form automatically reloads
B. An error message may appear
C. The database crashes instantly
D. All contacts are automatically deleted
Q10. Why might someone prefer the VBA solution over using link master/child fields?
A. VBA is always faster to write
B. VBA allows for more complex filtering logic and flexibility
C. VBA removes the need for forms altogether
D. VBA hides the data completely from users
Q11. What do you need to check when writing code for the subform filter in VBA?
A. The color of the form background
B. The name of the subform object in the parent form
C. The data type of the contact date
D. The language settings in Windows
Q12. What did the instructor say was one of his "pet peeves" about the combo box wizard?
A. It automatically adds macros
B. It does not let you name the combo box during creation
C. It sorts data in reverse order by default
D. It always uses bold fonts
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B
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 video from Access Learning Zone is all about streamlining the process of viewing a customer's contact history in Microsoft Access. If you find yourself repeatedly opening multiple forms just to check a customer's last interaction, there is a much easier solution, and that is exactly what I am going to cover today.
A common question I receive involves finding a quicker way to view all contacts for a specific customer without navigating through several different forms. For this lesson, I will demonstrate how to set up a form where selecting a customer from a combo box will immediately display that customer's contact records in a subform, letting you quickly switch between different customers' contact histories.
This tutorial reaches a developer-level skill set, but don't worry, you only need to add a small amount of VBA code (just two lines), and I will guide you through exactly where and how to use it. If you are completely new to VBA, I recommend checking out my beginner VBA tutorial first, since understanding a little bit of VBA gives you much more control over your forms.
We will be using my TechHelp free template database for this demo, which features customer and contact management. If you want more context on how this sample was built, you can find previous lessons on my site.
The central feature in this example will be a relational combo box, meaning it will be based on another table or query - in this case, a customer combo box based on the customer table. Filtering is a key concept here, so if you are new to how filtering works, make sure to look over my introductory resources on that topic too.
To set this up, imagine the typical way you might search for a customer's contacts: opening a customer form, searching for the customer, and then going into the contacts page. That works, but it is inefficient if you do this often. What we want is a form where you can select, say, Jane Cobb, from a drop-down list, and immediately see all of her contacts, with the ability to quickly switch between different customers.
We already have a contact form that displays all contacts for everyone, and it is usually filtered when accessed via the customer form. However, creating a separate unbound parent form with just a combo box for choosing a customer allows for more direct and rapid switching between contact histories.
Here is how you set this up: Start with a blank single form in design view. Remove any unnecessary fields, as this form will just host a combo box and a subform. Go into the form's properties to clean up things like record selectors and navigation buttons if you wish, and adjust formatting to your taste.
For the combo box - the customer picker - you can use a query that concatenates customers' last and first names, so the combo box offers a clear, sorted list to choose from. When setting up the combo box with the wizard, you will base it on this query, select the fields to appear, sort as desired, and adjust column widths so only the name is visible (hiding the ID column).
Once your combo box is ready, add your existing contact form as a subform to this new parent form. Adjust its size and anchoring so it scales nicely if you resize the window. I recommend checking out my video on anchoring if you want more details on how that works.
The next step is to set up filtering. Normally, dropping a subform into a parent form lets you use the Link Master Fields and Link Child Fields properties to automatically filter the subform based on a field in the parent. In our case, you can set the master field to be your combo box and the child field to be the customer ID in the subform. This method does not require any VBA and will work just fine if you do not want to write any code.
With this technique, when you select a customer from the combo box, the subform automatically updates to show only that customer's contacts. However, one drawback here is that you cannot easily see all contacts for all customers at once if you ever want that. For a bit more flexibility, the VBA approach is preferable.
By removing those link properties, and instead adding a couple lines of VBA code to the combo box's After Update event, you gain more control. Specifically, once a user selects a customer, your code sets the subform's filter property so it only displays contacts for the chosen customer, and then turns the filter on. This approach also allows the form to display all contacts initially, until a filter is set.
If you are interested in learning how to handle advanced scenarios - like what happens if the user clears the combo box, wants to add a new contact, or needs the new record to automatically inherit a default value - we explore all of that in the Extended Cut available to members. In those lessons, I walk through different ways the default value can be determined, depending on how the subform is used or embedded elsewhere.
If learning more about VBA or Access development interests you, there are plenty of developer-level lessons and long-form seminars on filtering and searching available on my website. I encourage you to check those out if you want to deepen your skills.
To sum up, setting up a combo box on a parent form to filter a subform based on the selected customer can make navigating contact histories much faster and more intuitive. This increases both your efficiency and the usability of your database.
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 parent form with an unbound combo box
Building a customer combo box sourced from a query
Concatenating customer first and last names in the combo
Placing a subform on the parent form to display contacts
Adjusting form properties to remove navigation elements
Anchoring and resizing subforms for better UX
Setting up link master fields and link child fields for filtering
Filtering the subform using combo box selection without VBA
Filtering the subform using the combo box with VBA
Using the AfterUpdate event to filter with VBA
Setting the filter property and enabling filter-on
Handling form and subform naming for proper code references
Demonstrating filter effect when combo box value changesArticle If you are tired of clicking through multiple forms just to review a customer's contact history in your Access database, there is a much easier approach. Instead of hunting through forms each time, you can build a single form with a combo box that lets you instantly filter a contacts subform for any customer you choose. I am going to walk you through exactly how to set this up, both with and without VBA, and explain why using a bit of code generally gives you more flexibility.
The goal is straightforward: create a parent form with a combo box listing your customers. When you pick a customer, the embedded contacts subform instantly filters to show only that customer's contact history. This setup is much faster than navigating through separate customer and contact forms.
First, start with a blank, unbound form. It will not be tied to a table, because all it needs is a combo box at the top. This combo box should list all of your customers, ideally using something like a concatenated Last Name, First Name for easy picking. You can use a saved query for this combo box's row source in case you already have a query combining and sorting your customer names.
Open the form in design view, and use the combo box wizard to base your combo on your customers query or table. Choose to display both the customer ID (which you can hide by setting the column width to zero) and the customer name. Finish the wizard, label your combo box as "Customer," and name the combo box something meaningful, like "CustomerCombo."
Next, add the contacts subform to this parent form. In design view, drag the contact form (which should already show all contacts for all customers in datasheet or continuous view) onto your parent form as a subform. You can resize the subform as needed and adjust the anchoring properties so it stretches when you resize the parent form.
At this point, you have a parent form with a combo box for customers at the top and a subform below showing all contacts. The next step is to filter the contacts by the selected customer. There are two ways to do this: with or without VBA.
For a VBA-free solution, you can use the Link Master Fields and Link Child Fields properties of the subform control. In the subform's property sheet, set Link Master Fields to the name of your combo box (for example, "CustomerCombo"), and Link Child Fields to the field in the contacts table that links to the customer, usually "CustomerID." When you pick a customer from the combo box, Access matches the value to the subform and filters the contacts accordingly. This works, and if you never want to see all contacts at once, this method is fine.
However, this approach limits flexibility. For example, you might want to start with all contacts visible, or be able to clear the filter and show every contact for all customers, or customize the filtering even further. For these situations, a small bit of VBA is ideal.
To add this flexibility, first clear the Link Master Fields and Link Child Fields properties so the subform no longer filters by default. Now, right-click your combo box and go to its properties. Under the Events tab, find the After Update event and click the ellipsis to open the VBA editor. In the generated event handler, you will write code to filter the subform by the selected customer.
Suppose your combo box is named CustomerCombo and your subform control is named ContactF. Your VBA code should look like this:
Private Sub CustomerCombo_AfterUpdate()
ContactF.Form.Filter = "CustomerID = " & Nz(Me.CustomerCombo, 0)
ContactF.Form.FilterOn = True
End Sub
Here is how it works: when the combo box value changes, this code sets the Filter property of the form inside the subform control so it only shows contacts where CustomerID matches your selected customer. The Nz function is used to ensure the code works even if the combo box is empty (though you may want to add more handling for that scenario). Then, by setting FilterOn to True, the subform updates immediately.
Save and compile your VBA code, then return to your form. When you first open it, the subform shows all contacts; selecting a customer from the combo box filters the subform instantly to just those contacts. You can style and resize your form as desired. This approach gives you more freedom in how and when the filtering occurs, and you can add further enhancements, such as viewing all contacts when the combo box is cleared or handling cases where you add new contacts.
To summarize, by creating a simple parent form with an unbound customer combo box and an embedded contacts subform, and either using subform field linking or just a few lines of VBA, you can quickly switch between customers and view their contact history in seconds. This eliminates tedious navigation and gives your users a much more efficient workflow in Microsoft Access. |
