Running parameter queries from VBA, suppressing parameter prompts

[Edwinah63]

Hi everyone, I would like assistance with the following:

I have a parameter query:

PARAMETERS dt1 DateTime, dt2 DateTime;
SELECT
Table1.name, Table1.dt,
IIf(Month([dt])=Month([dt1]),[number],0) AS mth0,
IIf(Month([dt])=Month([dt1])+1,[number],0) AS mth1,
IIf(Month([dt])=Month([dt1])+2,[number],0) AS mth2
FROM Table1
WHERE (((Table1.dt) Between [dt1] And [dt2]));

Where dt1 = Start Date and dt2 = End Date

This query is set up to run some moving 3 monthly data.

I have a form to collect the users and I have the following VBA code:

SomeButton_Click()

On Error Resume Next
Dim qd As DAO.QueryDef

Set qd = CurrentDb.QueryDefs("RollingQry")
qd.Parameters("dt1") = me.StDt
qd.Parameters("dt2") = me.EndDt
qd.Parameters.Refresh
DoCmd.OpenQuery qd.Name

End

The query results just need to pop up on the screen in a datasheet
view for the users. There is no need for the results to appear in a
report or sub-form.

However when the query runs it still prompts me for the parameter
values even tho' I have passed them in the code. How do I stop this?
If the values are being passed from the form via VBA to the query
proper, the prompts are redundant.

I could just enter "where table1.dt between [forms]![someform]![stdt]
and [forms]![someform]![enddt]" etc but then I would have to
needlessly clutter my query by making references to the form text
boxes throughout plus making the query less portable.

Can anyone help? If I cannot suppress the prompts, how else can I
structure the query without making a cluttered mess of it? Absolutely
desperate here!!

edwinah63

 

[Douglas J. Steele]

You're sure StDt and EndDt have valid dates in them?

What happens if you change your query to

 

[Douglas J. Steele]

Oops. Hit Enter too soon.

Does it make a difference if you change the query to the following?

qd.Parameters("dt1") = CDate(Me!StDt)
qd.Parameters("dt2") = CDate(Me!EndDt)

Are you being prompted for both dt1 and dt2?

 

[Douglas J. Steele]

D'oh! You're right, of course, Marsh!

 

[Edwinah63]

Hi guys,

Thanks for your assistance. Dissapointing that I can't reduce the
dependencies, but will think about the recordset option.

If anyone else has any ideas, all input very welcome!

e63