Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Unanswered: is it possible to filter a subform based on what is selected on a different subform?

    Hi there...

    I have a form called Clients. This is the MAIN FORM

    then i have a whole bunch o' sub forms.

    here is the question-
    i have a continuous subform called Outgoing
    and another subform called LenderInteractions

    What i would LIKE to have happen is that when the user highlights/selects/etc a record in Outgoing, that only the records which relate (field is called LoanName) show up in LenderInteractions.

    I think (although it is very likely i am wrong)... i am going to use On Got Focus... uh... now what?

    Is this even possible? Am i on crack? Wait, don't answer that,,,, HEEEEEEEEEEEEEELP

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, it most certainly is possible.

    You could set the recordsource of the LenderInteractions subform to include a criteria that refers to the Outgoing subform during the On Current event of the Outgoing subform.

    Hope that makes sense
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54
    hmmmmm

    care to give an example? *grins*

    Rachel

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not right now, too pressed for time unfortunately. I hate it when I am too busy... work coming out of my ears.

    If I get chance I'll post up an example later, but I don't like my chances of finding that "chance".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's a couple of different ways you can do it.

    As ST mentioned, in the recordsource of your subform, you can set the recordsource in the OnCurrent event of MySubForm1...
    ie..
    Forms!MyMainFormName!MySubForm2.recordsource = "Select * from MySub2Table where IDField = " & Forms!MyMainFormName!MySubForm1!IDField & ""

    Note: You can also set it to a query name versus a "Select...." statement.
    ie..
    Forms!MyMainFormName!MySubForm2.recordsource = "qrySubForm1"

    Your query would have criteria in it such as under the IDfield (ie. column) which matches the field on the other subform (you'll need a field to relate the 2).
    ie.
    =Forms!MyMainFormName!MySubForm1!IDField

    The key is that you'll need to have a "matching value" field in both MySubForm1 and MySubform2 (ie. your relational field that relates both subform tables together.)

    You can also just edit the recordsource of MySubForm2 (in design view) and put in the criteria above (=Forms!MyMainFormName!MySubForm1!IDField) (if it's always the case). And if needed, simply issue a Forms!MyMainForm!MySubForm2.requery command in the OnCurrent event of MySubform1 (or on a button, etc..). I would do this if it "Always" needed to corollate.

    Sometimes I will have a subform and combobox and depending on the value in the combobox, I'll set the recordsource for the 2nd subform.
    ie.
    In the Afterupdate event of Combobox1
    select case me!cboColorSelect
    case 1
    Forms!MyMainForm!MySubForm2.recordsource = "qryshowGreenrecords"
    case 2
    Forms!MyMainForm!MySubForm2.recordsource = "qryshowBluerecords"
    case 3
    Forms!MyMainForm!MySubForm2.recordsource = "qryshowRedrecords"
    End Select

    There are a few other possibilities but you'll need to ellaborate further on how you'd like to do it (ie. all the time, after certain events, etc...)
    Last edited by pkstormy; 07-01-09 at 22:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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