How to filter multiple fields in a report using comboboxes in a form (1 Viewer)

[cyrianagujo]

Hi guys, here's my problem. I have a report based on a query that I put inside a form. What I want to do is create 4 comboboxes from which users can choose options that will filter the report without leaving the form or opening the report in a separate window. Also, I want to make the filters dependent on each other, meaning if a select option A on combobox 1, the options i will select on combobox 2 will only be based on the earlier filter on combobox 1 (sorry if I can't explain it clearly.)

So far I'm able to do at least 2 of the comboboxes to filter the report, but they aren't based on which one was used to filter the report first. So if i filter the records by choosing option A on combobox 1, combobox 2 will still filter every record.

I'm really a beginner in VBA (almost 0 knowledge) but here's my 2 codes so far:

=======
Private Sub cboLocation_AfterUpdate()
On Error GoTo Proc_Error
If IsNull(Me.cboLocation) Then
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery
Else
Me.qrySalesByLocation.Report.Filter = "[Location]=" & Me.cboLocation
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit

End Sub

==========
Private Sub cboProduct_AfterUpdate()

On Error GoTo Proc_Error
If IsNull(Me.cboProduct) Then
Me.qrySalesByLocation.Report
Me.qrySalesByLocation.Report.Filter = ""
Me.qrySalesByLocation.Report.FilterOn = False
Me.qrySalesByLocation.Report.Requery

Else
Me.qrySalesByLocation.Report.Filter = "[Product]=" & Me.cboProduct
Me.qrySalesByLocation.Report.FilterOn = True
End If
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in setting subform filter:" & vbCrLf & Err.Description
Resume Proc_Exit

End Sub
=====

the other 2 comboboxes, I can't apply the same code cause the enter parameter dialog keeps popping out.

Thanks in advance for the help!

 

[Ranman256]

If you need complex form filters THEN make a query from it, do this:

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

    'remove 1st 'AND'
sWhere = Mid(sWhere, 4)

    'BUILD the query from the 'where'
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany"
If sWhere <> "" Then qdf.Sql = qdf.Sql & " WHERE " & sWhere
qdf.Close

	'open the query or report here!
'DOCMD.openquery kQRY
'DOCMD.OPENREPORT "rMyReport"

SET qdf = nothing
End Sub

 

[cyrianagujo]

If you need complex form filters THEN make a query from it, do this:

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

    'remove 1st 'AND'
sWhere = Mid(sWhere, 4)

    'BUILD the query from the 'where'
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany"
If sWhere <> "" Then qdf.Sql = qdf.Sql & " WHERE " & sWhere
qdf.Close

	'open the query or report here!
'DOCMD.openquery kQRY
'DOCMD.OPENREPORT "rMyReport"

SET qdf = nothing
End Sub

I'd really like to try this out, but I'm having a hard time understanding it as I barely know a thing about VBA

 

[cyrianagujo]

The process I want is like this:

1. I open the form. It displays the report showing all the records.
2. In the form header, there will be 4 combo boxes that will allow the user to filter the records.
3. Then, after one (or multiple) selections from the combo boxes is (are) made, the report is sorted without leaving the form or opening in a new window.

Sorry if it's a little convoluted