Best Way to Sync Parent Form With SubForm Filtered Records

[Sedrick]

What would be the best way apply a filter to a subform and then apply that recordset to the parent form?

For example.
Parent Form - Groups: ID, GroupName
Subform - Members: MemID, GrpID, Name, Age

Forms linked with Groups.ID = Members.GrpID

Parent Form has Button - btn80 - "80 Year Olds"

Private Sub btn80()
' Create filtered recordset in subform
Me.subfrmMembers.Form.Filter = "Members.Age = 80"
Me.subfrmMembers.Form.FilterOn = True
 
 

'How could the corresponding recordset be created in the Parent Form?

'Any Way to Progressively Filter Out Records In Form's Recordset?

'Possibly:

Until Me.subfrmMembers.Form.RecordsetClone.EOF
... ' Somehow add to Me.Filter with an operation on the filtered recordset each time? I noticed if you just set the filter each time it drops the previous filter


Is there a way within the form to redefine related recordsets based on a filter?

I think I have done this before by setting up a query as the data source for the form with criteria in the query pointing to the value of a control on the form, but I was wondering if I could get around that and do it all in the form.

[RuralGuy]

Maybe this is the link you seek: http://allenbrowne.com/ser-28.html

[nico5038]

I see you found the form linked fields, but are puzzled by the requirement:
'How could the corresponding recordset be created in the Parent Form?'
and the "80-years" button puzzles me too.

In general I would use the linkage fields for linking the main form to the subform and instruct the user to use the right-click pop-up form. When right-clicking the Age field the user can type "80" in the filter field (but also "70") and Enter.
All other right-clicks will automatically be added wit the "AND" relation.

Now inspecting the Me.Filter will show the "WHERE" you can pass to e.g. a report to print just the selection.

Getting the idea ?

Nic;o)

[Sedrick]

RuralGuy, that's the one I was looking for. Thanks! I thought I had seen this before but couldn't place where to find it.

I ended up doing it with a query as the static RecordSource and having the criteria in the query check the value of a Control on the Form. Changing the RecordSource dynamically is better though.

[RuralGuy]

Excellent! Glad we could help.

[Sedrick]

nico5038,

You know, what you shared about right clicking on text fields in the form is something I may have known about before but have not used very much at all with Access forms. I use it alot in Windows Explorer, Excel and a few other apps but not that much in Access.

I can see now that there are many instances in dealing with form input that it could save me overall, a tremendous amount of time! In filtering and sorting I have usually gone to the menus at the top of the screen, but right clicking is much quicker and I think it is something that I will be using a lot more.

Thank you for suggesting this!