Getting a circular reference in a Module

[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 (which I got from this great forum) 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! Or if you have code that you believe is better than the below, can you pass it on.

As an additional FYI, I changed the Do.cmd to:

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

But 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." (And yes I have checked the spelling and ensured that all the folders are there).





Code:
Function ExportSCACOTR()

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

Set MyRS = CurrentDb.OpenRecordset("qryCarrierSCACGrouping")
Set MyQD = CurrentDb.QueryDefs("qryCarrierExportModule")
MyRS.MoveFirst
Do
SCACcd = MyRS("SCAC")
MyStr = ("SELECT * from [qryCarrierExportModule] where [SCAC] = " & SCACcd & "")
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

 

[simongallop]

Circular reference is because your SQL is saying in lay mans language: GET EVERYTHING FROM THIS QUERY

You can't have a query getting information from itself!! (Your SQL is referencing SELECT * from [qryCarrierExportModule], yet [qryCarrierExportModule] is your query def which you are placing the SQL into...ie never ending circle)

HTH

 

[jfgambit]

Harry:
Would you recommend I create a third query to run the SQL statement from, or change my QueryDef?

 

[simongallop]

Look at the query in design view, place an example of your limitations in the criteria then look at it in SQL view. Copy that SQL code which ought to say SELECT fieldnames FROM another source WHERE [another source].SCAC = 'SAMPLE';

Paste the copied code to your string variable replacing SAMPLE with " & SCACcd & "

Now it ought to work

 

[jfgambit]

Harry:
I have done what you suggested, see below:
Function ExportSCACOTR()

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

Set MyRS = CurrentDb.OpenRecordset("qryCarrierSCACGrouping")
Set MyQD = CurrentDb.QueryDefs("qryCarrierExportModule")
MyRS.MoveFirst
Do
SCACcd = MyRS("SCAC")
MyStr = ("SELECT dtaDailyShipmentInformation.[Chain#], dtaDailyShipmentInformation.ChainNm,dtaDailyShipmentInformation.[Buyer#] from [qryCarrierExportModule] where [SCAC] = " & SCACcd & "")
MyQD.SQL = MyStr
DoCmd.OutputTo acReport, "rptCarrierExportModule", "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

But I still get the circular reference error at the DoCmd.OutputTo acReport Line.

If I remove this line from the module it runs just fine (I just do not get any excel sheets...

I appreciate all your help, and I am still stumped.

 

[simongallop]

Why is your SQL saying "...FROM [qryCarrierExportModule] " when all the fields are claiming to be from dtaDailyShipmentInformation?? Also where is the field SCAC in your SQL??

I would have thought that you ought to be saying:

MyStr = "SELECT dtaDailyShipmentInformation.[Chain#], dtaDailyShipmentInformation.ChainNm,dtaDailyShipmentInformation.[Buyer#] from [dtaDailyShipment] WHERE dtaDailyShipmentInformation.[SCAC] = " & SCACcd & ";"

Just to point out that in the above string you are treating SCACcd as a number yet it is declared as a string the variable ought to be enclosed in single quotes as well as double ie: ....[SCAC] = '" & SCAcd & "';"

HTH

 

[jfgambit]

Harry:
As you were responding to me I found the problem...the information needed to be pulled from the table not the query.

Duh...wow, sometimes you really someone to slap you upside the head and say "Hey, wake-up!"

Thanks for all the help and for reminding me about the single quotes!!

Everything works great now.

FYI...this is the only forum I know of where people respond in times uncomparable with other Access Forum sites. I have had this question on 4 other sites for 5 days and no one has even looked at it.

Again THANKS!