[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-05-26
8
Medium Priority
?
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

649 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