how to move to the next recordset and clean up the previous query by VBA

[ice051505]

Hi dear all, I have piece of code which is about to export different query into multiple worksheets in one excel file. Queries are made from distinct value of one recordset, I cam get the first loop successfully, but when it move to the second recordset, the result I got is the sum of two queries. I am pretty new on built the recordset and don't know what caused the problem in my code, I hope some of you genuis can have a look at my code here, Thanks a lot!

 

[ice051505]

Sorry, here is the code...not familiar with the forums...lol

Private Sub ExprDetail_Click()
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim rst As Recordset
Dim recordCnt As Integer
Dim recordNm As Integer
Dim sql As String
Dim arrStr As String
Dim arrCount() As String

Const JOIN_SEP = ""","""

Set db = CurrentDb()
Set qdf1 = db.QueryDefs("RecordExcel")
Set rst = qdf1.OpenRecordset
recordCnt = rst.RecordCount
ReDim arrCount(1 To recordCnt) As String
rst.MoveFirst
For recordNm = 1 To recordCnt
Set qdf2 = db.QueryDefs("DivideGrp")
arrCount(recordNm) = rst!grouping
arrStr = """" & Join(arrCount(), JOIN_SEP) & """"
sql = "SELECT Format_Step3.TimeGroup, Format_Step3.ClnVar, Format_Step3.SumOfCatch, " _
& "RecordExcel.grouping FROM Format_Step3 INNER JOIN RecordExcel ON Format_Step3.grouping = RecordExcel.grouping " _
& "WHERE (((RecordExcel.grouping)In ( " & arrStr & ")));"
qdf2.sql = sql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DivideGroup", "C:\Documents and Settings\WangA\My Documents\Export_Excel_Files\FNexcel.xls", True, arrStr
rst.MoveNext
qdf2.Close
Set qdf2 = Nothing
Next recordNm
End Sub

 

[SkipVought]

hi,

This forum is more suited to VBA question for applications other than Access.

I think you'll get a better response in an Access forum like forum705

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ice051505]

Thanks!!!