CODEWITHSUNDEEP
ms-accessvba
Rajesh Gangwar
Rajesh Gangwar

Reputation: 11

Multiple Filter for Subfom in MS Access

I have a main form and a tabular sub form in it. I am applying multiple filters to filter my main form but my subform is linked with parent and child field property so I am able to apply filter on one field only.

How can I apply the same filters in my subform to what I have in my main form?

Below is my code to help you to understand:

Private Sub Filtermainform()
    Dim strWhere As String

    'Make string
    If Nz(Me.Combo56, "") <> "" Then
        If IsNumeric(Me.Combo56) Then
        strWhere = strWhere & "[" & Me.Combo54 & "] = " & Me.Combo56 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo54 & "] = '" & Me.Combo56 & "' AND "
        End If
    End If
    If Nz(Me.Combo109, "") <> "" Then
        If IsNumeric(Me.Combo109) Then
        strWhere = strWhere & "[" & Me.Combo107 & "] = " & Me.Combo109 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo107 & "] = '" & Me.Combo109 & "' AND "
        End If
    End If
    If Nz(Me.Combo112, "") <> "" Then
        If IsNumeric(Me.Combo112) Then
        strWhere = strWhere & "[" & Me.Combo111 & "] = " & Me.Combo112 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo111 & "] = '" & Me.Combo112 & "' AND "
        End If
    End If
    If Nz(Me.Combo114, "") <> "" Then
        If IsNumeric(Me.Combo114) Then
        strWhere = strWhere & "[" & Me.Combo113 & "] = " & Me.Combo114 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo113 & "] = '" & Me.Combo114 & "' AND "
        End If
    End If
    If Nz(Me.Combo116, "") <> "" Then
        If IsNumeric(Me.Combo116) Then
        strWhere = strWhere & "[" & Me.Combo115 & "] = " & Me.Combo116 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo115 & "] = '" & Me.Combo116 & "' AND "
        End If
    End If
    If Nz(Me.Combo118, "") <> "" Then
        If IsNumeric(Me.Combo118) Then
        strWhere = strWhere & "[" & Me.Combo117 & "] = " & Me.Combo118 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo117 & "] = '" & Me.Combo118 & "' AND "
        End If
    End If
    If Nz(Me.Combo120, "") <> "" Then
        If IsNumeric(Me.Combo120) Then
        strWhere = strWhere & "[" & Me.Combo119 & "] = " & Me.Combo120 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo119 & "] = '" & Me.Combo120 & "' AND "
        End If
    End If
    If Nz(Me.Combo122, "") <> "" Then
        If IsNumeric(Me.Combo122) Then
        strWhere = strWhere & "[" & Me.Combo121 & "] = " & Me.Combo122 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo121 & "] = '" & Me.Combo122 & "' AND "
        End If
    End If
    If Nz(Me.Combo124, "") <> "" Then
        If IsNumeric(Me.Combo124) Then
        strWhere = strWhere & "[" & Me.Combo123 & "] = " & Me.Combo124 & " AND "
        Else
        strWhere = strWhere & "[" & Me.Combo123 & "] = '" & Me.Combo124 & "' AND "
        End If
    End If

    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.Filter = strWhere
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If

Upvotes: 1

Views: 209

Answers (1)

Gustav
Gustav

Reputation: 56016

You can have multiple fields in the MasterField and ChildField specification, like:

[Id];[FilterField]
[FK];[FilterField]

To remove the filter, double the first field, as you cannot modify either specification to have another count of fields than the other(!), thus:

[Id];[Id]
[FK];[FK]

Upvotes: 1

Related Questions