Opening a reports based off it's sub report data

[Jedi420]

Is there a way to open a report from criteria based on its sub-report? I have a report that displays projects (from tblProjects) and a sub-report(from tblProject_Updates) in that report that lists all the updates (if any) for each project. The two tables are linked via the projectID (and so are the report and sub-report). I want to be able to open the report and while having it display every project, only show the updates within a certain range. However, when I perform a

pubCriteria = "(tblProject_Updates.updateDate BETWEEN #6/1/2003# AND #6/30/2003# )"

DoCmd.OpenReport "Projects", acViewPreview, , pubCriteria
[code]

It prompts me for tblProject_Updates.updateDate. How can I run a report from criteria based off its sub-report.  I hope I made enough sense.  Any help or suggestions would be greatly appreciated. (^_^)

 -Jedi420

 

[dhookom]

If I understand correctly, you will need to have the subreport recordsource be filtered/criteria'd by text box values on a form. The record source of the subreport would use criteria like:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate

You could also use functions that retrieve global variables but the form controls might be easier. The form will need to be open but could be invisible.

Duane
MS Access MVP

 

[GoDawgs]

I think you should change the recordsource of the parent report. Right now you have it set to tblProjects, just set it up so it's a query linking the two tables and containing the criteria you want, that way only the records with data in the sub report will show up. Hope that helps.

Kevin

 

[Jedi420]

dhookom,

Thanks for responding. You say I should use criteria like:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate

but I am. Im using

"(tblProject_Updates.updateDate BETWEEN #6/1/2003# AND #6/30/2003# )"

only that I was just using the real dates to make the example simpler. See, what I want is to be able to select a date range on a form. and then open the report so that the main report still shows all the projects, and the subreport shows all the status updates for each project within that date range. So I try to just open the report using criteria like that above, but it prompts me for tblProject_Updates.updateDate.

-------------------------------
godawgs,

Thnx for repsonding! I believe the recordsource for the query is OK ... I just cant seem to be able to filter the sub report how I want it. Like, I should be able to easily view all the projects but only the status updates for projects that are within a specifice date range.
-----------------------------------



Any Ideas?


-Jedi420

 

[dhookom]

My suggestion was to include the criteria in the record source of your SUBREPORT. Don't do anything regarding dates with the filter of the main report. I assume your subreport record source has the column UpdateDate. Under this column/field, set a criteria of:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate

Duane
MS Access MVP

 

[GoDawgs]

Oh sorry, I misunderstood...Duane has the right answer...

Kevin

 

[Jedi420]

I'm sorry, I do not understand what you mean by set the criteria of the column/field. I thought that's what I was doing. Im trying to set the recordsource of the SUBREPORT to

Me.RecordSource = "Select updateDate, statusUpdate FROM projectStatus WHERE updateDate Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate "

in the sub-reports Report_Open procedure. I've read that's the only place you can set the recordsource for a subreport, but it says that I cant set the recordsource property once the printing has begun. This really seems like it should be a simple thing, but I can't get it to display what I want it to for the life of me ... :-(

-Jedi420

 

[dhookom]

Set the criteria in design view of the subreport's record source query, not through code. Attempting to do this through code will not work (AFAIK).

Open the subreport in design view, view the record source in design view, set the criteria under the appropriate column, and close & update the record source.

Duane
MS Access MVP

 

[Jedi420]

no, dhookom, it is unacceptable for me to always set the criteria of the subreport in design mode manually each time i run the report. Thanks for your help though, but I HAVE found a way to set the subreports recordsource through code at run-time ... So to anyone whose curious, heres how I do it.

To set the recordsource of a subreport (or report for that matter) during runtime, the only time it can be done is during the reports OnOpen event. So just enter your recordsourced changing code in the reports Report_Open( Cancel as integer) procedure. If you set this anywhere else, you WILL recieve an error.

Thanks for the help though guys!

-Jedi420

 

[dhookom]

You wouldn't have to open the subreport in design view every time to change the criteria. Your criteria could reference a control or controls on a form or might even use a function that returns a global variable. If you changed the date values on the form, the subreport would automatically be filtered. That is why I suggested the use of:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate
in the record source query of the subreport. You would not need to use code and you would not need to open any report in design view.

Opening a report in design view everytime you wanted to run the report would be a horrible solution.

Duane
MS Access MVP

 

[Jedi420]

Ahhh ... I see now what you mean by that. Very cool solution! Sometimes its very hard to get an accurate mental model of how people are trying to explain things. Sorry for the confusion and thanks for the help! (^_^)

-Jedi420

 

[Jedi420]

Oh! But one thing that makes me hesitate from migrating from my current solution to yours. Sometimes, I just want all the dates (so I have an 'Any Date' check box on the form by the date range text boxes), so, by using code, I can check if the criteria form isloaded or not and if the date range text boxes are null, then proceed accordingly. Just an afterthought.

-Jedi420

 

[dhookom]

This can be accomodated by setting the text box values to minimum and maximum values in the afterupdate event of the check box. Otherwise you could use
Between IIf(Forms!frmA!chkAnyDate,0,Forms!frmA!txtStartDate) and IIf(Forms!frmA!chkAnyDate,Date(),Forms!frmA!txtEndDate)

This would select records with dates up to today. If you have future dates, change Date() to something way out in the future.


Duane
MS Access MVP