I tried Steve's qryTemp idea but couldn't make it work. I created qryTemp in the application, and put a reference to the strId but either it doesn't work, or I don't know how to do it.
What I am trying to do it take a list of 500+ records (in a query) and create more or less 60 xls files, where each file will only have one "OfficeID". The files will then be emailed to the 60+ offices.
I have one query with Titles for the xls file - more or less 60 records.
The second query has the records. I had thought I would be able to use the query and "where" to do this, but I'm not having success.
I also tried transfer spreadsheet and ran into the same issues.
I guess the most important question is, can I do this at all? It seems obvious, but I cannot figure it out.
I am grateful for any and all help.
Thank you,
Patty
This is my code:
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstDetail As DAO.Recordset
Dim strTitle As String
Dim outputFileName As String
Dim qryTemp As String
Dim strOutput As String
Dim qryString As String
Dim frmListXeroxCopiers As Form
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstDetail As DAO.Recordset
Dim strTitle As String
Dim outputFileName As String
Dim qryTemp As String
Dim strOutput As String
Dim qryString As String
Dim frmListXeroxCopiers As Form
Set dbs = CurrentDB
Set rst = CurrentDB.OpenRecordset("Select * from[qryCopiersTitles_Xerox_Survey]")
Do Until rst.EOF
Do Until rst.EOF
Set rstDetail = CurrentDB.OpenRecordset("Select * from[qryListOfCopiers_Xerox_Survey] where [OfficeID] = " & (rst!OfficeID) & ";")
MsgBox "title data complete"
strSQL = "qryListOfCopiers_Xerox_Survey"
MsgBox "title data complete"
strSQL = "qryListOfCopiers_Xerox_Survey"
outputFileName = "C:\CopierSurveyForms\" & strTitle
DoCmd.OutputTo acOutputQuery, strSQL, "MicrosoftExcel(*.xls)", outputFileName, False, ""
'DoCmd.OutputTo acOutputReport, qryListOfCopiers_Xerox_Survey, acFormatXLS, "", False, ""
rst.MoveNext
Loop
rst.Close
rst.MoveNext
Loop
rst.Close
On Tue, Nov 25, 2014 at 10:52 AM, Patricia Mapes <patty.mapes@gmail.com> wrote:
I have the code below where I am trying to create multiple excel files, filtered by an OfficeID. They have distinct titles and each file should have all the records for a particular OfficeID.I can export the query to one file with the first title, and all of the records for all of the OfficeID's. I cannot seem to file the query satisfactorily however.No matter what I do I get some variation of the message:"...could not find the object 'qryListOfCopiers_Xerox_Survey where [OfficeID]= 2' (Two being the correct value for the first set of records.)I have tried many different ways to do this (leaving a couple commented out in the code) and each one gives a very close variation of the same message.I am hoping that you will the optimum way to do this!Thank you,PattyCode:
Set dbs = CurrentDB
Set rst = CurrentDB.OpenRecordset("Select * from[qryCopiersTitles_Xerox_Survey]")
strId = rst!OfficeID
rst.Filter = "OfficeID = '" & strId & "'"
Set rstFiltered = rst
strTitle = rstFiltered!Title & "_" & ".xls"
strSQL = "qryListOfCopiers_Xerox_Survey"
'strSQL = "qryListOfCopiers_Xerox_Survey where (qryListOfCopiers_Xerox_Survey.OfficeID) = " & rstFiltered!OfficeID & ")"
'strSQL = "qryListOfCopiers_Xerox_Survey where [OfficeID] = " & strId & ")"
outputFileName = "C:\CopierSurveyForms\" & strTitle
DoCmd.OutputTo acQuery, strSQL, "MicrosoftExcel(*.xls)", outputFileName, False, ""
MsgBox "Export completed."
rst.Close
Exit Sub
--Patricia Mapes
--
Patricia Mapes
__._,_.___
Posted by: Patricia Mapes <patty.mapes@gmail.com>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar