asked on

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.

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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" 

    rs.MoveNext
Wend

rs.close
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.

TIgerV

ASKER

als315 came in first. both excellent solutions