Filter on Subdatasheet

Question

In Access 2003, I have form with a a datasheet and an embedded subdatasheet. I would like to apply filters to both the main and sub datasheets. First, I build a filter string for the main datasheet strWhere, and one for the sub datasheet sdsWhere. Next, from the From object of the main datasheet, I set .filter=strWhere. Then, I set a filter on the subdatasheet's Form object. Basically:

With Me.Controls(dataSheetName).Form
                .Filter = strWhere
                .FilterOn = True
     With .Controls(subsheetname).Form
                .Filter = sdsWhere
                .FilterOn = True
     End with
End with

The problem is that the subdatasheet is only filtered for the first record of the main datasheet. Looking at the RecordSet of the sub datasheet, it only contains the child records of the first record in the main datasheet. I cannot find the other records anywhere.

I know that it should be possible to filter all subdatasheet records, because from the Access interface, placing the cursor in the desired value, and selecting Records>>Filter by Selection has the desired effect. (Except that records in the main form with have empty subdatasheets show, and I would like them to not show)

Answer 1

I'm an idiot - figured it out, of course just move the subdatasheet's Recodset position, and then apply the filter until eof. But we need to goto the first record, first otherwise it will miss the records before the record the user is currently positioned at. And make sure that subdatasheetexpanded is true.

Dim mainDS As Form, subDS As Form
Set mainDS = Me.Controls(dataSheetName).Form

If mainDS.SubdatasheetExpanded = False Then
    mainDS.SubdatasheetExpanded = True
End If

mainDS.Recordset.MoveFirst
Do While Not mainDS.Recordset.EOF
    Set subDS = mainDS.Controls(sdsheet).Form
    subDS.Filter = sdsWhere
    subDS.FilterOn = True
    mainDS.Recordset.MoveNext
Loop