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?
Your filter string is weird. It should read:
Reports!rptRSVPAttendance.Filter = "MeetingDate = #" & Format(Forms!frmMeetingSelect.cboMeetingSelect.Value, "yyyy\/mm\/dd") & "#"