Filter Master Record based on Filter from Child Subform record

Posted on 2012-08-29
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 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
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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
    LVL 2

    Expert Comment

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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    query error in access 2010 dao 3.6 5 27
    Oracle PL/SQL syntax 4 29
    Confirm a record has been added MS Access 4 17
    Order by 8 19
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now