Need to set subreport's recordsource from the form that calls the report (based on user parameter)

Posted on 2009-02-19
Last Modified: 2012-06-27
I have an Access project with a SQL backend.  I am calling a report from a form, and the form has a field that allows the user to specify the number of weeks of history to show in a subreport on the main report (I want to go back to the first day of the weeks specified).  Here is the recordsource in Access:  "SELECT * FROM dbo.tblBidCINotes WHERE ((ciActionRequired = 0) OR (ciActionRequired = 1 AND ciDone = 1)) AND ciDate>='" & DATEADD("ww", -Forms!frmReportMenu!ctl_HistWeeks, (Date - Weekday(Date) + 1)) & "'"

Unfortunately, this doesn't work with a SQL backend, so I switched to a stored procedure that receives the ctl_HistWeeks parameter.  (It gets its value from a control by that name in the Report Header section of the main report.)  This works beautifully -- except that the subreport now ignores the Child and Parent Link properties of the subreport and shows ALL the history records for EACH detail record of the main report.

So I added another parameter to replace the link field, and put that control (ctl_bmID) in the DETAIL section of the main report.  Now the subreport is blank for all records.  Any ideas short of putting all the desired history records in a temporary table?  I'll post the stored procedure below.


      -- Add the parameters for the stored procedure here

      @ctl_HistWeeks int, @ctl_bmID int



      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.


      Declare @datDate as datetime

      Set @datDate = Convert(datetime, Convert(Varchar(10), GetDate(), 101))


      SELECT * FROM dbo.tblBidCINotes WHERE ((ciActionRequired = 0) 

            OR (ciActionRequired = 1 AND ciDone = 1)) 

            AND (ciBMID=@ctl_bmID)          

            AND ciDate>=DATEADD(ww, -@ctl_HistWeeks, (@datDate - datepart(dw, @datDate) + 1))




Open in new window

Question by:WMorrissey
    LVL 44

    Accepted Solution

    Yes the behaviour you're describing is, I believe, correct in that this functionality is analogous to when a recordset is assigned as a form/report's source and then link fields have no impact.
    Rather than an entire set of values inserted into a temp table, would it not make more sense to just store the date parameters into a server table to be looked up rather than passed in as parameters.
    That way the subreport's recordsource could be left as a self contained object which requires no parameter, you'd just have to set the table values prior to calling the report.
    (And make them user specific of course - you don't want to simultaneous reporting requests to cause confusion!)

    Author Closing Comment

    This worked great -- Thanks!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Viewers will learn how the fundamental information of how to create a table.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now