Running parameter queries from VBA, suppressing parameter prompts

  • Thread starter Thread starter Edwinah63
  • Start date Start date
E

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
 
You're sure StDt and EndDt have valid dates in them?

What happens if you change your query to
 
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?
 
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
 
Back
Top