Unanswered: pass parameters to subreport in ADE file
I have a rather complicated Access ADP report that has a number of parameters. Users use a criteria form to select the facility/facilities that they wish to return in the report. The main report includes a number of subreports. Data populating the subreports is linked to data in the main report via a common field specified in the Link Child Fields / Link Master Fields properties. Users also have the option in the criteria form of filtering the data in a subreport further, say by date range. In order to pass the date range filter to a subreport, I am presently opening the subreport in design view and setting the recordsource property at that time. This works fine in ADP format; however, when I create an ADE file and attempt to run the same report, I get the error "The expression On Click you entered as the event property setting produced the following error: That command isn't available in an MDE / ADE database." I realize that I cannot open subreports in design view in ADE format (which is why I'm getting the error). Is there a way to pass parameters from a form to a subreport without opening the subreport in design view (so I can run the report in an ADE file)? I read somewhere that you cannot use a parameterized stored procedure as the source for the subreport in this case, as you cannot set the Link Child Fields / Link Master Fields properties to link the report/subreport data if a stored procedure is used as the source. I could simply use a view with a custom WHERE clause for the subreport's source but I haven't been able to figure out how to do this.
You can try calling the Filter property from code, something like this:
Public Sub ReportOpen(strReportName As String, StartDate As Date, EndDate As Date)
DoCmd.OpenReport strReportName, acPreview
Reports(strReportName).SubReport.Report.Filter = "[DateField]>=#" & StartDate & "# and [DateField]<=#" & EndDate & "#"
Reports(strReportName).SubReport.Report.FilterOn = True
This code assumes that the report you open has a Subreport Control named SubReport, with a field named DateField that you wish to filter on.
Then you can call the function with something like: