?
Solved

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

Posted on 2013-05-26
8
Medium Priority
?
360 Views
Last Modified: 2015-01-18
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. Thanks
Sample-School-Database.accdb
0
Comment
Question by:crownbrown
6 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39198216
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
 

Author Comment

by:crownbrown
ID: 39198223
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?

Thanks again!
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 39198224
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:crownbrown
ID: 39198253
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20927691.html

Any ideas would be appreciated, again, thanks for the tutorial!
LEFT-Join.accdb
0
 
LVL 17

Assisted Solution

by:Premkumar Yogeswaran
Premkumar Yogeswaran earned 500 total points
ID: 39199106
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39200781
I thought it was fairly clear when I wrote this:

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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question