Filtering Main Form Records Through "Filter By Form" on Subform

I am desperate to find an answer, I have spend 10+ hours trying to figure this out. I have attached a sample database.

I have a table "Students" that lists student names and ID numbers. I have a another table "Classes" that lists a student to the classes he/she is taken. So, for example (in attached example database), a student named Billy with ID 123456 is taking several classes (each a unique record in the "Classes" table linked by the student ID).

These tabled are directly related 1:1 using the student ID.

I then have a main form "Students" which has a subform "Classes". So if you go to Billy's main form you see his 3-4 classes listed out in the subform.

I am building this database for beginning users, so I need them to be able to hit "Filter By Form" and type in "Math" in the Classes subform header box and have the main student records filtered or constrained whether or not they have "math" in the Classes subform.

My hope is there is some behind the scene work I can do so that "Filter By Form" will allow the main form records to be filtered and constrained by subform results.

Again, I am desperate to figure this out, have read through 2-3 dozen long forum threads trying to figure this out with no luck. I will be watching my email around the clock so I can respond or clarify anything that didn't make sense and shower gratitude on anyone who is willing to help. ThanksSample-School-Database.accdb

Who is Participating?

You can't use the builtin Filter By Form capabilities to do this. You'd have to build your own form to do this.

The other question you linked to did use a Left Join, but they ended up writing custom code in the OnFilter event to "backflush" the filtering up to the main form. The Left Join has nothing to do with the eventual solution ...

In essence, you're trying to reverse the functionality of the mainform/subform relationships. A Subform's records are dependent on the Mainform's selected record.

If you want the users to be able to search for all Students in a specific Class, then build a form that's based on the Class table, and then build another form that's based on the Students table and embed that into the Class form. Then, when the user navigates to the Math class, for example, they'll see all students in that class.

However, as both Cap and I have suggested, you should get your table structure right before you move along to user interface considerations. Filtering data for the user interface should be way, way down the list at this point.

You can't use the builtin Filter By Form capabilities to do this. You'd have to build your own form to do this.

I'd also suggest that you review your table structure - it would seem that you need a table for Classes:

tClasses
--------------
ClassID
ClassName

You'd then have another table to "join" the Student with a Class:

tClass_Student
----------------------
ClassID
StudentID
etc etc

0

crownbrownAuthor Commented: 2013-05-26

I really appreciate the response, and I see what you are saying about the table structures and relationships (so I'd have three tables: one with student data, one with class data, and a third that says which student is in which class). I apologize for being dense, but I'm not connecting the dots between how setting up the table this way (while certain a more "normal" setup) would help with the filtering question.

Again, I really appreciate the response, if I massaged my database into the format you suggest how could I accomplish something similar to my goal?

Capricorn1, I appreciate the tutorial, I watched it all the way through. I understand that piece of normalizing the database, and I can migrate my database to that setup and will do so soon.

What I am still not understanding is how to do the filtering by subform piece. So even if I had a course table and a student table linked by an enrollment table (the video on spot on with that) I want to be able to hit "Filter by Form" and type in "Math" in the column header on the subform and be shown only main student records that contain Math in the subform.

It seems to me like some people have said they have had success achieving this end by using LEFT JOIN in the recordsource of the main form, but I tried recreating this person's entire sample database and using their code and it didn't work for me: