Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter subform on tab control? (2000 SP3)

    I have a tab control form with controls from the form's record source appearing on the first page (ClientInfo). On subsequent pages I have linked subforms, in Form View, planted on each page that contain data from other linked tables (Business)(Demographics)(CourseData) etc.

    Filter by Form and Filter by Selection works fine on the ClientInfo tab. But when running a filter on any of the other tabs, I always get the full number of records returned and no filter is applied.

    I tried Indexing the fields in the various tables but nothing seems to work. Is it possible to filter on a subform planted on a tab control?

    Thanks!
    Brady

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter subform on tab control? (2000 SP3)

    Subforms are normally filtered by their master/child links to the parent. You aren't filtering the tab, you're filtering the parent form, which automatically filters linked subforms. Perhaps if you explain what you're trying to accomplish, someone can help.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter subform on tab control? (2000 SP3)

    Charlotte,
    Thank you for the question. I think it made me realize what is going wrong, although I still need your help.

    For reporting purposes (to our funding sources) we must collect an enormous amount of data for each client we serve. Since the fields are so numerous, all demographic data is contained in a subform linked to the ClientID on the parent form on a separate page of the tab control. All business data is on another, etc.

    Late binding code was added to the tab control's On Change event to make each tab's subform visible or invisible, thus speeding up form loading and lighten server traffic.

    THIS MAY BE THE PROBLEM! Here's the code:

    Private Sub TabCtl0_Change()

    On Error GoTo HandleErr
    Select Case Me.TabCtl0.Pages.Item(Me.TabCtl0.Value).Name

    Case "&ClientInfo"
    Me.fsubClientDataCounselSub.Form.RecordSource = "qryClientDataCounselSub"
    Me.fsubClientDataCounselSub.Visible = True
    Me.frmClientDataCourseSub.Visible = False
    Me.frmClientDataBusinessSub.Visible = False
    Me.fsubClientDataDemographicSub.Visible = False
    Me.fsubClientDataKnowledgeSub.Visible = False

    Case "Co&urseData"
    Me.frmClientDataCourseSub.Form.RecordSource = "qryClientDataCourseSub"
    Me.frmClientDataCourseSub.Visible = True
    Me.fsubClientDataCounselSub.Visible = False
    Me.frmClientDataBusinessSub.Visible = False
    Me.fsubClientDataDemographicSub.Visible = False
    Me.fsubClientDataKnowledgeSub.Visible = False

    Case "&Business"
    Me.frmClientDataBusinessSub.Form.RecordSource = "qryClientDataBusinessSub"
    Me.frmClientDataBusinessSub.Visible = True
    Me.frmClientDataCourseSub.Visible = False
    Me.fsubClientDataCounselSub.Visible = False
    Me.fsubClientDataDemographicSub.Visible = False
    Me.fsubClientDataKnowledgeSub.Visible = False

    Case "&Demographics"
    Me.fsubClientDataDemographicSub.Form.RecordSource = "qryClientDataDemographicsSub"
    Me.fsubClientDataDemographicSub.Visible = True
    Me.frmClientDataBusinessSub.Visible = False
    Me.frmClientDataCourseSub.Visible = False
    Me.fsubClientDataCounselSub.Visible = False
    Me.fsubClientDataKnowledgeSub.Visible = False

    Case "&Knowledge"
    Me.fsubClientDataKnowledgeSub.Form.RecordSource = "qryClientIntakeEntryKnowledgeSub"
    Me.fsubClientDataKnowledgeSub.Visible = True
    Me.fsubClientDataDemographicSub.Visible = False
    Me.frmClientDataBusinessSub.Visible = False
    Me.frmClientDataCourseSub.Visible = False
    Me.fsubClientDataCounselSub.Visible = False

    End Select


    So, Charlotte, can a tab control form be filtered when using the above?

    Brady

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter subform on tab control? (2000 SP3)

    This code in itself should not influence the filtering. You haven't made it clear yet what you want to accomplish and how you want to filter.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter subform on tab control? (2000 SP3)

    Tab controls aren't filtered anyhow. Subforms that appear *on* tab pages can be filtered by the links to the parent form. Tab controls are just a method of displaying controls, subforms, etc, one group at a time without having to show/hide the rest of the controls or subforms. If the subforms are bound to the tab pages, then you don't need to do any setting of the visible properties of the subforms, the tab takes care of it by only exposing the one on the current page.

    I don't understand exactly why you're setting the recordsource of the subforms instead of populating the subform control with a sourceobject. You wouldn't really want to set the recordsource over and over every time you clicked on a tab, would you? The general approach is to put a subform on each tab (not on the parent form) and use the change event of the tab to set the sourceobject of that subform control the first time you click on that tab (when the length of the SourceObject property is zero). The master/child links handle filtering the specific records on the subreports down to whichever you have selected in the parent form. Are all these queries on the same table, by any chance? If not, congratulations, you grasped the relational concept.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter subform on tab control? (2000 SP3)

    I would like to those in our office to be able to use the Filter by Form or Filter by Selection buttons on the standard user interface to see, for instance, how many of our clients are Hispanic or how many were Vietnam War vets. "Hispanic" is one of the selections on a combo box appearing on the Demographics tab. "Vietnam War veteran" is a check box on the same tab.

    When I use Filter by Form or Filter by Selection on the first tab and filter by the name of a city, I get returned maybe 60 out of 240 records. But when I try to do the same thing on any of the tabs that display their data in a subform (the subform being planted on the tab in Form View, not a datasheet) I select "Hispanic" and get returned 240 records even though not all are Hispanic.

    Any idea what could be going wrong?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter subform on tab control? (2000 SP3)

    To repeat myself
    <hr>Tab controls aren't filtered anyhow. Subforms that appear *on* tab pages can be filtered by the links to the parent form. <hr>
    When you use the filter by form from any tab page, you are filtering the entire form, not the subform. You simply can't filter parent forms from the subform because the value you're looking for may not exist, so you get all the records instead. I realize you're trying to use some built in features, but you can't use them the way you're trying to. Filter by form means exactly that. It does not mean filter subform by form or filter form by subform, even though intuitively it seems like that should work, so you're going to need to find another method.

    Without knowing the relationship among the tables involved, I can't suggest a specific workaround, but basically what you need to do is capture the value they want to filter on and then apply that to the parent form's complete recordset, possibly with a subquery.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter subform on tab control? (2000 SP3)

    Charlotte,
    I sent my last message in reply to Hans message but it fell after yours. So in answer to your comments, I have been working with Access for several years on stand-alones but never designed anything for Client/Server use. The code I mentioned above was implemented to cut down on server traffic (supposedly, according to the book that I got the code from). The book said that by setting the recordsource only when a tab is clicked, Access doesn't need to load up as much when the form opens and since most of the work in the office only need info from the first tab, the database should run faster.

    Yes, all queries for each subform on the various tabs are based on different tables. Some are one-to-one (Demographics and Knowledge) and some are one-to-many (CourseData and Business) with the Clients table.

    So the question remains, any clue as to why Filter by Form and Filter by Selection don't work?
    Thanks,
    Brady

  9. #9
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter subform on tab control? (2000 SP3)

    Please don't reply to me too fast, as soon as I wrote you, you had already written again. The order of questions and answers is really making things confusing.
    Brady

  10. #10
    New Lounger
    Join Date
    Feb 2004
    Location
    Hsinchu, Taiwan
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter subform on tab control? (2000 SP3)

    Charlotte,

    SORRY to take so much of your time. I misunderstood you. I realize now that it won't work. I may need to build specfic forms for filtering, rather than using the main Client display form. THANK YOU for the help. As my emoticon shows, I have been beating my head against the wall for quite a while over this.

    Brady

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •