acCmdFilterBySelection With Main And Subforms 1

[dominicdunmow]

Hi there,

I have a main form linked to a subform by a Unique ID field.

I have a command button on the main form that uses "acCmdFilterBySelection" to filter by the selected field.

When this button is clicked I'd like the subform to be filtered by this same field.

Does anyone know how I can do this with VBA?

Thanks

 

[Remou]

Hi
Perhaps something like:

Dim ctl As Control

    Set ctl = Screen.PreviousControl
    ctl.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection
    
    Select Case ctl.Value
    Case IsDate(ctl.Value)
        Me.[i][Subform Control Name][/i].Form.Filter = ctl.Name & "=#" & ctl.Value & "#"
    Case IsNumeric(ctl.Value)
        Me.[i][Subform Control Name][/i].Form.Filter = ctl.Name & "=" & ctl.Value
    Case Else
        Me.[i][Subform Control Name][/i].Form.Filter = ctl.Name & "='" & ctl.Value & "'"
    End Select
    Me.[i][Subform Control Name][/i].Form.FilterOn = True

 

[dominicdunmow]

Hi there,

Thanks for you response, it's great and has me on my way works, but I have one further wish.

The code works if FilterBySelection is used once...if I want to further filter the mainform using the FilterBySelection button a second time, the subform recordset resets, then filters by the last FilterBySelection field rather than both.

Do you know how the code can be adapted to fit in with this? - I've tried fiddling but haven't got anywhere.

Thanks for your help.

 

[Remou]

Use And:
strCurrentFilter=Me.[Subform Control Name].Form.Filter
...
Me.[Subform Control Name].Form.Filter = strCurrentFilter & " AND " & ctl.Name ...

You will probably need a clear or show all too.