Excel Macro getting a error code

[jfgambit]

I have a table that has multiple line information for Carriers. I need to export an individual Excel sheet for each carrier based on their SCAC Code. I have 2 queries: the first pulls the individual SCAC Codes the second pulls all the information to be transferred to the Excel sheet. I have the below code in a Public Function, but every time I run it through a Macro I get the following message "Circular reference caused by 'qryCarrierExportModule'.

When I run the debugger it highlights the following line: DoCmd.OutputTo acOutputQuery, "qryCarrierExportModule", "MicrosoftExcel(*.xls)", "C:\Documents and Settings\jfrick\My Documents\OnTime Rpts\OTR_" & SCACcd & "_" & txDate & ".xls"....

Can anyone see why...I'm stumped!

Code:
Function ExportSCACOTR()

Dim MyRS As Recordset
Dim SCACcd As String
Dim MyStr As String
Dim MyQD As QueryDef
txDate = Format(Date, "mmddyy&quot

Set MyRS = CurrentDb.OpenRecordset("qryCarrierSCACGrouping&quot
Set MyQD = CurrentDb.QueryDefs("qryCarrierExportModule&quot
MyRS.MoveFirst
Do
SCACcd = MyRS("SCAC&quot
MyStr = ("SELECT * from [qryCarrierExportModule] where [SCAC] = " & SCACcd & "&quot
MyQD.SQL = MyStr
DoCmd.OutputTo acOutputQuery, "qryCarrierExportModule", "MicrosoftExcel(*.xls)", "C:\Documents and Settings\jfrick\My Documents\OnTime Rpts\OTR_" & SCACcd & "_" & txDate & ".xls"
MyRS.MoveNext
Loop Until MyRS.EOF = True
MyRS.Close
MyQD.Close

End Function

 

[bev]

I'm not quite sure I get all that you're doing, but assuming the rest of your code is right, shouldn't you be using:

DoCmd.TransferSpreadsheet rather than DoCmd.OutputTo?

DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel97, "qryCarrierExportModule", "C:\Documents and Settings\jfrick\My Documents\OnTime Rpts\OTR_" & SCACcd & "_" & txDate & ".xls"

Bev

 

[jfgambit]

Bev:
I have tried that, unfortunately I get Runtime Error '3011':
The Microsoft Jet Enginee could not find the object C:\Documents and Settings\jfrick\My Documents\OnTime Rpts\OTR_0000_071902 .Make sure the object exists and that you spelled its path correclty.

Any ideas why??