Search code examples
ms-accessvbams-access-2016

Filter from VBA gets error when I add another filter manually (Access 2016)

Got a database with a bunch of forms. One of them is my "starting page" with several buttons, each opening a form. I have entries from may years so, to make it more functional, I have a combo box on the starting page with all the years whose value I use in a code on all my buttons to filter the forms when they open. The code looks like that:

Private Sub Edit_Sale_Contract_Click()

'Opens the selected form'
DoCmd.OpenForm "new Sale Form", , , , acFormEdit

'Applies the filter'
If Forms![Main Menu].Combo10 = "All" Then
    Forms![new Sale Form].FilterOn = False
Else
    Forms![new Sale Form].Filter = "[crop]=" & Chr(34) & Forms![Main Menu].Combo10 & Chr(34)
    Forms![new Sale Form].FilterOn = True
End If
End Sub

I also have several reports. What I am trying to do is make a form with a Tab control, each Tab holding one report. For now, I have this similar code on the On Open event of my reports:

Private Sub Report_Open(Cancel As Integer)
    If Forms![Main Menu].Combo10 = "All" Then
        Me.FilterOn = False
   Else
       Me.Filter = "[crop]=" & Chr(34) & Forms![Main Menu].Combo10 & Chr(34)
       Me.FilterOn = True
End If
End Sub

This works fine so far. I open the form with a button(no filter code) and the reports open correctly in the tabs, already filtered.

The problem: When I try to to apply a secondary filter on "Name" for example with RightClick->equals"Name" it refreshes the report and does not apply it. I tried changing the event on the reports from On Open to On Load. The second filter then applies correctly, but when I click anywhere I get Run-time error '5': Invalid procedure call or argument. Debug indicates the line

MeFilterOn = True

Closing the debugger, the second filter on the "Name" is cancelled, the first one on "Crop" is still on.

Any advice is appreciated. Please note that I am learning Access and VBA myself and I am terribly new at it. Thanks in advance

Solution

  • Your current code overwrites any set filter. If you want to combine filters, you need to append your new filter to the old one:

    Private Sub Report_Open(Cancel As Integer)
    If Forms![Main Menu].Combo10 = "All" Then
        'Do nothing, because else you would deactivate the custom filter
   Else
       If Me.Filter <> "" Then
           Me.Filter = Me.Filter & " AND [crop]=" & Chr(34) & Forms![Main Menu].Combo10 & Chr(34)
       Else
           Me.Filter = "[crop]=" & Chr(34) & Forms![Main Menu].Combo10 & Chr(34)
       End If
       Me.FilterOn = True
   End If
End Sub

    Note that Access tends to save filters with the report, especially when using layout view. You need to make sure the Report.Filter property is cleared when saving the report.

    Use the same approach for the form.