Solved

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

Posted on 2013-05-26
8
327 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
8 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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 125 total points
ID: 39198224
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 125 total points
ID: 39199106
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

821 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