Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Individual record PDFs from one report?

Discussion in 'Information Technology' started by fixedpower@yahoo.com, Jul 28, 2006.

  1. fixedpower@yahoo.com

    fixedpower@yahoo.com
    Expand Collapse
    Guest

    Hello: I have an Access 2000 DB where I have a 100 page report, with
    each page for a different client. I want to output the report to 100
    individual PDF files so I can deliver them to clients. Is there any
    way to get this output, other than running the report 100 different
    times? Thanks!
     
  2. Loading...


  3. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    I don't know how you could output to a pdf, but if you would settle for HTML
    instead you could do something like the following.

    It would require a table (tblClient) that contains a list of the clients,
    and also some field that could be used to filter the recordsource of the
    form.

    Public Sub OutputReport()
    on error goto Err_Label
    Dim strReportName As String
    Dim strFileName As String
    Dim rst As ADODB.Recordset
    Dim strRecordSource As String
    Dim lngValue As Long

    Set rst = New ADODB.Recordset
    rst.Open "tblClient", CurrentProject.Connection
    rst.MoveFirst
    Do While Not rst.EOF
    ' your html file name for this report is the name of the client with an
    ..htm extension
    strFileName = "C:\" & rst.Fields("ClientName").Value & ".htm"
    ' lngValue is how you will determine the filtering for the report based
    on the client
    lngValue = rst.Fields("KeyValue").Value
    ' strReportName is simply the name of the base report
    strReportName = "rptReportname"
    ' create a string containing the record source for the report for this
    particular client
    strRecordSource = "SELECT * FROM tblSomeTable WHERE SomeValue = " &
    lngValue
    ' open the report in design view
    DoCmd.OpenReport strReportName, acViewDesign
    ' assign the new record source to the report
    Reports.Item(strReportName).RecordSource = strRecordSource
    ' close and save the report with the new record source
    DoCmd.Close acReport, strReportName, acSaveYes
    ' Export the report contents to an HTML document
    DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, strFileName
    ' go the the next client and repeat the process until you run out of
    clients
    rst.MoveNext
    Loop
    ' clean up
    Exit_Label:
    On error resume next
    rst.clost
    set rst = nothing
    exit sub
    Err_Label:
    Msgbox err.description
    Resume Exit_Label
    End Sub
    <fixedpower@yahoo.com> wrote in message
    news:1147306025.432444.106090@i39g2000cwa.googlegroups.com...
    > Hello: I have an Access 2000 DB where I have a 100 page report, with
    > each page for a different client. I want to output the report to 100
    > individual PDF files so I can deliver them to clients. Is there any
    > way to get this output, other than running the report 100 different
    > times? Thanks!
    >
     

Share This Page