Subreport within a Report

[Guest]

Actually I want a single report by the name "Sales and Purchases Summary
Statement" which comprises of a main report name "Purchases" and a subreport
name "Sales".

I have also created a query for main report (i.e. Purchases) with the
following two parameters;

PurchasesType and
Date Range (i.e. Between PurchasesStartDate and PurchasesEndDate)

Likewise I have also created a query for subreport (i.e. Sales) with the
following two parameters;

SalesType and
Date Range (i.e. Between SalesStartDate and SalesEndDate).

I have set the Purchases Query and Sales Query at the Record Source of the
Main Report and Subreport respectively.

Now when I separately open the Main Report and the Subreport, they work very
nicely but when I set the subreport under the main report. It prompts me 2-3
times for the parameters of the subreport (i.e. Sales).

How can I get rid of this. Please explain

Secondly, is it possible that when I run the "Sales and Purchases Summary
Statement", a dialog box is open which ask me the parameter values once for
all of both purchases query and sales query? And which contain two buttons
by the name OK and CANCEL. And when I click on OK button, "Sales and
Purchases Summary Statement" is open in a print preview form.

I would really appreciate a quick reply.

thanks

 

[Marshall Barton]

Actually I want a single report by the name "Sales and Purchases Summary
Statement" which comprises of a main report name "Purchases" and a subreport
name "Sales".

I have also created a query for main report (i.e. Purchases) with the
following two parameters;

PurchasesType and
Date Range (i.e. Between PurchasesStartDate and PurchasesEndDate)

Likewise I have also created a query for subreport (i.e. Sales) with the
following two parameters;

SalesType and
Date Range (i.e. Between SalesStartDate and SalesEndDate).

I have set the Purchases Query and Sales Query at the Record Source of the
Main Report and Subreport respectively.

Now when I separately open the Main Report and the Subreport, they work very
nicely but when I set the subreport under the main report. It prompts me 2-3
times for the parameters of the subreport (i.e. Sales).

How can I get rid of this. Please explain

Secondly, is it possible that when I run the "Sales and Purchases Summary
Statement", a dialog box is open which ask me the parameter values once for
all of both purchases query and sales query? And which contain two buttons
by the name OK and CANCEL. And when I click on OK button, "Sales and
Purchases Summary Statement" is open in a print preview form.

The recommended way to deal with both of your issues is to
create an unbound form with text boxes for the parameters
and a button to open the report. This provides the
parameters to the queries regardless of how many times they
are needed and it avoids the complications of using a pop up
or dialog box.

The query parameters will then look like:
Forms!theform.textbox

Whenever you want to run the report, just open the form,
fill in the parameters and click the button.

 

[Guest]

Thank you very much for your reply mr. barton.

Can you do me a favour by explaining it with some more detail and if
possible can you send me or recommend me any sample which would be helpful
for me to do what i want to do.

Actually I am new to MS Access and I need your help and support to complete
my project at work.

I would really appreciate your reply.

thanks and regards
Stephen

 

[Marshall Barton]

Create a new, unbound form (don't use a wizard). Using the
Toolbox toolbar add 6 text boxes and drag them to a sensible
place on the form. Doubleclick on a textbox to display its
property sheet and change the text box's Name property to
something meaningful such as txtPurchasesType,
txtSalesStartDate, etc. Repeat for the other text boxes.

Then add a command button to the form and let the button
wizard create the code to open the report. Close and save
the form with a name like PrintSP.

Then modify the query's criteria to look like:
Forms!PrintSP.PurchasesType
Between Forms!PrintSP.txtSalesStartDate And
Forms!PrintSP.txtSalesEndDate
etc.