Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    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.

  2. #2
    Join Date
    Feb 2004
    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
    End Sub
    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:

    Call ReportOpen("MyReport",#3/1/2004#,#4/1/2004#)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts