Subform isn't filtering properly

[Access_Novice]

Please see the attached Word document for screen shots. All information in the screen shots are purely fictional. Here is an explanation of the screen shots.

1. Figure 1 shows the relationship diagram. My form in Figure 4 is working only with the 1st and 3rd tables in the red box (Parents and Membership).
2. Figure 2 shows the structure of a query called qryMembership. The table called Parent is by coincidence the Parent table, Gymnast is the child table, and Membership is the "grandchild table."
3. Figure 3 shows the results of the query called qryMembership. Notice the last 2 rows in the red box. The same parent appears twice (Mildred Henry) because she has 2 kids (Matt and Kate).
4. Figure 4 is the form which consists of the main part (record source is the Parents table) and a subform whose record source is the query called qryMembership. This subform shows only 1 record (for Matt) instead of two records (Kate would be the second record) as seen in Figure 3.
5. Figure 5 shows the properties for the subform. I did set the Master and Child fields. I think this is done correctly based on the ParentID and Gymnast fields in the 1st and 3rd tables of Figure 2.

The problem I have is that when the main form gets to parentID 45 (which is the last record in the underlying table), the subform should show two records (Matt and Kate) but only the first record, Matt, is shown.

My question is, why isn't the 2nd record (Kate) showing? I believe it has something to do with the fact that the Membership table is a "grandchild" table instead of a child table. Is this correct?

[ItsMe]

What happens when you open the query that your subform is based upon and filter the field that you have as the child link to '45'?

[Access_Novice]

What happens when you open the query that your subform is based upon and filter the field that you have as the child link to '45'?

I opened that query and filtered the field called 'gymnast' to 45. Only one record shows. That is the record for the gymnast Matt Henry, with his parent Mildred Henry.

[ItsMe]

So, maybe you can work at the query level instead of the form. The problem seems to be with your join. Get the query to show what you need. Then decide how much of the functionality you want to save in the query and what functions you want to have the form mange. For example, you are using a filter in the query to see if filtering 45 works. When you get it to work in the query then you can decide if you want the filter function in a form, maybe as a Main vs Child link