I have been using Access forms and reports with extensive vba code as part of my Planchest application for nearly ten years. However, this simple requirement has me stumped.
I'm trying to generalise a method of allowing users to filter and sort in a form using standard Access menu options and have the same filter and sort apply in the corresponding report.
To this end, I have a dozen paired forms and reports which use the same recordsource and have same named controls.
When the user clicks a report button from a filtered form, they are prompted to give the filter a friendly name (to complete a label on the report) and then on the open event of the report, the corresponding form filter and orderby properties are retrieved and applied.
This works fine for almost any filter condition except for a date field where the date is blank. Then I get the error:
Run-Time error '7874': can't find the object '|1'
In my Report open event I have:
Private Sub Report_Open(Cancel As Integer)
SourceFilterOrder Me, Forms("frmProjectActionsIn
This code is generic to all reports:
Public Sub SourceFilterOrder(ByRef rpt As Report, ByVal frm As Form)
rpt.RecordSource = frm.RecordSource
rpt.Filter = SimpleFilter(frm)
rpt.FilterOn = frm.FilterOn
rpt.OrderBy = SimpleOrder(frm)
rpt.OrderByOn = frm.OrderByOn
Function SimpleFilter(frm As Form) As String
If .FilterOn And Not IsNull(.Filter) Then
SimpleFilter = Replace(Replace(.Filter, frm.Name & ".", ""), "", "")
Function SimpleOrder(frm As Form) As String
If .OrderByOn And Not IsNull(.OrderBy) Then
SimpleOrder = Replace(Replace(.OrderBy, frm.Name & ".", ""), "", "")
(These last two strip the form name whether or not in square brackets from the filter and sort expressions)
My underlying form recordsource may already include dynamically applied SQL 'where' conditions and although I could append the filter condition before applying it to the report recordsource, this would mean I had failed to simply clone the filter and order from the form to the report, surely a simple enough objective.