Access Loop thru Record Set (Either a Table or Query) & Export Each Unique Group to an Excel Fil

[jhabey01]

Hi,
What I have:
I have a table which has a say a thousand records. A column in the table is called Group and say a hundred records in group1, a hundred records in group2 and so on I also have a column call Order each group is a assigned the same order number.

I also have query that is a basically uses this table and lists all the fields.

What I want..
VBA code to loop through each distinct group and export the group to an excel file.

Code I have:

Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strPath As String
Dim strFileName As String
Dim strquery As String
strSQL = "Select distinct Order from Tbl_Carrier"
strPath = "R:\Admin Services\RK BEAM Billing Summary Reports\RKXCHANGE GROUPS\Carrier Payment Backup\"
strquery = "qry_detailrpt"
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs

If .RecordCount > 0 Then
.MoveLast
.MoveFirst
Do Until .EOF
strFileName = "Premium_PaymentDetail_" & .Fields!order & ".xls"
DoCmd.OpenReport qry_detailrpt, acViewPreview, , "Order = " & .Fields!order, , acHidden
DoCmd.OutputTo acOutputQuery, strReport, "xls", strPath & strFileName

DoCmd.Close acQuery, strReport
DoEvents
Loop
End If
End With
rs.Close
setrs = mothing
setdb = Nothing
End Sub


Any thoughts on how to get this working. Seems to be getting stuck at the : DoCmd.OpenReport qry_detailrpt, acViewPreview, , "Order = " & .Fields!order, , acHidden -
Error I am getting is action or method require a report name arguement
Thanks for looking,
John

 

[PHV]

Do yourself a favor and use Option Explicit...
What are qry_detailrpt, strReport, setrs and setdb ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Also I would be very worry about using Order or Group as my fields’ names since those are reserved words in Access.

Have fun.

---- Andy