Automatically output query results with changing parameter?

[Terry161]

I need to prepare weekly reports on how our sales people are doing, and currently have one query for each salesperson (~10). I have a button on a form that exports all of these queries to Excel, using commands like this:
DoCmd.OutputTo acOutputQuery, "QueryName" , acFormatXLS, QueryName-1.xls"

This works--but if I get a new salesperson, I need to create a new query and modify some code. If the criteria for the query changes, I need to change it 10 times.

I have table that stores information about the salespeople (tblSalespeople ), and I'd like to create a loop that runs the query for each tblSalespeople. salespersonName and exports it to Excel.

What is the best way to do that?

[ADezii]

I need to prepare weekly reports on how our sales people are doing, and currently have one query for each salesperson (~10). I have a button on a form that exports all of these queries to Excel, using commands like this:
DoCmd.OutputTo acOutputQuery, "QueryName" , acFormatXLS, QueryName-1.xls"

This works--but if I get a new salesperson, I need to create a new query and modify some code. If the criteria for the query changes, I need to change it 10 times.

I have table that stores information about the salespeople (tblSalespeople ), and I'd like to create a loop that runs the query for each tblSalespeople. salespersonName and exports it to Excel.

What is the best way to do that?

I whipped this up right before going to bed, and it hasn't been thoroughly tested, but it looks good on paper. If you have any problems, feel free to ask and hopefully I'll be completely awake next time.

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intNoOfRecords As Integer, intCounter As Integer
Dim MyQuery As QueryDef

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblSalesPeople", dbOpenSnapshot)
MyRS.MoveLast: MyRS.MoveFirst

Set MyQuery = CurrentDb.QueryDefs("QueryName")

intNoOfRecords = MyRS.RecordCount

If intNoOfRecords > 0 Then
  For intCounter = 1 To intNoOfRecords
    MyQuery.SQL = "SELECT * From tblYourTableName Where [SalesPersonName] = '" & _
                   MyRS![SalesPersonName] & "'"
    DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "QueryName-" & CStr(intCounter) & ".xls"
    MyRS.MoveNext
  Next
Else
  Exit Sub
End If

MyRS.Close

[Terry161]

I whipped this up right before going to bed, and it hasn't been thoroughly tested, but it looks good on paper. If you have any problems, feel free to ask and hopefully I'll be completely awake next time.

Thanks! This does exactly what I need. I was figuring I was getting to the point where I needed to start creating recordsets, and this just saved me a bunch of time. Thanks!

[ADezii]

Thanks! This does exactly what I need. I was figuring I was getting to the point where I needed to start creating recordsets, and this just saved me a bunch of time. Thanks!

Glad to help, Terry161.