asked on

How can a Pass a Parameter (like a month and year mm-yyyy) to multiple queries in a macro?

I have multiple queries that I run via a Switchboard button for monhtly reporting. I would like to be able to enter the date mm-yyyy only once and have this passed to each query that needs it as that query runs.

I am using the following code to run the queries (this is an example of one)

DoCmd.OpenQuery "KO_QN_EFR_A_Product_Sum_Monthly", acViewNormal, acEdit

Can someone show me/ tell me how to do this? Thank you.

Scott McDaniel (EE MVE )

You can have your queries refer to a hidden control on the Switchboard form, then enter that value in the hidden textbox.

Or you can run your queries directly like this:

Currentdb.Execute "Your SQL Here WHERE SomeValue = " & Me.Somevalue

"Your SQL Here" would be the SQL needed for your queries ... you can get that by opening your query in Design view, and then click View - SQL.

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Rather than open the queries in a recordset view, why not open a separate form (datasheet view) and control the specific records with a Where clause in the DoCmd.OpenForm method. If the field content for each query is different, we have a problem.

Rex

ASKER

thank tou for your suggestions. I'm not sure I was clear. essentially, I have a series of queries that prompt me for the month and year as below. I was wondering how I could enter that once, and have the subsequent queries use the information already entered, so that I only had to do it once.
Parameter-Prompt.jpg

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helen Feddema

If the entered year-month combination is used as a query criterion, you can use my code (posted earlier) to save this value to a custom db property, and then use it as a criterion in the query -- just put GetProperty("PropName", "") in the Criteria line of the appropriate field in the query, and remove the query parameter, as it is no longer needed.

GRayL

Can you tell me what kind of queries are in the lot?

Rex

ASKER

Here's an Example of one. It creates a two column table for use in feeding a Pareto chart.

SELECT (KO_QN_Data.Field20)+' ('+[KO_QN_Data.Code group]+')' AS Defect, Sum(CLng(TransformTextFields([KO_QN_Data.DefectQty (ext)]))) AS QTY INTO KO_QN_EFR_B_Product_Pareto_Monthly_Table
FROM KO_QN_Data
WHERE (((Format([Created On],"yyyy-mm"))=[yyyy-mm]) AND ((KO_QN_Data.[Short text for code])="Kimball Office Furniture") AND ((GetBucket([Code group]))="Product") AND ((GetNOFGSA([Product Hierarchy]))="Not NOF GSA"))
GROUP BY (KO_QN_Data.Field20)+' ('+[KO_QN_Data.Code group]+')'
ORDER BY 2 DESC , 1;

Open in new window

GRayL

In addition, my code assumes that the parameter in each query is written: [yyyy-mm] - ie. bracketed. If not bracketed, remove the brackets from the Replace() line.

GRayL

Then my code should work well with that. You just have to enter the query names in the arrQryNames line in place of qryName1, qryName2, etc.

GRayL

I see an error:

DoCmd.RunSQL arrQryNames(i)

should be:

DoCmd.OpenQuery arrQryNames(i)

Rex

ASKER

Thank you both. I went a slightly different route, but both of your solutions helped me arrive there. I really do appreciate it. Thank you.

Rex

GRayL

Thanks, glad to help.