Running Multiple Filters (97)

Topic

New Reply

Hi folks,

I’ve inherited (groan!) a database from an employee that has just left our company and am struggling to get the filters to work.

It has a simple form, with displayed data on the right and two (soon to be more) combo boxes on the left. The combo boxes allow users to select citeria to filter by (specifically username and company).

Rather than have the combo boxes filter on their “after update” events, I would like a user to be able to select the criteria from the two boxes and then click an “Apply Filter” button which runs the filter based on the selections made.

I can easily do it for one combo box criteria using the “on click” button event to run the code DoCmd.ApplyFilter , “[Auto_ID] = Me!Combo25.Value”.

My question is, therefore, how can I stack up these filters,so that the filter runs on the values of two (or more) combo boxes? I guess its an SQL thing?

Also, I would like the multiple filter to accept values where a combo box hasn’t be used – i.e. user may want to specify filter criteria in CB’s 1, 3 and 4, but leaves 2 and 5 untouched??

Finally! How can I stop the whole form going blank when the filter doesn’t find any values?

Phew!

Thanks in advance,

Ade

Reply | Quote

Replies

This sample code is doing such selection to build the sql string for a report based on the different combo’s from a form named “frmprintselecties”.
With some adjustments you can use it for your application.

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String
Dim ysnCond As Boolean
ysnCond = False
If IsLoaded("frmprintselecties") Then
    If "" & Forms!frmprintselecties!cmbActiviteit  "" Then
         strSQL = "SELECT * from qryKlantActivAll WHERE IDActiviteit = " _
            & Forms!frmprintselecties!cmbActiviteit & " "
        ysnCond = True
   Else
        strSQL = "SELECT * from qryKlantAlfa "
        ysnCond = False
    End If
    If "" & Forms!frmprintselecties!cmbverteg  "" Then
        If ysnCond Then
            strSQL = strSQL & "AND "
        Else
            strSQL = strSQL & "WHERE "
        End If
        strSQL = strSQL & "lngvertegenw = " & _
             Forms!frmprintselecties!cmbverteg
        ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbtelefoon  "" Then
        If ysnCond Then
            strSQL = strSQL & "AND "
        Else
            strSQL = strSQL & " WHERE "
        End If
        strSQL = strSQL & "txtTelefoon LIKE " & Chr(34) & _
            Forms!frmprintselecties!cmbtelefoon & "*" & Chr(34)
        ysnCond = True
        
    End If
        If "" & Forms!frmprintselecties!cmbLandcode  "" Then
        If ysnCond Then
            strSQL = strSQL & "AND "
        Else
            strSQL = strSQL & " WHERE "
        End If
        strSQL = strSQL & "txtlandcode = " & _
            Chr(34) & Forms!frmprintselecties!cmbLandcode & Chr(34)
        ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbTaal  "" Then
        If ysnCond Then
            strSQL = strSQL & "AND "
        Else
            strSQL = strSQL & " WHERE "
        End If
        strSQL = strSQL & "txttaal = " & Chr(34) & _
             Forms!frmprintselecties!cmbTaal & Chr(34)
        ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbDocum = "Ja" Then
        If ysnCond Then
            strSQL = strSQL & "AND "
        Else
            strSQL = strSQL & " WHERE "
        End If
        strSQL = strSQL & "ysndocum = " & Forms!frmprintselecties!ysnDocum
        ysnCond = True
    End If
    
    Me.RecordSource = strSQL
Else
    MsgBox "This repport can only been " & _
      "printed when calling from Printing Selections Form", , "Error"
    Cancel = True
End If

End Sub

Succes

Reply | Quote