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?

 

[Marshall Barton]

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.

AFAIK, OpenQuery is unaware of any parameter settings you
have done in your code. It goes directly to the QueryDef's
SQL property.

To use VBA code to set the parameter values you would also
need to open a recordset:
Set rs = qd.OpenRecordset()
and use a simple datasheet form based on the recordset:
DoCmd.OpenForm "mydsform"
Forms!mydsform.Recordset = rs

OR
you could do what you were trying to avoid and use the form
references in the query.

Either way, you have some dependencies between either the
two forms or your form and the query.

 

[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