Loop Through Access Table or Query and Export to Excel

[jhabey01]

I have a query called: Qry detailrps, which I have code below that loops and exports the query based on a unique valu in the column called Group_name

The code creating unique excel files with the respective group name but no records for that group are in the file. How do I get the underlying records to export out as well for each group?

Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim v As String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("select distinct Group_name from qry_detailrpts")

Dim strqry As String
Dim qdftemp As DAO.QueryDef
Dim strQdf As String
strQdf = "_TempQuery_"
Do While Not rs1.EOF
v = rs1.Fields(0).Value

strqry = "select * from tbl_detailrpt where Group_name = '" & v & "'"
Set qdftemp = CurrentDb.CreateQueryDef(strQdf, strqry)
qdftemp.Close
Set qdftemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQdf, "R:\Admin Services\RK BEAM Billing Summary Reports\RKXCHANGE GROUPS\Carrier Payment Backup\Work Area\" & v & ".xls", True
CurrentDb.QueryDefs.Delete strQdf
rs1.MoveNext
Loop
rs1.Close

End Sub

 

[dhookom]

I would not create and delete the querydef. Consider simply updating the SQL property of a saved query. Have you ever copies and pasted the strQry into a blank query SQL view to see if there are any records?

Have you tried any debugging to view the value of strQry?

Duane
Hook'D on Access
MS Access MVP

 

[jhabey01]

In case anyone is interested here the code works. It exports each group individually and saves that data to an excel file with the name of the group as the name of the excel file:

Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim v As String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("select distinct Group_name from qry_detailrpt")

Dim strqry As String
Dim qdftemp As DAO.QueryDef
Dim strQdf As String
strQdf = "_TempQuery_"
Do While Not rs1.EOF
v = rs1.Fields(0).Value

strqry = "select * from qry_detailrpt where Group_name = '" & v & "'"
Set qdftemp = CurrentDb.CreateQueryDef(strQdf, strqry)
qdftemp.Close
Set qdftemp = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, strQdf, "R:\Admin Services\RK BEAM Billing Summary Reports\RKXCHANGE GROUPS\Carrier Payment Backup\" & v & ".xls", True
CurrentDb.QueryDefs.Delete strQdf
rs1.MoveNext
Loop
rs1.Close

End Sub