Repopulate subreport at runtime.

CochiseCounty used Ask the Experts™
I have a main report with a subreport. How do I requery the query that used for the subreport everytime the main report is opened?
I have something like this, seems like it works, but the subreport picks the result from the last runtime. How do I make the subreport pick the newest result? My subreport name 'rpt_SubReport'. Thanks for help.
Dim qd As QueryDef
    Set qd = CurrentDb.QueryDefs("Q_BiweeklySupreport_Cal")
    qd.SQL = ("SELECT * FROM CalDetail WHERE ((CalDetail.ReturnDate) BETWEEN #" & dtStartDate & "# AND #" & dtEndDate & "#) ORDER BY [Type], [TagNo]")
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't think your question is clear enough.
When you run a report you ALWAYS get the latest data.  It is not possible for Access to retain the previous results.

If you are saying that the subreport recordsource is always working on the criteria you set in the previous run then it means that you are opening the report too early.
Where are you running the code you have shown in your question?


The code is in the Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) in the main report. What do I do to make it not open too early? Do I need to put the code in the subreport?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Do you have the
Link Child Fields and Link Master Fields set on the Sub Report Control?

If so, you should not have to requery the sub report.



OK, I made it work. I moved the code to the Report_Open, instead of Detail_Format.
Thanks for helping

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial