My suggestion would be to put all your search fields on the main form. Being a bit short of information, I am going to make the following assumptions:-
1) You have 2 Combo Boxes - CboMinistry & CboStatus that return a number MinistryID and StatusID and that these fields also exist in your subform.
2) You have 2 Text Boxes for Dates FromDate & ToDate and there is a field called MyDate in your subform.
3) There is a Text Box called TxtSearch on the form & there is a field called MyText in your subform.
OK this is air code to build up a filter after setting up as many or few search fields as you want. Obviously you will need to adapt it for your own use, and I stress it is air code
-
Private CmdSearch_Click
-
Dim Fltr as String
-
Dim MinistryFltr as String
-
Dim StatusFltr as String
-
Dim FromDateFltr as String
-
Dim ToDateFltr as String
-
Dim TextFltr as String
-
-
If Not IsNull(CboMinistry) Then
-
MinistryFltr = "MinistryID = " & CboMinistry
-
End If
-
-
If Not IsNull(CboStatus) Then
-
StatusFltr = "StatusID = " & CboStatus
-
End If
-
-
If Not IsNull(FromDate) Then
-
FromDateFltr = "MyDate >= #" & FromDate & "#"
-
End If
-
-
If Not IsNull(ToDate) Then
-
ToDateFltr = "MyDate <= #" & ToDate & "#"
-
End If
-
-
If Not IsNull(TxtSearch) Then
-
TextFltr = "MyText Like " & Chr$(34) & "*" & TxtSearch & "*" & Chr$(34)
-
End If
-
-
If Nz(MinistryFltr) > "" Then
-
If Nz(Fltr) > "" Then
-
Fltr = Fltr & " AND " & MinistryFltr
-
Else
-
Fltr = MinistryFltr
-
End If
-
End If
-
-
If Nz(StatusFltr) > "" Then
-
If Nz(Fltr) > "" Then
-
Fltr = Fltr & " AND " & StatusFltr
-
Else
-
Fltr = StatusFltr
-
End If
-
End If
-
-
If Nz(FromDateFltr) > "" Then
-
If Nz(Fltr) > "" Then
-
Fltr = Fltr & " AND " & FromDateFltr
-
Else
-
Fltr = FromDateFltr
-
End If
-
End If
-
-
If Nz(ToDateFltr) > "" Then
-
If Nz(Fltr) > "" Then
-
Fltr = Fltr & " AND " & ToDateFltr
-
Else
-
Fltr = ToDateFltr
-
End If
-
End If
-
-
If Nz(TextFltr) > "" Then
-
If Nz(Fltr) > "" Then
-
Fltr = Fltr & " AND " & TextFltr
-
Else
-
Fltr = TextFltr
-
End If
-
End If
-
-
If Fltr > "" then
-
Me.MySubformName.Form.Filter = Fltr
-
Me.MySubformName.Form.FilterOn = True
-
Else
-
Me.MySubformName.Form.Filter = False
-
End If
-
-
End Sub
-
So I am building a "Mini" Filter for each field and then combining them.
Phil