How to Filter Access Subforms based on a control from a Main Form

If you are reading this then you have probably noticed that filtering a Subform based on a control from the Main form is a pain in the neck.

Here's one way to filter the subform by changing the underlying form.

Sub frameTaskStatus_Click()
Dim intStat As Integer
intStat = frameTaskStatus.Value
' 1 = all
' -1= closed
' 0 = open
' 2 = new open frmCreateNewRequest

Select Case intStat
Case -1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 0
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsFiltered"
Case 1
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsAll"
Case 2
Me!sbfrmMainRequests.SourceObject = "subfrmRequestsNew"
End Select

End Sub


Cases -1 and 0 (closed and open, respectively) make subfrmRequestsFiltered the underlying subform. This subform's datasource is a query that applies a filter also based on the value selected in frameTaskStatus.

Case 1 makes subfrmRequestAll the underlying subform in the Main form. subfrmRequestAll is also based on a query but it is not filtered.

Case 2 makes subfrmRequestsNew the underlying subform in the Main form. subfrmRequestsNew is a data entry only form.