• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Running reports by field value

I have a membership database where the membership is broken up by Region and Chapter numbers.

I would like to run a report for each of the 100 chapters with only their membership, and export it to a PDF named "REGION x CHAPTER xxx"  where x=value of region and chapter numbers.

Do I need to make individual exports, or can I do this easily.

Added bonus:  under documents\roster\ I have folders divided by region (7 of them numbered 0 to 6).  I'd like to save the pdf into those folders.
1 Solution
You can do it with DoCmd.OutputTo
Select Region and Chapter in query and loop across records. Look at sample
Dale FyeCommented:
I generally do this by opening the report (the whole report, with all 100 chapters)

Then I create a recordset and loop through the recordset, filtering the report for each chapter, then save/send the report using docmd.OutputTo and docmd.SendObject methods.  Using this technique ensures speeds things up over opening the report 100 times with a unique WHERE condition.

It might look something like:
docmd.OpenReport "yourReport", acViewPreview
set rpt = reports("yourReport")

strSQL = "SELECT [Chapter], [Region], [ChapterEmail] FROM tbl_Chapters "
set rs = currentdb.openrecordset(strsql,,dbfailonerror)
While not rs.EOF
    rpt.Filter = "[Chapter] = " & rs!Chapter    'assumes Chapter is numeric
    rpt.FilterOn = True

    strFileName = "C:\SomeFolder\" & Format(rs!Region, "00") & "\Chapter_" & Format(rs!Chapter, "000").pdf
    docmd.OutputTo acOutputReport, rpt.name, acFormatPDF, strFileName
    docmd.SendObject acSendReport, rpt.name, adformatpdf, rs!ChapterEmail, , , "Chapter report" 


set rs = nothing

Open in new window

Using SendObject this way, you may get a bunch of Outlook warning messages popup.  I personally use vbMAPI to handle this issue.
TIgerVAuthor Commented:
als315 came in first.  both excellent solutions
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now