[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

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

1 Solution
Leigh PurvisDatabase DeveloperCommented:
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!)
WMorrisseyAuthor Commented:
This worked great -- Thanks!

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now