Trouble clearing Access report filter

Question

I have a report listing several meetings with several records per meeting. I've set up a pop-up form to select a single specific meeting date with a combo box and use that to filter the form for printing purposes. (The built-in filtering click-through is clunky and apparently combo boxes don't work on reports.) This all works fine the first time through. The problem comes when I try to select a different meeting date - the report still displays the first meeting selected despite all efforts to clear the filter, other than closing the form and re-opening. I've placed the following code in the report button that opens the selection form:

Private Sub cmdMeetingSelect_Click()
'Clear any pre-existing filter
    Me.Filter = ""
    Me.FilterOn = False
'Open form to filter the report
    DoCmd.OpenForm "frmMeetingSelect"
End Sub

The form opens, I see that the filter gets turned off on the report, and I'm able to select a new date on the form, but the the old date is still used to filter the report again.

Including the following on the selection form doesn't help:

Private Sub Form_Open(Cancel As Integer)
    Me.cboMeetingSelect = ""
End Sub 

This is the code in the selection form sending the filter choice back to the report. Not sure if something about it makes it work only once:

Private Sub cmdFilterMeeting_Click()
'Run a filter on the RSVPAttendance report
    Reports!rptRSVPAttendance.Filter = "MeetingDate = Forms!frmMeetingSelect.cboMeetingSelect"
    Reports!rptRSVPAttendance.FilterOn = True
'Close selection form
    DoCmd.Close
End Sub

Clicking through Advanced -> Clear All Filters in the report is also ineffective.

I've seen a few old reports of bugs regarding filtering forms with combo boxes in Access 2010, but surely that's been resolved by now? What else am I missing?

Answer 1

Your filter string is weird. It should read:

Reports!rptRSVPAttendance.Filter = "MeetingDate = #" & Format(Forms!frmMeetingSelect.cboMeetingSelect.Value, "yyyy\/mm\/dd") & "#"

Answer 2

there are several things that seem odd here:

1. get rid of these lines in cmdMeetingSelect_Click, they are not doing anything for your report, that controls the filters on the form: Me.Filter = "" Me.FilterOn = False

2. You have a syntax error in your filter criteria, but that is besides the point. I think you expect to filter to change when the report is currently open. that will not work , you have to close the report and re-open it, and you cannot change filters this way. what i suggest you do instead is set the where condition on the report instead of trying to apply a filter. Before you do this though, open that report in design mode, and remove any existing filter, and also set filters off. Then replace what you have in cmdFilterMeeting_Click with this:

Const RptName As String = "rptRSVPAttendance"

 'close report if already open
 If SysCmd(acSysCmdGetObjectState, AcObjectType.acReport, RptName) = 1 Then   'report is open
  DoCmd.Close acReport, RptName
 End If

 'open report with where criteria, no filtering needed
 DoCmd.OpenReport RptName, acViewPreview, , ("[MeetingDate] = #" & Me.cboMeetingSelect.Value & "#")

'close form
 DoCmd.Close acForm, Me.Name