I have the following code that I am trying to get to do the following: Query the main table (which has manager as a field as well as several other data fields) for a unique list of managers, then query and return the data for each manager and send it to an excel spreadsheet named for the manager.
so if the main table had 2 rows for manager = Jones and 4 records for manager = Smith I would get 2 spreadsheets saved one named Jones and the other Smith each containing just the rows aligned to them in the table
I get Error 91 on this line Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Code:
Option Compare Database
Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim strSQL As String
Dim rs As DAO.Recordset
Setdbs = CurrentDb
strSQL = "SELECT DISTINCT [Manager] FROM qry_Monthly_Export " & "ORDER BY [Manager]"
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
While Not rs.EOF
strSQL = "SELECT * FROM qry_Monthly_Export " _
& "WHERE [Manager] = '" & rs("Manager") & "'"
dbs.QueryDefs("qry_ExportCopy").SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"qry_ExportCopy", _
"C:\Temp\" & rs("NameField") & ".xls"
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub