Running report

[dcarr]

I am trying to run a report that is based on an underlying query. The query usually prompts the user for an input but I want to take this out and apply a value before it is run. So I want to first launch the query with the value LG and then run the report based on this value. There are several other input values such as LE, DG, SG, that all need to be run using the same query and report? How can this be done?
Thanks

 

[yellow]

To remove the parameter, design the query and take out the criteria for that field. EG: Remove [Please enter the input value:] And put LG in it's place.

This may also be in the report, so you may need to remove it from there or it will still ask you for a parameter when run.

If you need to run LG, LE, DG and SG all that the same time then put the criteria as LG or LE or DG or SG.

You'll then need to group your report by this to get them to come out in the correct order.

If they don't all need to be run at the same time, then some user intervention is required if they all have to be run from the same report and query unless certain input values are used on certain days for example, then you could program this.


Hope this helps.

 

[cogent1]

Perhaps you should consider putting all the available values in a combo box and opening the report using the selected value in an SQL statement, on the After Update event of the combo. That's how it's usually done.

 

[dcarr]

Is it not possible to use a macro and the set value field? I need to run the report and save it several times? How do you use the set value?

 

[cogent1]

The Set Value argument is the name of a control on a form or report, not a query.

If you want to open a report with different parameters from a macro, you have to use the WHERE condition of the macro.

This means writing a separate macro for each available parameter.

You can use the Set Value option if you create a form with a combo and use the value of the combo to call the report with the parameter you require at any given time.

 

[dcarr]

When building the WHERE condition in the SQL do I place the condition on the underlying query or do I base it on the field in the report? The query underneath currently prompts the user for an input. I want ot change this so I can specify it for the user. Thanks

 

[cogent1]

Your report is based on a query which prompts you for an input,
e.g: [ Enter a value:] to which you respond by entering LG or one of the other codes - is that correct?

Let's call the field concerned MyValue.

Go to Design view of the query and delete the criteria row of MyValue. This makes it a straight select query. If you open the report from the database window, it will show all the rows returned by the query.

To limit the rows, your macro must have [MyValue]="LG" as the WHERE condition. The report then only opens with rows having "LG" as MyValue.

But this means that you have to write a separate macro for each possible MyValue.


If you create a combo on a form, the user simply selects from a list of all MyValues, then clicks a button to open the report. In a macro or code attached to the button, there is a WHERE clause that says

[MyValue]=Forms!MyForm!MyCombo


and the report opens with rows containing MyValue, the same as the combo box. This is more efficient and flexible way of controlling what is returned in the report.

 

[Rich]

Is it not more efficient to set the query criteria to the combo, as opposed to the Where argument of the OpenFor/Report ?

 

[cogent1]

Yes,you're probably right. I'm always having my shortcomings exposed - not a pretty sight at the best of times.

It still requires the user to have an open form and possibly a command button to open the report, unless the AfterUpdate of the combo is used.

Doing it this way simply requires you to replace the existing query parameter with:

Forms!MyForm!MyCombo

Either way works better than using a macro.

 

[Rich]

Agreed, Say No to macros, should be issued as a standard warning