Execute Insert Into SQL with Variable define recordset

[SevnSins]

I have the following module for exporting some sales reports, and am having problems getting it to run completely.

I have defined Rs2 as a recordset, and want to use it for the data source of my Insert Into statements. Is this possible?

Here's what I have so far. I'm getting a "Missing Semicolon" Error on the line in red.

Public Sub ProdGrpExport()

Dim dbPath As String
Dim fileFolder As String
Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim Rs3 As DAO.Recordset
Dim strRepID As String
Dim strRep As String

dbPath = Application.CurrentProject.Path
Set Rs1 = CurrentDb.OpenRecordset("SELECT tblSmGrd.SalesTerrSCust, tblTerr.TerrName FROM tblTerr RIGHT JOIN tblSmGrd ON tblTerr.TerrID = tblSmGrd.SalesTerrSCust GROUP BY tblSmGrd.SalesTerrSCust, tblTerr.TerrName HAVING (((tblSmGrd.SalesTerrSCust) Is Not Null And (tblSmGrd.SalesTerrSCust)<>' '));")

Do Until Rs1.EOF
strRepID = Rs1("SalesTerrSCust")
strRep = Rs1("TerrName")

Set Rs2 = CurrentDb.OpenRecordset("SELECT sum(tblSmGrd.Year) AS Yr, tblSmGrd.ProdGrp, tblSmGrd.ProdCat, sum(tblSmGrd.NWgtImperial) AS Vol, sum(tblSmGrd.GrossSalesUSD) AS GsUSD, sum(tblSmGrd.GrossSalesL) AS GsCAD, sum(tblSmGrd.NetSalesUSD) AS NsUSD, sum(tblSmGrd.NetSalesL) AS NsCAD, sum(tblSmGrd.COGSUSD) AS CgUSD, sum(tblSmGrd.COGSL) AS CgCAD, sum(tblSmGrd.MarginUSD) AS MgnUSD, sum(tblSmGrd.MarginL) AS MgnCAD FROM tblSmGrd WHERE (((tblSmGrd.SalesTerrSCust)= '" & SalesTerrScust & "')) GROUP BY tblSmGrd.Year, tblSmGrd.ProdGrp, tblSmGrd.ProdCat;", dbOpenDynaset)

DoCmd.RunSQL ("DELETE tblProdTot.* FROM tblProdTot;")
CurrentDb.Execute ("INSERT INTO tblProdTot ( ProdGrp, ProdCat, BuVol, BuGsUSD, BuGsCAD, BuNsUSD, BuNsCAD, BuCgUSD, BuCgCAD, BuMgnUSD, BuMgnCAD ) VALUES (Rs2.ProdGrp, Rs2.ProdCat, Rs2.Vol, Rs2.GsUSD, Rs2.GsCAD, Rs2.NsUSD, Rs2.NsCAD, Rs2.CgUSD, Rs2.CgCAD, Rs2.MgnUSD, Rs2.MgnCAD) FROM Rs2 WHERE Rs2.Yr=10;")

DoCmd.OpenReport "rptProdTot", acViewReport
DoCmd.OutputTo acOutputReport, "rptProdTot", acFormatPDF, dbPath & "\Exports\" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & "\" & strRep & ".pdf", , , , acExportQualityPrint

Rs1.MoveNext
Loop

End Sub

[pbaldy]

As noted on the other site you have this, you can't refer to the recordset values in the SQL. You'd have to concatenate them in:

"...VALUES(" & Rs2.ProdGrp & ", "...

Note that would work for numeric data types. Text would need single quotes added:

"...VALUES('" & Rs2.ProdGrp & "', "...

and dates:

"...VALUES(#" & Rs2.ProdGrp & "#, "...

That said, it seems like a roundabout way of creating reports. I would think you could have the report based on a query that got the criteria for SalesTerrSCust from a form control. Then cycle through your recordset placing the current value in that form control and running the report. This would be another method you could adapt:

http://www.granite.ab.ca/access/emai...recipients.htm

[SevnSins]

I tried it using the syntax you provided, but it now tells me Method or Data Member not Found.

About you're other suggestion...
I think I understand what you're saying:

1. attach my "filter" query (Rs2) to a text box in a hidden form?
2. Then tell my module to apply the loop variable to the text box
3. Run my Insert statements
4. open/export report
5. Loop

If so, I think that will work. I will try it real quick, and see what happens.

[pbaldy]

As to the error, I made the same copy/paste error; dot should be bang:

Rs2!ProdGrp

Yes to what you propose except no insert statements. You shouldn't need a temp table (they have their place, but this doesn't sound like one of them). The report would be based on a query, not a table.