OutputTo Macro that Passes Criteria to Query First

[langlang98]

I haven't found this question asked before so I apologize if it has and I have overlooked it. I have created a macro in Access that I am wanting to use the OutputTo Action to output a query to an Excel spreadsheet. What I am wanting to do it run this for multiple sales reps and have an output file for each rep. My thought was to have an OutputTo Action for each rep and have the action pass the rep number to the query for Criteria but I am having issues doing this. What I have done as an alternative is to have a query for each rep instead. I dabble with VBA enough to get some things done and am not a programmer by any means but am sure there has got to be a simple line of code or an option I am over looking that allows this. Below is the Code that I have.

Function Pivot_Table_Macro()
On Error GoTo Pivot_Table_Macro_Err
DoCmd.SetWarnings False
DoCmd.OpenQuery "Make MTD Table", acViewNormal, acEdit
DoCmd.OpenQuery "Make YTD Table", acViewNormal, acEdit
DoCmd.OutputTo acOutputQuery, "99945101", "MicrosoftExcelBiff8(*.xls)", "L:\Commercial Team Reports\HME Pivot Tables\RepPivotTables\99945101.xls", False, "", 0
DoCmd.OutputTo acOutputQuery, "99945301", "MicrosoftExcelBiff8(*.xls)", "L:\Commercial Team Reports\HME Pivot Tables\RepPivotTables\99945301.xls", False, "", 0
DoCmd.OutputTo acOutputQuery, "99945302", "MicrosoftExcelBiff8(*.xls)", "L:\Commercial Team Reports\HME Pivot Tables\RepPivotTables\99945302.xls", False, "", 0
MsgBox "Macro completed.", vbInformation, ""
DoCmd.SetWarnings True


Pivot_Table_Macro_Exit:
Exit Function
Pivot_Table_Macro_Err:
MsgBox Error$
Resume Pivot_Table_Macro_Exit
End Function


The blue font are the individual OutputTo Actions for each query. My thought would be to change it to something below but have Criteria pushed to it so that I have only a single query but the Criteria changes.

DoCmd.OutputTo acOutputQuery, "Pivot Table Query", "MicrosoftExcelBiff8(*.xls)", "L:\Commercial Team Reports\HME Pivot Tables\RepPivotTables\99945101.xls", False, "", 0

and have a line similar to below that would push the criteria to the query.

Queries![Pivot Table Query]![D2 Sales Rep_Label]='99945101'

Any help on this would be greatly appreciated!

Jim

 

[alansidman]

Instead of multiple queries, why not develop a search form that allows you to input the sales rep name and then run the query. Here is how to do that:

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[langlang98]

The idea is that we have 50 reps and I want the macro to automatically run all 50 without user input as the reps don't change that often. And every month we are going to run this macro with minimal user input. (this isn't for ad hoc reporting, it is for monthly sales reports for all of the reps) This way I only have to update the macro once in a while as we add a new rep.

Jim

 

[martipe1]

Jim,

I want to do the same. Did you find a solution?

Thanks,

Eduardo