Solved

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

Posted on 2013-05-26
8
286 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
Comment Utility
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
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
Comment Utility
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:crownbrown
Comment Utility
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
Comment Utility
0
 
LVL 84

Accepted Solution

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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 …

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now