How to stop the parameter prompt when opening a report linked to a saved query?

[CNx]

I have created a report, and set the record source to a parameterized query. The query has two parameters, DATEFROM, and DATETO. Although uncommon, I want to maintain the ability for a user to directly open the report using the DB Window at which point, Access will naturally ask the user to enter "DATEFROM" and "DATETO".

However, most common users will go through a form to open the report; at which point I don't want the application to prompt them to enter DATEFROM and DATETO

Despite everything I try, including the code below, Access still prompts me to enter the parameters DATEFROM and DATETO. Does anybody know why the code might not work when attached to a report?

    Dim qDef As QueryDef
    Set qDef = CurrentDb.QueryDefs("qryReport")
    qDef.Parameters("DATEFROM") = DateAdd("d", -75, txtDate.Value)
    qDef.Parameters("DATETO") = txtDate.Value
 
    DoCmd.OpenReport "rptUsage", acViewPreview

P.S. Yes, I'm aware that I can change teh saved query to [Forms]![MyForm]!txtDate.Value, but that defeats my goal of report atonomy as mentioned in the first paragraph. I am more interested to know why I can't set a querydef parameter in VB and not have Access recognize it.

 

[statsman]

As long as your report is running from THAT query, it is always going to present the prompts.
Why? Because you have set up the report to run from a query that must have parameters entered before it can produce a report.

If you don't want the parameter prompts, copy the existing report with a new name and set the record source to a new query that doesn't require parameters or directly from the table.

 

[ajetrumpet]

as Long As Your Report Is Running From That Query, It Is Always Going To Present The Prompts.
Why? Because You Have Set Up The Report To Run From A Query That Must Have Parameters Entered Before It Can Produce A Report.

If You Don't Want The Parameter Prompts, Copy The Existing Report With A New Name And Set The Record Source To A New Query That Doesn't Require Parameters Or Directly From The Table.

well Put Rainman!

 

[Rich]

Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("qryReport")
qDef.Parameters("DATEFROM") = DateAdd("d", -75, txtDate.Value)
qDef.Parameters("DATETO") = txtDate.Value

DoCmd.OpenReport "rptUsage", acViewPreview

Try
qDef.Parameters[DATEFROM] = DateAdd("d", -75, txtDate.Value)
qDef.Parameters[DATETO] = txtDate.Value

or

qDef.Parameters(0) = DateAdd("d", -75, txtDate.Value)
qDef.Parameters(1)= txtDate.Value