Association 11
By Richard Rost
3 years ago
Microsoft Access Association Database, Part 11
This is Part 11 of my Microsoft Access Association Database. In today's video we are going to add a subform to select groups (including families, companies, interests, and so on) from the main person form. Will also add a combo box to filter the group type in that subform.
Pre-Requisites
Members
No extended cut, but here's the database file:
Links
What's Next
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, group subform on person form
Subscribe to Association 11
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access Association Database by creating a way to see all of the groups a person belongs to, such as families, companies, and interests, directly from the persons form. You'll learn how to set up a subform to view and filter group memberships by group type, use combo boxes to manage linked records, create queries to support dynamic filtering, update the user interface with new tabs, and troubleshoot filtering issues in real time. This is part 11.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today is part 11 of the Association Database. In part 10, we made groups, and inside each group type, you can see the list of groups like families, companies, interests, all that stuff. We made it so you could pick a group and see all the people that were in it.
Today, we're going to go backwards. We're going to make it so you can go to the persons form and see what groups they're in, what family they belong to, what their interests are, and so on. We're going in the reverse of the last one.
Strap yourselves in today, folks, because today's lesson is going to be a good one. It's a little complicated, so you might have to watch it more than once to get everything.
Today's one of those lessons that I wanted to actually run through first myself real quick just to make sure I don't have any major screw-ups. Sometimes I leave intentional screw-ups in my videos so you can see the mistakes that I make, but I also don't want to fumble around like an idiot. I've done this before in my ABCD video, for example, in a couple of Developer lessons, but it's been a little while.
So here's what it's going to look like: here's the person list. Go to a person. Notice we're adding another tab across the top here, Groups. We're going to set the default to Family, and now you can see a person can belong to multiple families. If you want to put this person in two families, want to change the group type, drop this combo box down, pick Company, drop it down again, there's Interests. Now you can see you can have any number of group types in your group lists. If you want to add more of these, just go to your helper table and add another one.
I want to add a different interest in here, drop it down, pick something else. There's one thing I didn't do: I didn't edit this combo box to limit this to just Interests, so we'll do that in this class too. This was my quick run through just to show you what it's going to be like.
There, I fix it. Drop it down, now it's just got Interests. Drop this down, go to Clubs, got DnD club. Drop it down, go to Companies, at least the companies. Again, this was just my quick run through, so let's go ahead and build this together.
This, I'm looking at, is a lot of stuff in this lesson. We might need to break this up into multiple lessons, but we're going to do as much as we can. I try to keep these to about 10 to 15 minutes, so let's get started.
I'm going to close that. We'll take our 10 database, right-click, drag, copy it over here, make it 11. Now, follow with me, folks. If you have to watch this multiple times to get it all, don't be afraid. This is expert level stuff with a little developer sprinkled in, so this is not easy stuff, folks.
We need a subform goal on our person form over here, like I showed you before. Let's copy our continuous form, this thing, right? We'll copy this, Control-C, Control-V. We will call this our Person X Group F. Our Person X Group. Because we're going the opposite way, the other one was a Group X Person, this one is Person X Group. We're starting with the person and we're showing groups.
Open it up in Design View. We're going to set the last of the colors first. Always get the colors first, folks. Very important. Usually, I stick away from theme colors, but just to keep things simple, I'm using them for these lessons.
Now, we don't need these, and we don't need that. Get rid of that. We're actually going to get rid of this too, but we'll leave it there for a second. Let's change the record source to the Person X Group T. It's where we're getting all of our data from. Save that, close it, and open it up. It's got a bunch of blank records.
There should be exactly the same number of records as you have in the table, which is 26. Now, picture this as a subform inside the person form. What do you want to be displayed here? You want the list of groups to be displayed there, so we're going to make a combo box to display the groups.
Go to Design View, get rid of this guy, and we're going to put a combo box here to display the groups. Get the values from table or query, get it from Group table. We need the Group ID and the Description. Next. We're going to sort by Description. Next. That's what it's going to look like, and it'll show all of them for now, until we filter it later. That's the thing I forgot to do at the end of my preview thing that I was showing you.
Next. What are we going to do? We're going to store that value. We're going to pick a group and we're going to store it in the Group ID in the junction table. Next. Because we can add and edit and delete and all kinds of stuff in here, what label do you want? It doesn't matter. We're going to delete it. Goodbye.
Slide this up and make it bigger, and bring the bottom up. Save it, close it, open it up, and now you should see a record for all of the group records. This should match the junction table one for one - 26 records, 26 records.
Now, I can put this in right now as a subform because this is based on the junction table Person X Group T, and the junction table knows about the Person ID. You don't always necessarily have to have it on the form as long as it's part of the record source underneath it. It knows about Person ID, so we should be able to make it a subform right now.
To do that, all we have to do is open this up, and we're going to make another one of these fancy labels. Remember this guy: copy, paste, slide it over here. Open up the properties for it. It's Label 45, we'll change that in a second. We're going to go to All. This will be the caption, it will be Groups. This will be the Group label. Let's make it nice and short. Don't need to be that big, you can put it in front if you want. That's up to you. If you want to make this the first default tab, change that color, make it green.
Now, what kind of code do we use to switch? Let's do a brief review event On Click. What is that On Click event? Real simple, it's one line. I noticed there's some Family combo code in here. We need to go through the whole database and just do a quick search for Family. Anywhere we see that in our VBA code, delete it. I'll put a note to that on our to-do list. There might be a few things bouncing around, that's fine.
This is all we need: subform control dot SourceObject equals and that. So we're going to go over here, go to our Groups, go to the On Click event, dot dot dot, drop that in there, and what's the name of the form? It is the Person X Group F. Save it, close it, open back up again, click, and there we go.
Looking good so far. There's all of the groups that I am in: Ross, Trekkie, Amichram, Book Club, Firefly Server. Go to somebody else, go to Jean-Luc. There you are. There's the groups that he's in.
My Roomba vacuum just kicked on. I've got one of those cool Roombas that goes around and when it's done, goes back to the base station, and the base station sucks all the junk out of the Roomba and it fills up in a bag. You only have to change it like once a week. It's pretty cool. I like it. I highly recommend it. I went through two or three of them, though. Roomba's customer service was excellent. They made me happy, I just returned it, they sent me another one, and finally we got one that works. It just would get stuck everywhere, would stop, or would just sit in the middle of the floor.
This is not a Roomba commercial. I love my Roomba, but that's what you just heard.
Anyway, what we're seeing here is everything that this person is in, all the groups. Now, we're going to add a combo box to filter this list by group type: Family, Company, Interests. You'll pick the group, then in here you'll pick a combo box, and then you'll be able to filter that by their group types. Let's go back. I like to work right in the source. Let's go back to this guy: Design View.
Now I need a combo box here, and the combo box is going to let me pick a group type, one of the ones that are in there. The group type is going to be from my helper table - there it is, and it's going to be any Helper T record of type 9.
Now, this will be the second time I've had to write this SQL, so I'm going to make a query out of this because it's something that I think we're going to use a lot. Go create: Query Design. Bring in Helper T. I want the Helper ID, Helper Type T, Helper Value. You'll see why I'm bringing them in like that in a second. I want the Helper Type ID to be 9, and I want to sort by Helper Value. Save this as my Group Type Q, because I'm going to be using this list often. It's a list of group types.
Now I can just call Group Type Q. Remember when we did the last lesson, we made a really complicated SQL statement to pull this guy, but now I just make it. I just thought of it after the last one was over: just make a query out of it.
Now I've got the query that I need to make the Group Type combo box up here, right? Form design. Combo box, drop it right there. Look up the values of the table or query, queries, Group Type Q. Otherwise, you're writing SQL to select from Helper Type where the Helper Type ID is 9. See, I try to avoid writing lots of queries if possible, but they have their place. There's a reason for them. That's one of them: to make complicated SQL that you're going to be using a lot easier.
We don't need the Helper Type ID. We don't need that one in this one. Next. We're going to sort it by Helper Value. Next. That's what it's going to look like. This is based on a query, so we don't get the little checkbox here to hide the ID column. That's fine. Next. That's the bound field. Next. Where do you want to put it? We don't want to just hang on to it for later use. We're not storing it anywhere. We're just going to use this value to filter this list of records. Next. Label, it's going to go away. Next, delete it.
Let's make this guy bigger and more important looking like I did in my run through. Bring it out to maybe there, make it a bit taller, like that. I changed the font a little bit bigger, what did I do, 14 point? Yeah, I'm old. A little bit background color, maybe that. Okay, that's good. This doesn't have to be quite so big. And we don't need a footer for this one. Save that, close it.
Now, let's go back in here, open it up, and there's our combo box to filter. It's not actually working yet, we haven't plugged it in yet, but it's there. We're getting there.
The next thing I'm going to do is give this thing a default value. So when I click on Groups and it opens up, when I come in here and click on Groups, this is going to start on whatever you think you're going to use the most. I think Family should be the default, and the Helper Type ID for Family is 58.
So, 58 right there. We're going to make the default value 58. In the combo box, Design View, you're not Combo6 either, you're going to be Group Type Combo. Your default value under Data is going to be 58. That way, when you open it up, you're going to start on Family. We still have to tie it into filter, but that's what it's going to start off with.
Now, I want to use this to filter this. But there's one problem that we have: this is the Group Type ID, which is also a Helper Type ID or Helper ID. This list of records is based on the junction table, and unfortunately the junction table knows nothing about the Group Type ID. So we have to make a query and add into it the same stuff here, but just add the Group Type ID. These records out here don't know, they're just getting that from the combo boxes. So we need another query. Create, Query Design. We need to bring in the junction table, then the Group table, because we have to link it to that Group Type ID right there. You could, if you want to, bring in the Helper table if you want to see the Helper Value in the query, but we don't really need it in here. So I'm going to bring in the star for that and then Group Type ID.
We're going to save this as my Person X Group Subform Q. We already have a Person X Group Q, so this is going to be specifically for that subform.
If you run it now, you'll see exactly what we got, and now we know what the Group Type ID is for each of the records. So now we can filter based on that. Are you with me? This is a complicated one.
Now I have to change the record source for this guy to that query. This will now be the subform, and it should look the same. Nothing should appear to have changed if you go back in here. Same stuff. We haven't actually applied the filter yet, but now these records know what group type they belong to. This knows it's a company, this knows it's a family.
Now the next step is going to be making it so that we only see the records in here for that specific group type. To do that, we're going to modify the query. Let's close this, go back to the query, Design View. This Group Type ID, we're going to give it a criteria. We're going to say this Group Type ID should only be records where it equals that filter combo box we created. What's the name of that filter combo box? Let's go back in here. There's a lot to show you. It's People F (Forms!PeopleF), this thing is the subform control. This thing is called the Group Type Combo.
So it's going to be Forms!PersonF!subformcontrol.Form!GroupTypeCombo. We'll put that in here.
It's going to be =Forms!PersonF!subformcontrol.Form!GroupTypeCombo
If you don't know what this is, go watch the TechHelp video "Value from an Open Form." I talk about how to get a value from a subform, too. It's really crazy.
Save the query. Now let's go back to the form. Now, I'm not seeing anything. That's weird. Let's try someone else. No. Let's open that query up and see what's going on. Wait, I see it in there now. There's data in there now.
Here's the problem: and I ran into this myself when I was going through my run through and refreshing my memory. The problem is that the query is trying to load up and get its default value before this form is finished loading, so it has no value it can get here. That value becomes a null and it doesn't show you any records.
So what you have to do is, in this button, this label code, when this thing loads up this form, it has to let it finish loading and then requery it. This form has to load, the record set will load with no records in it, then you requery it. Now when you requery it, the combo box has a value at this point, and you'll get your records.
Right-click, Build Event. Right after this line here (that's going to load up the form), all you have to say here is subform control dot Requery. That will force the records to load a second time. Now they've got a value.
There's a really complicated way you could do this with just one pass, but this works just fine. I've been doing it for years. Double-click Groups, there's my Family. Jean-Luc, Pamela, etc.
So the button loads the form. The form has nothing in this default value yet. No records load because the query says, nothing in there. The default value is assigned and you're telling it now to requery, and it can pull in the stuff.
We're running a little over time that I wanted to be, but I'm in the mood to finish this one lesson. Next problem: now, when I change this guy to a different group type, this doesn't requery. Easy fix, we just need to put some code in this combo box so when we change it, it refreshes that list.
Come back in here, Design View, this guy, open up its properties, go to Events, go to After Update, this is going to run when you change the value in this combo box. This one's going to be a Me.Requery, that'll force the form - the whole form, Me - to requery. Save that, close it, open it up, go to Groups, change it to Interests, there's Interests. Go to me, Groups, default Family loads, go to Interests, there's all my Interests.
Next up is the problem that I had in the preview video before, at the beginning of this video when I showed you: if you go to add another interest, drop this down, everything is showing up in here from all the different group types. Now we just need to modify this combo box to recognize that filter.
So, Design View. Actually, I closed it. I keep going into Design View, but it's going to load up the default subform. So go right back to here, Design View, open up this guy's properties. It's called Combo4, we don't like that. This is going to be the Group Combo.
Your data - let's take a look at the data. First things first, let's simplify this. We only need GroupID and Description from GroupT order by Description. What I need in here is a WHERE condition. I need to say WHERE the GroupTypeID equals the GroupTypeCombo that's on top of this form. Because GroupT has the GroupTypeID field in it, and GroupTypeCombo is a combo box on this form. Save it, close it, open it, drop this down, and now you see just a list of Families. Change it again to Interests. Oh, look what happened now.
What happened now is when this form initially loaded, you had a list of Families because Family was the value there. This combo box also needs to tell these combo boxes to requery.
This guy also needs, in his After Update event - you've got Me.Requery and you've got GroupCombo.Requery. Because you're going to load a whole new list of records from a different group, you have to requery that combo box too, so it's got the proper list of values.
Go to Groups, there's a list of Families, drop this down, Interests, it requeries that, and now you'll see just a list of Interests down here.
As of right now, there's no way to prevent them from adding the same interest twice or three times. If you want to do that, you could do it with a composite key. Here's a TechHelp video on it. I'll put a link down below. I will be adding this to the outline. We will add this feature eventually to this database because that's a pretty big thing. If you're basing mailing lists on families or people with particular interests, if they're in the same group three times, then it'll create three mailing labels for them. You don't want that. So we'll add that in a future version. If you're impatient and you want to do it right now, go ahead and do it now.
Now we can go both ways. We've got - and you know what, we've got to put the button on here - change a button on here too. Design View, this Family list, we're going to go Groups, we're going to put Person List on top because that's kind of like the main thing. Then we get Groups. We'll change this to the Group button. Right-click, Build Event, notice it was where's the Family, here's the Family list, get rid of that, delete, delete, and this will be just do Command.OpenForm. What was the name of it, Group? Was it GroupTypeF? Yeah, I think it was GroupTypeF. Save it, close it, open it, Groups, there's that big thing. If you want to go get the big list of Families, there you go, there's the list of families and who's in it.
You could do this with a series of continuous forms that open other continuous forms, like this is one form, this is another form, this is another form - you open one, it opens the next one, it opens the next one. That's kind of boring, so I figured I wanted to show you something cool just like this. You could do this with multiple forms, but again, this is a neat technique. I like to show it. It's pretty cool. I do this in a couple of my different videos.
While I'm thinking about it, let's search out here, do a Control-F to find, we're going to look for Family, see if there's any other Family code floating around. Click on Current Project. That will search all of the VBA everywhere in the database. Find. Didn't find any. I must have caught it all. We deleted some forms too, so that had some Family stuff in it. Debug, Compile - never a bad idea.
I think we're good.
We're almost at a half an hour. I'm trying to keep these Fast Tips fast. These are supposed to be like 10 minutes apiece, and yeah, I could be cheesy and spread them out and break this and work this into three different lessons, but I hate breaking stride when I'm in the middle of showing you how to do something that's going to take a while. I'd rather just make a longer lesson.
Don't be like that guy that said he wanted more breaks. You can just press pause anytime.
That's your Fast Tip for today. That's a lot of stuff in that video. That's more advanced than I was planning on it being, but you guys said in the poll you wanted some more advanced developer type stuff. There you go.
That's your Fast Tip for today. Live long and prosper, folks, and I'll see you next time.Quiz Q1. What is the main new feature implemented in this lesson compared to the previous one?
A. Viewing which groups a person belongs to from the person form
B. Creating a new group type table
C. Generating mailing labels for each group
D. Exporting group lists to Excel
Q2. What is the purpose of the 'Person X Group F' form created in this lesson?
A. To show all people in a selected group
B. To display all groups that a selected person belongs to
C. To add new people to the database
D. To import group data from other sources
Q3. What type of form was used for the 'Person X Group F' subform?
A. Single form
B. Datasheet form
C. Continuous form
D. Split form
Q4. When filtering the subform to show only groups of a selected type (e.g., Family, Company), what is used to provide the list of group types?
A. A hardcoded list in a macro
B. A helper table and a saved query
C. Manual user entry
D. The Group table itself
Q5. Why did the instructor create a separate query for group types rather than writing the SQL statement each time?
A. Queries can be scheduled to run automatically
B. Queries are required for all subforms
C. It makes complicated SQL easier to reuse and maintain
D. Queries are faster than SQL statements in forms
Q6. Why did no records initially appear in the subform when filtering by a group type after setting the filter?
A. The group types were not yet entered in the database
B. The filter value was not set when the query first loaded
C. The database file was corrupted
D. The query did not include the correct fields
Q7. How was the issue of the subform not displaying records due to filter timing resolved?
A. Closing and reopening the form each time
B. Adding a secondary query with macro code
C. Forcing a requery of the subform after it finishes loading
D. Increasing the form's load timeout
Q8. What must happen when the user changes the selected group type in the combo box to ensure the record list updates?
A. The form must be reopened and closed each time
B. A code in the After Update event must requery the subform
C. The user must manually refresh the records
D. The database must be compacted
Q9. Why was the combo box that chooses a group for a person modified to filter only by the selected group type?
A. To prevent database errors
B. To make the form load faster
C. To ensure only relevant group options appear for selection
D. To display all groups regardless of type
Q10. Which best describes the reason for eventually adding a composite key to the Person X Group table?
A. To link groups to companies
B. To prevent duplicate group memberships for a person
C. To speed up record sorting
D. To allow unlimited group assignments
Q11. What does the default value of 58, set in the group type combo box, represent?
A. The number of records in the database
B. The Helper ID for the Family group type
C. The total number of group types available
D. The ID for the Interests group type
Q12. Why were queries used instead of direct SQL statements in several places across this lesson?
A. Queries run faster than SQL on forms
B. Queries allow easier reuse and maintenance for complex logic
C. Queries are mandatory for all form controls
D. Queries are required for all reports
Q13. Why does the instructor recommend searching for and removing any lingering 'Family' code in the project?
A. To improve form appearance
B. To avoid bugs from obsolete references
C. To rename fields automatically
D. To enable group merging
Q14. What is the main advantage of building both 'Group X Person' and 'Person X Group' forms/subforms?
A. To allow viewing relationships in both directions
B. To double the number of records
C. To separate companies from families
D. To increase database performance
Q15. What is the purpose of the After Update event for the group type combo box?
A. To delete all records with the selected group type
B. To reset the group membership list to blank
C. To requery the subform so that records match the selected group type
D. To change the color of the combo box
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-B; 9-C; 10-B; 11-B; 12-B; 13-B; 14-A; 15-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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on enhancing our Association Database. This is part 11 in the series. In the previous lesson, we set up group types — such as families, companies, and interests — and configured it so that you could select a group and view all the people within it.
In this lesson, we are tackling the reverse process. Instead of starting with a group, we're going to begin with an individual person and show which groups they belong to. We'll make it so that from the person's record, you can quickly see all of the families, interests, companies, clubs, and other groups they are associated with.
This is a fairly advanced topic, so feel free to revisit sections as needed. We are taking a deeper look at handling many-to-many relationships using junction tables and subforms. There is some complexity here, but I will walk you through it step-by-step.
The goal is to add a "Groups" tab to our Person form. When selecting an individual, the tab will default to showing their family associations. You can then use a combo box to switch the group type — perhaps to company memberships or interests. This allows for easy navigation and a clearer understanding of all the group affiliations tied to a specific person. Adding new group types, such as clubs, is straightforward — simply add more records to your helper table.
The initial setup involves copying the existing continuous form we've used in the past — specifically the one that handled Group X Person listings — and creating a new version that shows Person X Group. This flips the directionality: instead of starting with a group and listing people, it starts with a person and displays their groups.
First, I set up the foundation for the subform and adjusted the layout to match our color scheme, sticking with theme colors for simplicity within these lessons. We then link our new subform to the junction table Person X Group T as the data source to ensure we are displaying the correct associations.
Inside the subform, our central feature will be a combo box for selecting groups dynamically. This control connects to the Group table, showing both Group ID and the group description, sorted for ease of use. However, at this point, the combo box is showing all groups, not just the relevant type — this is something we will refine shortly.
Once the subform is set up and records are displaying correctly, the next step is to bring this subform into the main Person form as its own tab labeled 'Groups.' This integration leverages the fact that our junction table already includes the Person ID, allowing Access to manage the parent-child link seamlessly. I create the tab, update its label and appearance, and include the event code that controls which subform is displayed when the corresponding tab is clicked. I also make sure to do a quick search throughout the database to remove any outdated references to "Family" in the code, since we've expanded the concept to include multiple group types.
The next layer of interactivity we want is the ability to filter which group types are displayed. To do this, I add a combo box at the top of the Groups tab that lists all available group types such as Family, Company, or Interest. Instead of writing out the necessary SQL each time, I create a saved query called Group Type Q, which draws from our helper table and is filtered to only show group types. This reduces repetition and improves efficiency as we'll use this query repeatedly.
When configuring the combo box, I specify that it's not meant to store data, but rather serves as a filter for what appears in the subform below. I set it up so that Family (whose Helper Type ID is 58) is the default selection, as that's often the most commonly referenced association.
A key technical challenge arises at this point. The subform needs to filter its records based on the selected group type in the combo box. However, the junction table doesn't inherently know the group type — we solve this by constructing a new query, Person X Group Subform Q, joining the junction and group tables so each record can be connected to its group type. This new query becomes our subform's record source.
To ensure the subform only shows records for the chosen group type, I add a criteria reference in the query that points directly to the group type filter combo box. Here it's important to use the correct syntax to reference a control on a subform — Forms!ParentForm!SubformControl.Form!ControlName. If you're unsure about referencing controls on subforms, I recommend reviewing my video on how to get values from an open form.
There is an initial hiccup: when the subform loads, its query sometimes tries to retrieve the filter value before the form is fully loaded, resulting in blank results. To solve this, after loading the subform, I programmatically force an immediate requery. This ensures the default filter is applied and records are displayed as soon as the Groups tab opens.
The next behavior we need is for the subform to refresh whenever a user changes the selected group type. This is accomplished by using an After Update event on the filter combo box, triggering a requery of the form to pull in new data corresponding to the selected type.
Another important tweak is to ensure that when adding new group memberships for a person, the available choices in the group selection combo box are limited to the currently selected group type (rather than showing every group in the database). This involves editing the combo box's row source to filter based on the active group type filter — specifically, by matching the GroupTypeID. To keep everything in sync, we update the After Update event of the group type combo box to trigger a requery of this group combo box as well.
At this stage, the system is nearly complete: you now have a Person form with a Groups tab that displays and filters group associations, and combo boxes reflect only appropriate groups based on the selected type. You can navigate both directions — see what groups a person is in, or see what people are in a group. If you're concerned about duplicate entries (where a person could be added to the same group multiple times), consider implementing a composite key in your junction table, which I cover in another TechHelp tutorial.
Finally, I organize the navigation by making sure the main Person List is at the top, followed by the Groups tab, and improve consistency in button labeling and behavior. As a last housekeeping step, I search and remove any stray references to the old Family-specific code, then perform a compile to verify that all VBA is working correctly.
Today's lesson was a bit longer and more advanced than usual, as I wanted to cover this process without breaking the flow. You asked for more involved developer content in the recent poll, so I wanted to make sure we tackled this topic comprehensively. If you need a break, remember you can always pause and return whenever you'd like.
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 Displaying groups for a selected person in the Persons form
Creating a Groups tab on the Persons form
Adding a subform to display a person's group memberships
Copying and modifying an existing continuous form for use as a subform
Changing the record source of a subform to use the junction table
Adding a combo box to display and select groups in the subform
Filtering the group list in the subform by group type
Creating a helper query for group types using the helper table
Adding a combo box to select group type and filter displayed groups
Setting a default value for the group type filter combo box
Building a new query to join the junction and group tables for filtering
Applying criteria in the query to filter by selected group type
Setting up form event code to refresh records after loading
Adding After Update event code to requery subform records when filtering
Modifying the group selection combo box to filter by group type
Adding event code to requery combo boxes when the filter changes
Testing filtering to ensure only relevant groups display per group type
Adding a button to switch between person and group list views
Searching and deleting old code references to specific group types
Ensuring that combo boxes and subforms update correctly with filtering |
