Help with Subreport's Recordsource 1

[VickyC]

Greetings

My report(rptR, bound to qryR) has a subreport (srptS bound to qryS).
My form has 4 buttons (cmdS0, cmdS1, cmdS2, cmdS3).

When I click cmdS0, I have code to open rptA...

        DoCmd.OpenReport "rptA", acViewPreview, , , acDialog

Here's where my newbie question comes in. When I click cmdS1, I once again want to open rptA, but I want a restriction put on srptS's recordsource, qryS. (I want only those records where the field WorkGroup = "" OR "Reserve". There are similar criteria on cmdS2 and cmdS3.

How do I do this? When the buttons are clicked, neither rptA nor srptS are open.

thanks for any help
Vicky C

 

[dhookom]

I would create a saved query as the record source for the subreport. You can change the SQL property of this saved query prior to opening the report.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane - thanks for responding. I haven't been able to change the subreport's Recordsource before it is open. For a relative newbie, could you amplify what you meant by "change the SQL property" and how I could go about doing this? Thank you for your time - Vicky C.

 

[dhookom]

Let's assume you have 3 saved queries qS1, qS2, and qS3 that properly filter the subreport as needed. Create a new query qS with SQL view of:

   SELECT * FROM qS1;

Then, before you actually open the report, use code like the following for cmdS2:

Currentdb.QueryDefs("qS").SQL = "SELECT * FROM qS2;"
DoCmd.OpenReport "rptR"

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane: So the subreport always draws its values from qS, but the definition of qS changes depending on which cmd is clicked - very nice! Vicky C

 

[dhookom]

That's the way it works. As you have probably noticed, the WHERE CONDITION of the DoCmd.OpenReport has no effect on the subreport's records.

Duane
Hook'D on Access
MS Access MVP