Exporting Reports to Word - breaking up the data into separate docs

[smilla]

Hello there,

I have been asked this question a couple of years ago and I did not know the answer but I'm hoping someone will know now.

I want to export my reports to RTF Word which works ok but I want to create a different word document for each Company. Is there a way of writing some code in VBA to OutputTo but when it reaches CompanyID for instance, it creates a new document by the Company Name.

Any other ideas would be appreciated.

Regards
Smilla

 

[Pat Hartman]

I would create a totals query that summarizes the main query into just the companies contained. Then write a code loop that reads the summary table and runs the OutPutTo for each company in the summarized recordset. The report would need to be based on a query that used a parameter to supply the company.

In this situation, a function that returns the CompanyID would work well. You'll need to define a global variable to use for the code to pass the value to the function.

Select ...
From ...
Where CompanyID = fCompany();

Public gCompanyID as Long

Your code to loop through the company recordset:
....
    gCompanyID = rs!CompanyID
....

Public Function fCompany() as Long
    fCompany = gCompanyID
End Function

 

[smilla]

Thanks, not totally sure what you mean but will work it out. What about creating a different word document for each Company?

Regards
Smilla

 

[Pat Hartman]

If you just want to output an Access report as an .rtf file, use OutputTo. If you want to use MailMerge, that takes a lot more coding. You can find sample code by searching here or in the Microsoft knowledgebase or the MSDN library. The MSDN library has excellent in depth articles on how to do lots of things. Be sure to look at all the Access versions. Don't just limit yourself to your present version. Most techniques will work for more than one version.