Filter report with two criteria using combo boxes and button on form. (1 Viewer)

CraigDouglas

Registered User.
Local time
Today, 02:29
Joined
Sep 14, 2016
Messages
31
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:29
Joined
May 7, 2009
Messages
19,252
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

Enthusiastic Amateur
Local time
Today, 02:29
Joined
Sep 21, 2011
Messages
14,665
Why not just pass the filter in on the Docmd.Openreport?
 

Ranman256

Well-known member
Local time
Yesterday, 21:29
Joined
Apr 9, 2015
Messages
4,338
another way:

Code:
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

Registered User.
Local time
Today, 02:29
Joined
Sep 14, 2016
Messages
31
another way:

Code:
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
 

Users who are viewing this thread

Top Bottom