Set Query Parameters in a Macro

[AccessExcelHell]

Hi,

I have a query with 1 field where the criteria value changes. I want to create a macro that runs this query for each possible value of the 1 field. I tried to use the SetValue function prior to running the query but that didn't work. Any ideas?

Thanks in Advance.

 

[BNPMike]

Are you saying you want the query to run N times where n is the number of different values in some table field F? Maybe you could tell us more about the query.

 

[JonFer]

What is happening each iteration of the query?

You could have a subform that shows a table in datasheet view where the various parameter values could be edited (one parameter per record) and then just run the query once. I have used a table with a Yes/No field that the user can check to include that value as a parameter. It's similar then to a multi-select listbox but doesn't require any coding to get the query results.

If you need to step through the values, you can use the macro command that moves to the next record of a subform and have the query criteria set to the field on the subform (it will be the value on the current record).

 

[AccessExcelHell]

Sorry, I wasn't clear above.

short version of the query:

select blah from blah where x=[Enter value];

I have a macro where I use the "Output To" function, which runs this query for me and saves the results in a excel file on my drive. I copied the "Output To" about 15 times in the macro. The problem is that I have to enter the 15 values each time to create 15 files, because the difference is the 1 parameter in the query. So what I want to be able to do is "set the value/parameter" for the query, then have it output, assign another parameter and run the query and have it output, etc..

I know what the values to be entered are, and they don't change, so I figured that there should be a way to do this.

Thanks.

 

[JonFer]

I would do this with a table in a sub-form and have the output filename shown in a second field next to the parameter value.

Then you can just do a loop in the macro and not have the OutputTo action repeated. The filename parameter in the macro would be an expression that returns the value of the filename field on the subform. If the filename can be built from the parameter value, you don't need the second field.

You have more control with this in a module - you can generate a recordset based on the table and then use MoveNext to run through each record. Each iteration you'd set the query SQL and then do the export.

 

[BNPMike]

To be frank I would copy out the query 15 times, each with a fixed value, as you say the 15 values are always the same. The you could stick them in a macro and let them run automatically.

It would be more elegant to run one query 15 times from a macro but I can't see how to supply a parameter each time inside the macro.