Moving Main Form to specific record from Subfrom

Question

I have a main form with a subform. The main form is bound to a table, which has a primary key of ID1 and a foreign key of ID2.

The main form / subform relationship is set up on ID2, so the main form will show 1 record at a time, and then the subform will show all related records that match ID2 for the current record on the main form.

I'm trying to use a command button within the subform records so that I can move the main form to that particular record in the subform using the ID1 field:

Private Sub cmdLoadAssess_Click()

    Dim rs As DAO.Recordset
    Dim frm As Form

    Set frm = Me.Parent
    Set rs = frm.RecordsetClone

    rs.FindFirst "ID1=" & Me.ID1

    If _
        Not rs.NoMatch _
    Then        
        Debug.Print "Match found for " & Me.ID1
        frm.Bookmark = rs.Bookmark          
    Else        
        Debug.Print "No match found for " & Me.ID1      
    End If

End Sub

Unfortunately this seems to only be able to find a match on ID1 for the current record in the main form (which is pointless, since the main form is already there!)

I know these records exists (they're right there in subform), and those Debug.Print lines tell me ID1 is being passed from the subform. What am I missing here?

Edit: After doing a rs.MoveLast and then checking the cloned recordset's rs.RecordCount and rs!ID, it seems that only the current record on the main form has been cloned for the recordset rather than the whole table that the main form is bound to. Why is it not cloning the whole recordset of the main form?

I was just about to suggest rs.MoveLast and Debug.Print rs.RecordCount - are you sure the main form has the complete table as record source, not just one record? Can you manually navigate in the main form? — Andre
I think I've discovered my issue (on the same lines as what you've just asked), but I need to test it. When the form is opened, it opens to a specific record, and it seems to use the .Filter property of the form. So perhaps the recordset is essentially already filtered before I try to look for other records inside it. — Matt Hall
In that case you'd do frm.Filter = "ID1=" & Me.ID1 instead of using Bookmarks. — Andre
Do you mean just change frm.Filter and nothing else, i.e. don't use RecordsetClone? You couldn't expand that in answer could you? I only ask because just setting the frm.Filter doesn't seem to move the form to a record. — Matt Hall

Matt Hall Matt Hall · Accepted Answer · 2015-09-15T16:37:40

My form was opened to a specific record using the WhereCondition argument of the DoCmd.OpenForm method.

This causes the form's .Filter property to be set to whatever was put in that WhereCondition.

When RecordsetClone is used on a filtered form, it is cloned in its filtered state, hence trying to find any other records in that cloned recordset is fruitless.

Thanks to comments from @andre451 for helping me workaround this. As suggested, simply redefining the form's filter to match that of the selected subform record does the trick:

Private Sub cmdLoadAssess_Click()

    Dim frm As Form

    Set frm = Me.Parent

    frm.filter = "ID1=" & Me.ID1

    Set frm = Nothing

End Sub

Moving Main Form to specific record from Subfrom

2 Answers