Printing graphs in report (query based)

[scarlet1978]

Hi,

I currently have an Access report that is based on a query, and the query is based on variables entered in a form (e.g. begindate and enddate).

The report is generated OK (currently appears in print preview format), however when I try to print the report out it asks for the variables to be entered a second time. I have the feeling that it needs to run the query a second time to print. Is there a way around this?

Thanks.

 

[scarlet1978]

I forgot to mention that the report consists of a table and a graph (which is entered as a subreport). If I remove the graph from the report the problem disappears. If I open the subreport with just the graph in it and try printing I have the same problem - so the problem definitely relates to the graphs.

 

[GingerR]

I suggest creating a form, with text boxes which you first enter the data into, then have your report reference these text boxes instead of prompting you for input. In the recordsource for your report, instead of having something like "[Enter Date:]", you'd have

[Forms]![YourFormName]![txtDate]

where [txtDate] is the name of your text box on the form. You'd do the same for the query that the chart is based on. That way, it won't ask any more.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crowley16]

erm, did you say something about the inputs being on a subform?

if that's the case, then use GingerR's method but with:
[Forms]![YourFormName]![SubForm]![txtDate]

--------------------
Procrastinate Now!

 

[scarlet1978]

The inputs are currently on a form. What happens:

I open the form and have two text boxes asking for a beginning date and an end date. I then have a query that references the form through using, for example:

[Formulieren]![FormAskAccountLossHistory]![cboInceptionDateLossHistory]

The report is then generated based on the query. The problem is that although I get a print preview, when I try to actually print the report it has lost the values in
[Formulieren]![FormAskAccountLossHistory]![cboInceptionDateLossHistory]
and asks for them a second time.

 

[GingerR]

So when you first generate the report (in Preview mode), it doesn't ask for the values, does it?

Please post your report's record source.
Please also post your chart's recordsource.

Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scarlet1978]

OK.

Report record source: Query entitled 'QueryPremiumClaims'

Chart record source: same query 'QueryPremiumClaims'

Query record source: Table entitled 'Program' and
WHERE (((Program.AccountID)=[Formulieren]![FormAskAccountLossHistory]![cboAccountNameAskLossHistory]) AND ((Program.InceptionDate)=[Formulieren]![FormAskAccountLossHistory]![cboInceptionDateLossHistory]));

A friend of mine suggested that the resolution of the graph may have to be changed when it get's printed out, which is why it needs the input parameters again. Would that make sense? At the moment I have a fairly indirect solution of putting the query into a table (INTO ... SELECT ... WHERE) and running the report from the new table...

Thanks for your help.

 

[traingamer]

Are you closing your form when you do the print preview?

traingamer

 

[GingerR]

If it's set up exactly as you say, it should not ever "ask" you for parameters unless the form is not available at the time something (in this case your report) is looking for it.

So again: When you PREVIEW the report with the chart embedded in it, it DOES NOT prompt you for parameters, correct?

If so, how are you 'printing' after the print preview? Using the built-in menu or your own button or custom menu item? If your own button or menu item, please post its code here.

Does the subreport have a recordsource? IF so, please post it here. Please post not just the 'query' name, but the SQL of the query itself.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

By the way, how come you have your chart in a subreport instead of directly on the main report?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GingerR]

Hi again.

Please post your sql code of query "QueryPremiumClaims".

Also, why do you have your chart in a subreport instead of directly on the main form?

Thanks.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scarlet1978]

OK, the SQL of the query is:
(sorry, but this is the updated SQL where it makes a new table to get around the problem):

SELECT Account.AccountName, Program.AccountID, Program.InceptionDate, QueryPremiumClaimsCombined.ABCYearBeginLossHistory AS Year, QueryPremiumClaimsCombined.Premium, QueryPremiumClaimsCombined.Claims, [Claims]/[Premium] AS LR INTO DynamicLossHistory
FROM Account INNER JOIN (QueryPremiumClaimsCombined INNER JOIN Program ON QueryPremiumClaimsCombined.ProgramID = Program.ProgramID) ON Account.AccountID = Program.AccountID
WHERE (((Program.AccountID)=[Formulieren]![FormAskAccountLossHistory]![cboAccountNameAskLossHistory]) AND ((Program.InceptionDate)=[Formulieren]![FormAskAccountLossHistory]![cboInceptionDateLossHistory]));

And this also comes from a query called QueryPremiumClaimsCombined (as you can see), which looks like:

SELECT ProgramID, ABCYearBeginLossHistory, ABCPremium0 AS Premium, ABCClaims0 AS Claims
FROM Program
UNION ALL
SELECT ProgramID, [ABCYearBeginLossHistory]-1, [ABCPremium-1] AS Premium, [ABCClaims-1] AS Claims
FROM Program
UNION ALL
SELECT ProgramID, [ABCYearBeginLossHistory]-2, [ABCPremium-2] AS Premium, [ABCClaims-2] AS Claims
FROM Program
UNION ALL
SELECT ProgramID, [ABCYearBeginLossHistory]-3, [ABCPremium-3] AS Premium, [ABCClaims-3] AS Claims
FROM Program
UNION ALL SELECT ProgramID, [ABCYearBeginLossHistory]-4, [ABCPremium-4] AS Premium, [ABCClaims-4] AS Claims
FROM Program;

- which just transposes the variables I need into the format I need for a graph.

I am printing just using standard print buttons - either in the file menu or the print icon. I have the same problem if I ONLY print the subreport containing the graph.

Why the graph as a subreport? Because I was struggling to get the layout I wanted with a table and a graph together in a report.

Please don't kill me for such bad programming! I admit I don't know a thing about databases or SQL, but my boss asked me to do it so here I am... Thanks for your patience and help!