Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Filter Master Record based on Filter from Child Subform record

Posted on 2012-08-29
Medium Priority
Last Modified: 2012-09-02
I have been racking my brain trying to solve this.  OK....

I have a "Main" form = Properties
and each property can have many Tenants  so Properties is a split form and Tenants is a subform.  
Properties record source is a query = PropertyQuery
Tenants record source is as follows

SELECT AnchorInfo.*, AnchorName & " " & Format([AnchorGLA],"- 0,000") AS AnchorCombo
FROM AnchorInfo;

AnchorInfo is a table containing
PropertyID - this is not a lookUp but is tied to the Properties table via ID

Everything works great...except...now I need to set a filter based on a selection in the subfom that will then filter the Master form.  i.e.

Master form Property = City Lake Center
Subform Anchors = Target
                                  Best Buy
                                   Bass Pro

I need to set a filter on Target so only the Properties containing Target appear.  I guess that would raise another issue because I wouldn't want to filter the subform of any other Anchors.  

Should I have a popup filter, Maybe just create a filtered report?  Not sure what to do here if setting a text filter of AnchorName woun't work.
Question by:napsternova
  • 3
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38346457
Not sure if this really makes sense.

The master record controls the subrecords...
Select a Customer in the main form, and only Orders for that customer will appear in the subform.

So I am not understanding why a "Order" (Child)  field would exist in the customer form/table to be filtered on...

So perhaps a sample database would clear things up

Expert Comment

ID: 38353858
Attached is a Database that shows you how to do this.  I have used buttons so that you can easily follow the code.  The Properties form pops us when you open the database.  It should be self expanatory  but the simple explanation is this:

In the subform, you put code that modifies the parent form's Filter and filterOn properties.  You build a SQL statement that retrieves the record of only the properties that have an Anchor store of the one you have selected and you place that query string in the Filter Property of the Parent Form.  To clear the Filter, you set the FilterOn Property of teh Parent form to False.

Hope that solves it.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38354537
Again, your logic here is invertedsed.
This is why what you are asking for is difficult to describe, and to do.

In a nutshell
The main form always controls the subform.
So if the PropertyID in the main form is 1, then only Anchor stores for Property1 will be listed in the subform.
With only AnchorStores for property1 listed in the subform, how would it be possible to "pick" a propertyID (in the subform) other than 1, to filter on?

Again, with a PropertyID of 1 in the main form, the subform will only display Child records for PopertyID1

Now, ...with all of this being said...
If your real goal here is to select an "AnchorStore" and only show main records for it, then this "Really" should be done from a separate form.
If you try to filter from "both ends", things will get confusing for the user.

What you are asking for can certainly be done, but it is not the standard way of designing a user interface.

LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38354643
So to do this right you would also need a "AnchorStore" junction table.
One AnchorStore can be in many Properties, and also, ...One property can have many AnchorStores

In your current design each Anchorstore is associated with only one property.
This opens the door for misspellings:
Home Depot
The Home Depot

This means you can't reliably "filter" for an anchorstore "name"

A junction table solves this by listing each anchorstore once, then each anchorstore can be assigned to multiple Properties.

Now, again, this may all sound more complex than it needs to be.
But the database design must be flexible enough to meet your need now and in the future.
If done in this fashion, what you are asking for is easy to do directly.


Author Closing Comment

ID: 38359437
What I ended up doing was creating a report with a Dialog Form and just filtered the results that way.  Then the end-user can select what Property they want to see in its entirety using a hyperlink to the Property Details Form.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

564 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