Results 1 to 7 of 7
  1. #1
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317

    Parent and child subforms: filtering records

    I have a main form ('MainForm') containing two subforms, both continuous forms, positioned alongside each other ('Subform1' and 'Subform2'). MainForm is unbound, while Subform1 acts as a surrogate parent to Subform2's child.



    In this scenario (taken on its own terms, please!), what's the fastest way to filter the records in Subform2 when Subform1 moves to a different record? I can think of three possibilities, which are, roughly:

    1. Add a textbox to MainForm, set its control source to '= Subform1.ID', link Subform2.Subform1ID to this field as child to parent, and make MainForm recalculate every time Subform1 moves record.

    2. Set Subform2's filter property to '= Subform2.Subform1ID = Forms.MainForm.Subform1.Form.ID' (or whatever the correct syntax would be), and make Subform2 recalculate every time Subform1 moves record.

    3. Use 'Forms.Mainform.Subform1.Form.ID' (ditto on the syntax) in the criteria for the Subform1ID field in Subform2's record source query, and make Subform2 requery every time Subform1 moves record.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't say which might be fastest, but if it were me I'd be setting the recordsource or filter properties of the second subform from the current event of the first. Generically

    ReferenceHere.Recordsource = "SELECT...WHERE KeyField = " & Me.Keyfield

    Here's a reference on the syntax:

    http://www.theaccessweb.com/forms/frm0031.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Ha! I've had some unexpected time today to try out these ideas and logged in to report my findings, only to see that you'd already suggested a further option.

    Here's what I've found:


    1. It's Subform2 that needs to recalculate (not MainForm).


    2. This goes in Subform1's On Current event:
    Code:
    Me.Parent.Subform2.Form.Filter = "[Subform1ID] = " & Me.ID.Value
    Me.Parent.Subform2.Form.FilterOn = True
    Me.Parent.Subform2.Form.Recalc    'Refreshes Subform2 when the mouse button is pressed, rather than when it's released

    3. The first line goes in Subform2's record source query, while the other two go in Subform1's On Current event:
    Code:
    WHERE [Table2].[Table1ID] = [Forms].[MainForm].[Subform1].[Form]![ID]
    Code:
    Me.Parent!Subform2.Form.Requery
    Me.Parent!Subform2.Form.Recalc

    Option 1 is painfully slow. I'm surprised, because it's the only suggestion I could find online. Options 2 and 3 are about the same in speed. I've found that this also works:


    4. Bind MainForm to the same query as Subform1 and place this in Subform1's On Current event:
    Code:
    Set RS = Me.Parent.Recordset.Clone    'RS is my recordset variable
    RS.FindFirst "[ID] = " & Me.ID.Value
    If Not RS.EOF Then Me.Parent.Bookmark = RS.Bookmark
    Me.Parent.Subform2.Form.Recalc

    And finally, this works just as well:


    5. In Subform1's On Current event:
    Code:
    Me.Parent.Subform2.Form.Recordsource = "SELECT...WHERE [Table2].[Table1ID] = " & Me.ID.Value

    For all I know, options 2, 3 and 5 might amount to the same thing (I hadn't appreciated that filters are equivalent to WHERE clauses). Is there anything to recommend one of them over the others, or anything to recommend option 4 over all three?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Filter vs RecordSource: there are those that will always use Filter. My untested gut feeling is that using the filter means all the records have to be pulled, then filtered. Setting the RecordSource should mean that only applicable records are pulled, thus more efficient. That said, if the usage is such that all records may be accessed anyway, then the filter or bookmark methods may work better for you. The bookmark method will leave all records available for the user to navigate to in subform 2, the filter and RecordSource methods will not.

    I don't think I've ever used recalc. In the places you've got it I don't think it does anything. That said I'm just on a mobile and too lazy to research it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Thank you, that's very helpful.

    Recalc does achieve something, but it's to do with conditional formatting and hence not particularly interesting. What I said about it in my last post turns out to be a red herring.

  6. #6
    Remster is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2010
    Posts
    317
    Postscript: Easily the simplest way to do this turns out to be to type "[Subform1].Form![ID]" in the Link Master Fields field in the property sheet of Subform2's subform control ("[Subform1ID]" goes in the Link Child Fields field), and make Subform2 requery every time Subform1 moves record. I've no idea why I didn't think of this before.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Remster View Post
    I've no idea why I didn't think of this before.
    You and me both. Good thinking, glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-21-2018, 03:53 PM
  2. Replies: 11
    Last Post: 06-23-2017, 10:25 AM
  3. Parent Child records in reporting
    By Darlene in forum Reports
    Replies: 1
    Last Post: 03-22-2014, 06:49 PM
  4. Parent / 2 child subforms Question
    By jschlapi in forum Forms
    Replies: 1
    Last Post: 04-26-2012, 08:48 PM
  5. Delete child records with parent record
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 09-07-2010, 08:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums