One Query One Report Multiple Text Files Output

[racer25]

Hi,

I tried searching for this and did not get anything close to what I am looking for, which porobably means I am not really sure what to look for.

My problem is that I have one table with details of all invoices that I am paying to multiple suppliers. These suppliers vary each month, but all have a unique id.

I email a remittance advice to each vendor/supplier and have a report that has a new page for each.

My objective is with your assistance hopefully, to create code that for each new vendor outputs a seperate textfile with the filename = to the Vendor Name.

As I say I don't really have a clue where to start, but with some pointers I can usually get to the end.

I have uploaded a rough sample that should show this a little better.

Thanks in advance.

R

 

[racer25]

Thanks for that its one table that all fields would populate it in the query.

I know I need something with Do until you have reached the end of the query but after that, way off getting started.

 

[Bat17]

to keep the formatting of the report:-

change the source of the report to

SELECT Remittance.* FROM Remittance WHERE ((([Remittance].[VND_CODE])=getVndCode()));

then in a module you can use this code

Global strVndCode As String

Sub exportReport()
Dim rs As DAO.Recordset
Dim sql As String
Dim strPath As String
strPath = "C:\PathToDirectoryToSaveTo\"
sql = "SELECT DISTINCT Remittance.VND_CODE FROM Remittance;"
Set rs = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)
Do While Not rs.EOF
    strVndCode = rs!VND_CODE
    DoCmd.OutputTo acOutputReport, "Remittance", acFormatTXT, strPath & strVndCode & ".txt"
    rs.MoveNext
Loop
Set rs = Nothing
End Sub

Function getVndCode()
getVndCode = strVndCode
End Function

HTH

Peter

 

[racer25]

Thanks a million for that.

I now have it working and reading through the code, understand what you are doing.

Now to get it to attach the files to Lotus Notes drafts, which I have something kind of working.

Thanks again.