Please help with exporting query results

[Rhapsody]

Hi, I currently export to pdf the results from a report for each clients account, some clients need this to now be as an excel worksheet, I modified my current code to a docmd from the query, which exported and saved the files for each client name, except when I open the files the data is for all clients not that one client.
Is there a way to have the query results for each client saved to their own individual file? any help would be appreciated. My code is below Thanks

Public Sub exportClientActn()
On Error GoTo errHandler
Dim rst As DAO.Recordset
Dim cnn As DAO.Database
Dim str_sql As String
Dim rpt As Report
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim att As Outlook.Attachment
Dim str_filename As String
Dim counter As Long

Set cnn = CurrentDb

str_sql = "SELECT DISTINCT tblInvoices.[InvoiceDate], tblClientDetails.[ClientAccount] AS clientid, tblClientDetails.[Category] AS TYPE, tblClientEmail.[Email ID], tblClientEmail.[Email ID2]" _
& " FROM (tblInvoices INNER JOIN tblClientDetails ON tblInvoices.[Client_Account] = tblClientDetails.[ClientAccount]) INNER JOIN tblClientEmail ON tblClientDetails.[ClientAccount] = tblClientEmail.[ClientNo] " _
& " where [Method] = ""EFT""" _
& " order by 2"

Set rst = cnn.OpenRecordset(str_sql)

counter = 0
While Not rst.EOF
str_filename = "C:" & Format(rst.Fields("Pay End Dt"), "YYYYMMDD") & "-" & rst.Fields("RECIPID") & ".xls"
Debug.Print "Creating Excel: " & str_filename
If rst.Fields("type") = "SUPER" Then
DoCmd.OutputTo acQuery, "EFT AP Super File", "MicrosoftExcel(*.xls)", str_filename, False, ""
Else
DoCmd.OutputTo acQuery, "EFT AP File", "MicrosoftExcel(*.xls)", str_filename, False, ""
End If
counter = counter + 1
rst.MoveNext
Wend

ExitSub:Exit Sub
errHandler:Call ErrorHandler("basexportClientActn", "exportClientActn")
Resume ExitSub
End Sub

 

[pbaldy]

Presumably the query doesn't have a criteria that would restrict it to a particular client, as it would need. Add a textbox to the form that the query looks at for a criteria (it can be hidden), and populate that textbox with the appropriate data in your loop before the export.

 

[Rhapsody]

Hi, query looks at tables not form, what I would like is to include the criteria in my code somehow so that it will export the results before it loops through the records again. Thanks

 

[pbaldy]

I mean that the criteria should come from a form; I would expect the data to come from a table. The query needs a criteria of some sort; you can't just use code to restrict the records to be exported.

 

[Rhapsody]

Hi, Thank you for your help, simple really when you know what your doing, thanks for taking the time to put me on the right path.