MS Access - apply filter to report, using combo boxes but can't apply a manual filter (1 Viewer)

M

meanie

New member
Local time
Today, 09:22
Joined
Feb 13, 2017
Messages
5
Hello! first time long time.

Anywho, I have a Report section, taken and modified from the Northwind sample. It uses comboboxes and option buttons to apply a filter to a subform, and then pushes the filter to the report. That all works fine.

However, in the subform, when you click on a field and use the common filter options (clicking the filter button in the Access navigation menu, or right clicking in the field and selecting a filter option from the menu), it won't apply that filter alongside the coded filters when pushing to a report. I'm sure it's as simple as adding a line to include any current filter, but I'm a novice at code and have been stumped trying to find an answer online.

I have pasted the code below. I have tried a few variations of adding Me.Filter and Me.FilterOn = True, etc... to no success. Any help would be greatly appreciated:

Code: 
Private Function StockSearch()
On Error GoTo Error_StockSearch

Dim FilterClause As String, D As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings
Dim strDateField As String
strDateField = "[pay run]" 'Put your field name in the square brackets in these quotes

'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value

'Filter by Option Group Selected

If Me.PriorityGrp.Value = 5 Then
    If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    FilterClause = FilterClause & "[SumRequested] Is Not Null"
End If
If Me.PriorityGrp.Value = 6 Then
    If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
    FilterClause = FilterClause & "[SumRequested] Is Null"
End If

'1st Combo - Fiscal Year Text DataType
If Nz(Me.cboFY.Column(0), 0) > 0 Then
  If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  FilterClause = FilterClause & "[Fiscal Year]='" & Me.cboFY.Value & "'"
End If

'2nd Combo - REGION Text DataType
If Nz(Me.cboRegion.Column(0), 0) > 0 Then
  If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  FilterClause = FilterClause & "[Region]='" & Me.cboRegion.Value & "'"
End If

'3rd Combo - DIR Text DataType
If Nz(Me.cboDIR.Column(0), 0) > 0 Then
  If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  FilterClause = FilterClause & "[DIR]='" & Me.cboDIR.Value & "'"
End If

'4th Combo - BRANCH Text DataType
If Nz(Me.cboBranch.Column(0), 0) > 0 Then
  If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
  FilterClause = FilterClause & "[Branch]='" & Me.cboBranch.Value & "'"
End If

'Fill this Form wide variable so that it can be used for the Report.
CurrentFilter = FilterClause: FilterClause = ""


'Place our created Filter Criteria into the Filter property of SubForm.
[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter
'Turn on the Filter
[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = True

Exit_StockSearch:
Exit Function

Error_StockSearch:
MsgBox "StockSearch Function Error" & vbCr & vbCr & _
      Err.Number & " - " & Err.Description, vbExclamation, _
      "Stock Search Error"
Resume Exit_StockSearch
End Function

Private Sub StockReportBut_Click()
On Error GoTo Err_StockReport_Click

DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, , CurrentFilter

Exit_StockReport_Click:
Exit Sub

Err_StockReport_Click:
MsgBox Err.Description
Resume Exit_StockReport_Click
End Sub
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
I just played with this a bit. I put a command button on a form with the code

Code: 
MsgBox Me.Filter

just so I could see what it contained When I right click on a field and filter it, e.g., I right click on ID and select "Equals 5", and then click this command button I get

[tbl.Periods].[ID] = 5

This tells me that the filtering done in this manner is contain in the Filter property of the form. So I suggest trying sending that to the filter of the report.
 
T

Tfa

Registered User.
Local time
Today, 19:22
Joined
Nov 29, 2016
Messages
32
do an
msgbox CurrentFilter
and tell me the message
propably the problem is that before you sent teh filter you must replace a part of that string to match the one to the new report
 
M

meanie

New member
Local time
Today, 09:22
Joined
Feb 13, 2017
Messages
5
The msgbox displays whatever filter I applied through the combo box, but does not include the common filter.

For example, I chose a fiscal year and a DIR, which applied the filter. Then, in the subform, I right clicked in the name field, and selected a name to further the filter. When pushing to a report, the msg box is
[Fiscal Year]='2016-17' AND [DIR]='IBMS'
Click to expand...

But does not include any filter for [FullName] which was also filtered (common filter).

Hope that was clear. Thanks.
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
Would you try concatenating the existing filter content to whatever filter you applied through the combo box by changing this line
Code: 
[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter

to

Code: 
[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter & CurrentFilter

and let us know if that helps.
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
And if the suggestion in the previous post doesn't work please try adding an "And" to the concatenation like:

Code: 
[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter & " And " & CurrentFilter
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
Also I guess this code would need to include the forms filter rather than CurrentFilter, i.e.,

Code: 
Private Sub StockReportBut_Click()
On Error GoTo Err_StockReport_Click

DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, , [COLOR="Blue"][Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter
[/COLOR]
Exit_StockReport_Click:
Exit Sub

Err_StockReport_Click:
MsgBox Err.Description
Resume Exit_StockReport_Click
End Sub
 
M

meanie

New member
Local time
Today, 09:22
Joined
Feb 13, 2017
Messages
5
Thanks for the replies.

Progress so far:
- using "Form.Filter & CurrentFilter", I get error 3075, snytax error (missing operator)
- using "Form.Filter & " And " & CurrentFilter" causes the filter to break - when choosing an item from a combo box, the results in the subform are null
- adding "[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter" to the preview report button also shows null results (#Type!), the msg box for the filter displays what I chose in the combo box. A common filter was not applied as there were no results
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
The attached simple database demonstrates how filters can be combined. The command button code that applies the filtering from the combo concatenates the formed filter with the filter content unless the filter is empty in which case the formed filter is directly assigned like:

Code: 
Private Sub FilterCombo_Click()

Dim CurrentFilter As String
CurrentFilter = "[ID] < " & Me.cboID
If Me.Filter & vbNullString = vbNullString Then
    Me.Filter = CurrentFilter
Else
    Me.Filter = Me.Filter & " And " & CurrentFilter
End If
Me.FilterOn = True


End Sub

When the common filter is used the same thing happens. You can see this by clicking the Show Filter button.

The report is open with the form's filter past in the WHERE argument like:

Code: 
Private Sub OpenReport_Click()
On Error GoTo OpenReport_Click_Err

DoCmd.OpenReport "Report", acViewPreview, , Me.Filter


OpenReport_Click_Exit:
    Exit Sub

OpenReport_Click_Err:
    MsgBox Error$
    Resume OpenReport_Click_Exit

End Sub

This works in this case as the form and report have the same record source.

Hope this demo helps.
 

Attachments

  • CommonFilterTest.accdb
    444 KB · Views: 248
M

meanie

New member
Local time
Today, 09:22
Joined
Feb 13, 2017
Messages
5
Thanks, sneuberg.

So in testing yours, everything works like how I want it to, even when I switch it from a command_click to the 'StockSearch()' function in the after update of the combo box.

So again, I'm a noob with coding. I can generally figure it out, but I don't know all the rules. Here's my entire code for the form (excuse all the ' I've been experimenting a bunch):
Code: 
Option Compare Database
Option Explicit

Dim CurrentFilter As String

Private Sub cboDIR_AfterUpdate()
   On Error Resume Next
   cboBranch.RowSource = "Select Branch.Branch " & _
            "FROM Branch " & _
            "WHERE Branch.Dir = '" & cboDIR.Value & "' " & _
            "ORDER BY Branch.Branch;"
End Sub

Private Sub Form_Load()
   Dim Ctrl As Control

   For Each Ctrl In Me.Controls
      If Ctrl.ControlType = acComboBox Then Ctrl = Null
      If Ctrl.ControlType = acTextBox Then Ctrl = Null
   Next Ctrl
   
   CurrentFilter = ""
   
   'Rermove and Filter Criteria From the SubForm
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = ""
   'Turn OFF the Filter
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = False
   
   
End Sub

Private Sub RemoveFiltersBut_Click()
   Dim Ctrl As Control

   For Each Ctrl In Me.Controls
      If Ctrl.ControlType = acComboBox Then Ctrl = Null
      If Ctrl.ControlType = acTextBox Then Ctrl = Null
   Next Ctrl
   
   CurrentFilter = ""
   Me.Filter = ""

   
   'Rermove and Filter Criteria From the SubForm
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = ""
   'Turn OFF the Filter
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = False

End Sub


Private Function StockSearch()
   On Error GoTo Error_StockSearch
   
   Dim FilterClause As String, D As Long
   Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
   Dim strDateField As String
   strDateField = "[pay run]" 'Put your field name in the square brackets in these quotes.

   
   'Hold whether we Use AND or OR in our Filter Criteria
   D = Me.DirectionGrp.Value
   
   
   'Filter by Option Group Selected

   If Me.PriorityGrp.Value = 5 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[SumRequested] Is Not Null"
   End If
   If Me.PriorityGrp.Value = 6 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[SumRequested] Is Null"
   End If
   
   '1st Combo - Fiscal Year Text DataType
   If Nz(Me.cboFY.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Fiscal Year]='" & Me.cboFY.Value & "'"
   End If
    
   '2nd Combo - REGION Text DataType
   If Nz(Me.cboRegion.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Region]='" & Me.cboRegion.Value & "'"
   End If
   
   '3rd Combo - DIR Text DataType
   If Nz(Me.cboDIR.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[DIR]='" & Me.cboDIR.Value & "'"
   End If
   
   '4th Combo - BRANCH Text DataType
   If Nz(Me.cboBranch.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Branch]='" & Me.cboBranch.Value & "'"
   End If

   'Fill this Form wide variable so that it can be used for
   'the Report.
   CurrentFilter = FilterClause: FilterClause = ""
    'CurrentFilter = FilterClause
   
   'Place our created Filter Criteria into the Filter property of SubForm.
   '[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter
   'Turn on the Filter
   '[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = True
   
   If Me.Filter & vbNullString = vbNullString Then
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter
    Else
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = Me.Filter & " And " & CurrentFilter
    End If
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = True
   
Exit_StockSearch:
   Exit Function
   
Error_StockSearch:
   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Stock Search Error"
   Resume Exit_StockSearch
End Function

Private Sub StockReportBut_Click()
   On Error GoTo Err_StockReport_Click

   DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, , Me.Filter & " And " & CurrentFilter
   MsgBox Me.Filter & " And " & CurrentFilter


Exit_StockReport_Click:
   Exit Sub

Err_StockReport_Click:
   MsgBox Err.Description
   Resume Exit_StockReport_Click
End Sub

Private Function ClearCtrl(Ctrl As Control)
   Ctrl = Null
   Call StockSearch
End Function

But I'm not getting the results, and the filter has issues. Leads me to believe it's something to do with either the coding for each combo box, the "FilterClause", or Me.Filter. Here's what I've tested/noticed:

  • If I just apply one filter through a combo box, let's say [DIR]='IBMS', a msg box for CurrentFilter will display exactly that, if I add in Me.Filter to the msgbox, it will display IBMS 3 times! ([DIR]'IBMS' And [DIR]='IBMS' And [DIR]='IBMS'... If I change a combo box to another DIR, the first two will still say IBMS, the third will say the one I've chosen.

  • Me.Filter on it's own will display 2x DIR=IBMS, even if no filter is selected. It, however, pushes the report out without a filter([DIR]='IBMS' And [Dir]='IBMS' And)

  • Me.Filter doesn't clear with the filter clear button. So I added Me.Filter = "" to the clear filter button, which does wipe the entire filter... but then filtering a report using the combo boxes and pushing to a report, I get a "syntax error in query expression 'And [Fiscal year]='2016-17' AND [DIR]='IBMS' (i chose those two combo boxes, applied no other filter and pushed it to a report).

  • I've played around with "[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter" by trying = Me.Filter & " And " & CurrentFilter (also applying this to the OpenReport where argument), but it breaks the filter leading to syntax error when I add in Me.Filter (CurrentFilter and Me.Filter on their own push a report, but don't give the filter I want). So there's something it doesn't like about Me.Filter & " And " & CurrentFilter
I can't figure out what the difference is between yours and mine on the whole, so any help would be awesome. Sorry if the post was long.
 
S

sneuberg

AWF VIP
Local time
Today, 09:22
Joined
Oct 17, 2014
Messages
3,506
I will need a little time to go through this and figure it out but for now why are you passing Me.Filter & " And " & CurrentFilter to the report like


Code: 
DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, , Me.Filter & " And " & CurrentFilter

If the record source of the report is the same as the subform you would pass subform filter like:

Code: 
DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, ,[COLOR="royalblue"][Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter[/COLOR]

It would be easier for me to figure this out if you upload your database. Could you do that?
 
M

meanie

New member
Local time
Today, 09:22
Joined
Feb 13, 2017
Messages
5
OMG, I had that at one point including other Where arguments, but in my experimenting, it didn't work because of problems in the code and I forgot to come back to it.

It works! Thank you. :banghead:

I'm pasting the working code below for reference:
Code: 
Option Compare Database
Option Explicit

Dim CurrentFilter As String

Private Sub cboDIR_AfterUpdate()
'Restricts Branch combo box list based on selection for DIR
   On Error Resume Next
   cboBranch.RowSource = "Select Branch.Branch " & _
            "FROM Branch " & _
            "WHERE Branch.Dir = '" & cboDIR.Value & "' " & _
            "ORDER BY Branch.Branch;"
End Sub

Private Sub Form_Load()
   Dim Ctrl As Control

   For Each Ctrl In Me.Controls
      If Ctrl.ControlType = acComboBox Then Ctrl = Null
      If Ctrl.ControlType = acTextBox Then Ctrl = Null
   Next Ctrl
   
   CurrentFilter = ""
   Me.Filter = ""

   
   'Rermove and Filter Criteria From the SubForm
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = ""
   'Turn OFF the Filter
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = False
   
   
End Sub

Private Sub RemoveFiltersBut_Click()
   Dim Ctrl As Control

   For Each Ctrl In Me.Controls
      If Ctrl.ControlType = acComboBox Then Ctrl = Null
      If Ctrl.ControlType = acTextBox Then Ctrl = Null
   Next Ctrl
   
   CurrentFilter = ""
   Me.Filter = ""
   
   'Rermove and Filter Criteria From the SubForm
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = ""
   'Turn OFF the Filter
   [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = False

End Sub


Private Function StockSearch()
   On Error GoTo Error_StockSearch
   
   Dim FilterClause As String, D As Long
   Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
   Dim strDateField As String
   strDateField = "[pay run]" 'Put your field name in the square brackets in these quotes.

   
   'Hold whether we Use AND or OR in our Filter Criteria
   D = Me.DirectionGrp.Value
   
   
   'Filter by Option Group Selected

   If Me.PriorityGrp.Value = 5 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[SumRequested] Is Not Null"
   End If
   If Me.PriorityGrp.Value = 6 Then
        If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
        FilterClause = FilterClause & "[SumRequested] Is Null"
   End If
   
   '1st Combo - Fiscal Year Text DataType
   If Nz(Me.cboFY.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Fiscal Year]='" & Me.cboFY.Value & "'"
   End If
    
   '2nd Combo - REGION Text DataType
   If Nz(Me.cboRegion.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Region]='" & Me.cboRegion.Value & "'"
   End If
   
   '3rd Combo - DIR Text DataType
   If Nz(Me.cboDIR.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[DIR]='" & Me.cboDIR.Value & "'"
   End If
   
   '4th Combo - BRANCH Text DataType
   If Nz(Me.cboBranch.Column(0), 0) > 0 Then
      If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
      FilterClause = FilterClause & "[Branch]='" & Me.cboBranch.Value & "'"
   End If

   'Fill this Form wide variable so that it can be used for the Report.
   CurrentFilter = FilterClause: FilterClause = ""
    'CurrentFilter = FilterClause
   
   'TEMP - to be deleted/previous code - Place our created Filter Criteria into the Filter property of SubForm.
   '[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter
   '[Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = True
   
   'Checks if common filter exists, then applies combo box filters
   If Me.Filter & vbNullString = vbNullString Then
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = CurrentFilter
    Else
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter = Me.Filter & " And " & CurrentFilter
    End If
    [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.FilterOn = True
   
Exit_StockSearch:
   Exit Function
   
Error_StockSearch:
   MsgBox "StockSearch Function Error" & vbCr & vbCr & _
          Err.Number & " - " & Err.Description, vbExclamation, _
          "Stock Search Error"
   Resume Exit_StockSearch
End Function

Private Sub StockReportBut_Click()
   On Error GoTo Err_StockReport_Click

   DoCmd.OpenReport "rpt_Costs_by_Employee", acPreview, , [Forms]![ReportCenter]![sfrReportPage].[Form]![srpReport].Form.Filter

Exit_StockReport_Click:
   Exit Sub

Err_StockReport_Click:
   MsgBox Err.Description
   Resume Exit_StockReport_Click
End Sub

Private Function ClearCtrl(Ctrl As Control)
   Ctrl = Null
   Call StockSearch
End Function
 
You must log in or register to reply here.

Similar threads

C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
284
CraigDouglas
C
N
Solved How to filter a single form in MS Access with VBA
Replies
12
Views
477
nector
N
N
Solved Filtering a combo box with over 300 k data in Ms Access linked to SQL Server
Replies
9
Views
680
nector
N
N
Solved How to changes thousands of products prices in a retail business (The backed is SQL Server AZURE)
Replies
3
Views
347
nector
N
A
Typing in a Combobox to filter dropdown as you type
Replies
5
Views
951
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom