VBA run Access Report with Parameter based on query

Question

I want to automate some of our Access Reports with a VBA script. But when I want to run them with DoCmd.OpenReport, I have the problem that every Report has a Popup for some Parameters coming from the underlying Query.

This is how the Parameter looks like in the Query:

PARAMETERS [Time] Text ( 255 );

I googled a lot and tried with:

 DoCmd.OpenReport "B_My_Report", acViewPreview, , "[Time]= 423"

But this didn't work; the popup still comes and when I enter nothing, the query will fail because Time parameter is empty.

Is there a way I can call the Report with the Parameter value. I read a lot of Suggestion to remove the Parameter completely and use the where condition in OpenReport. But I can't change these Queries because they aren't made and maintained by me. I only have to run them sometimes. So I would love to have a solution without touching the Report or the Query.

Answer 1

If your Access version is >= 2010, consider the DoCmd.SetParameter Method.

This query is the Record Source for my report:

PARAMETERS which_id Long;
SELECT rmy.id, rmy.reportDate, rmy.gainOrLoss
FROM record_matYields AS rmy
WHERE rmy.id=[which_id] OR [which_id] Is Null;

So then I can assign a value for the parameter and open the report displaying only the matching record (id is the primary key):

DoCmd.SetParameter "which_id", 4
DoCmd.OpenReport "rptFoo",acViewReport

Or, because of the OR [which_id] Is Null condition in the query's WHERE clause, I can assign Null to the parameter before opening the report if I want all records included regardless of their id values:

DoCmd.SetParameter "which_id", Null

Answer 2

You can't. If you would open the query in VBA, you could supply the parameter. But since the report is the only one who calls the query, the query will ask for its parameter.

If you can't change the query, you'll have to live with it.

Answer 3

You can refer the parameter value from a form that will open this report using the syntax Forms![form name]![control name], so the query needs to be changed something like Select * from [table name] where time_id= Forms![form name]![control name] this will make sure the the query gets parameter from the form's control and it won't prompt parameter. But it only works if you open that query when this form is kept open/loaded...otherwise it will prompt like parameter again.

The Docmd.setparameter in Access 2010 seems to be a very good suggestion where it keeps flexibility and good programming.