Filter report with two criteria using combo boxes and button on form.

[CraigDouglas]

Please help. I have code that filters my report using the open event of the report but I want to have the code in the button on a form so I don't have to make many reports.
The combo boxes are called cboSubcategory and cboCustomer1 the button is called cmdOpenRptCustomerStockMain.

I have code in the open event which is similar just a few differences as I am doing a different report to test the code. I want to be able to see the results of the choice in the cboSubcategory, the cboCustomer1 and if selections of both are chosen together. Thank you for any help. Craig

Here is the code that is in the open event of the report

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
'Add filter

Dim frm As Form
Set frm = Forms!frmSwitchboard2 'Must Be Open
Dim strFilter As String
strFilter = ""
'If both are empty show everything
If Len("" & frm!cboCustomer) = 0 And Len("" & frm!cboProductSubcategory) = 0 Then
Me.filter = ""
Me.FilterOn = False
Exit Sub
End If
'Customer
If Len("" & frm!cboCustomer) > 0 Then
strFilter = "[CustomerName] = Forms!frmSwitchboard2!cboCustomer"
End If
'ProductSubcategory
If Len("" & frm!cboProductSubcategory) > 0 Then
If Len(strFilter) > 0 Then
strFilter = strFilter & " And "
End If
strFilter = strFilter & "[ProductSubcategory] = Forms!frmSwitchboard2!cboProductSubcategory"
End If
'Add filter
Me.filter = strFilter
Me.FilterOn = True

CleanUpAndExit:

Exit Sub

ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")

Resume CleanUpAndExit


End Sub

 

[arnelgp]

you passed cboProductSubcategory value as OpenArgs parameter when you open the report.
then on the open event of the report, check if the OpenArgs has been passed (not Null).
and filter your report.

 

[Gasman]

Why not just pass the filter in on the Docmd.Openreport?

 

[Ranman256]

another way:

Public Sub btnOpenRpt_Click()
Dim sSql As String, sWhere As String

sWhere = "1=1"

    '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


   'use it to filer the report
docmd.OpenReport "rMyReport",acViewPreview ,,sWhere 


   'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif

END SUB

 

[CraigDouglas]

another way:

Public Sub btnOpenRpt_Click()
Dim sSql As String, sWhere As String

sWhere = "1=1"

    '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


   'use it to filer the report
docmd.OpenReport "rMyReport",acViewPreview ,,sWhere


   'use it to filer the form records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif

END SUB

Thank you that seems to work. I don't know what I am doing but this is the code I used

Private Sub cmdOpenRptCustomerStockMain_Click()
Dim sSql As String, sWhere As String

sWhere = "1=1"

'the query is built depending on the various filters the user picks...
If Not IsNull(cboSubcategory) Then sWhere = sWhere & " and [ProductSubcategory]='" & cboSubcategory & "'"
If Not IsNull(cboCustomer1) Then sWhere = sWhere & " and [CustomerName]='" & cboCustomer1 & "'"
'If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value


'use it to filer the report
DoCmd.OpenReport "rptCustomerStockMain", acViewReport, , sWhere


'use it to filer the form records
If sWhere = "1=1" Then
Me.FilterOn = False
Else
Me.filter = sWhere
Me.FilterOn = True
End If




End Sub