Error 91 Object Variable or Block Variable Not Set

[mindbender]

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)

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

[pbaldy]

If that's a copy paste, you need a space after Set:

Setdbs = CurrentDb

[mindbender]

Man I stared at that for hours.....thanks so much.

Now that I am past that step I get the following error 3265 - Item not found in this selection

and it highlights this section

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
 "qry_ExportCopy", _
 "C:\Temp\" & rs("NameField") & ".xls"

[pbaldy]

The only field you selected for the recordset was Manager, so it's going to error if you specify any other field.

[mindbender]

Figured it out. I had the namefield wrong for the file creation step.

It is working perfect now.

Thanks for all your help

[pbaldy]

No problem.