[AccessD] Loop for PDF Generation

Sat Oct 26 14:01:36 CDT 2024

Here's an extract from code we used for many years to loop through a
recordset containing client information (e-mail addresses, payment terms,
etc.)-it opens the report in Hidden mode, makes a few edits to some text
fields (invoice number, date, due date) then exports the file to PDF and
calls a procedure to activate Outlook to e-mail the report. I don't recall
having a dependency on ADO (and I don't really see any ADO objects in the
code). Hope it helps:
-----
    Const rptName As String = "qryInvoiceDetails"
    strInvoicePath = DLookup("DetailValue", "AppDetails",
"Key='InvoiceFilePath'")

With rstQBID
        While Not .EOF
        'Step 2 - open Invoice Report in hidden window; filter to current
QBID; output to email with pdf attachment
            bSend = True 'assume we'll send the invoice
            strWhere = "QBID='" & rstQBID("QBID") & "'"
            If IsNull(.Fields("Email")) Then
                'can't send invoice - update status message and continue
                bSend = False
                strStatus = strStatus & vbCrLf & "EMail not available for
QBID=" & rstQBID("QBID") & "."
            Else
                bSend = True
                strTo = Nz(.Fields("Email"), "NA")
            End If
            strCC = Nz(rstQBID("QBCust.CC Email"), "")
            strInvoiceDate = frm.txtInvoiceDate
            strInvoiceNo = frm.txtInvoicePrefix & frm.txtInvoiceNo
            strSubject = "Your xxx Invoice"
            strInvoiceFN = "xxx.com Invoice #" & strInvoiceNo & " - " &
rstQBID("Company") & " - " & Format(strInvoiceDate, "YYYYMMDD") & ".PDF"

            DoCmd.OpenReport reportname:=rptName, View:=acViewPreview,
wherecondition:=strWhere, windowmode:=acHidden
            Set rpt = Reports(rptName)
            rpt.Controls("InvoiceDate") = strInvoiceDate
            rpt.Controls("InvoiceNumber") = strInvoiceNo
            rpt.Controls("InvoiceDueDate") = strinvoiceduedate

            'Alternate - save report as named pdf file, then automate
outlook to send file (or use smtp)
            On Error GoTo sendError
            Application.Echo False
            DoCmd.SetWarnings False
            DoCmd.OutputTo acOutputReport, rptName, acFormatPDF,
strInvoicePath & "\" & strInvoiceFN
            'SendInvoice sub uses Outlook to send HTML message with attached
file
            If bSend Then
                SendInvoice strSubject, strTo, strmsgtext, strInvoicePath &
"\" & strInvoiceFN, strCC
            End If
            DoCmd.Close acReport, rptName, acSaveNo
            Application.Echo True
            DoCmd.SetWarnings True
            On Error GoTo handleError

nextInvoice:
            frm.txtInvoiceNo = frm.txtInvoiceNo + 1
            lngInv = lngInv + 1
            rstQBID.MoveNext
            DoEvents
        Wend
    End With
-----

Eric Flamm
Flamm Consulting, Inc.
eric at flammconsulting.com

-----Original Message-----
From: AccessD
<accessd-bounces+eric=flammconsulting.com at databaseadvisors.com> On Behalf Of
accessd-request at databaseadvisors.com
Sent: Saturday, October 26, 2024 1:00 PM
To: accessd at databaseadvisors.com
Subject: AccessD Digest, Vol 256, Issue 1

Send AccessD mailing list submissions to
	accessd at databaseadvisors.com

To subscribe or unsubscribe via the World Wide Web, visit
	https://databaseadvisors.com/mailman/listinfo/accessd
or, via email, send a message with subject or body 'help' to
	accessd-request at databaseadvisors.com

You can reach the person managing the list at
	accessd-owner at databaseadvisors.com

When replying, please edit your Subject line so it is more specific
than "Re: Contents of AccessD digest..."

Today's Topics:

   1. Access VBA code (loop) needed (DENISE A DECENSO)
   2. Re: Access VBA code (loop) needed (Stuart McLachlan)
   3. Re: Access VBA code (loop) needed (John Bodin)
   4. Re: Access VBA code (loop) needed (Stuart McLachlan)

----------------------------------------------------------------------

Message: 1
Date: Fri, 25 Oct 2024 21:12:30 +0000
From: DENISE A DECENSO <DADECENS at sentara.com>
To: "AccessD at databaseadvisors.com" <AccessD at databaseadvisors.com>
Subject: [AccessD] Access VBA code (loop) needed
Message-ID:

Content-Type: text/plain; charset="us-ascii"

Hello, I have a simple access database used only to create and export
reports as pdf files.
It  produces a single large multi-page report for many employers (each
having its own page) and I would like to save each employer as its own
separate pdf, with the employer as the file name. Currently it is being done
manually print/save 1 at a time.

Here is the relevant information:
   the data source to be output to pdf is a report named [FundingReport]
   the critical field in the report is [EmployerName].  The report is
grouped and sorted on that field.
   the report has a natural page break with the content for each
[EmployerName].
   there are about 550 pages (employers) in total.

   the goal is to output to pdf a file for each Employer report with that
[EmployerName] as the file name.

  Other notes:
  Using Access 2016. There is no "DAO" in the library, so any code I've
found that has DAO is failing.

I appreciate any help you can give me.

Thank you,
Denise DeCenso

Disclaimer:

This electronic message and its contents and attachments contain information
from Sentara Health and is confidential or otherwise protected from
disclosure. The information is intended to be for the addressee only.

If you are not the addressee, any disclosure, copy, distribution or use of
the contents of this message is prohibited. If you have received this
electronic message in error, please notify us immediately and destroy the
original message and all copies.

------------------------------

Message: 2
Date: Sat, 26 Oct 2024 08:04:52 +1000
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: Access Developers discussion and problem solving
	<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Access VBA code (loop) needed
Message-ID: <671C1604.9494.8EDF41A at stuart.lexacorp.com.pg>
Content-Type: text/plain; charset=US-ASCII

I don't understand "no DAO in the library".  Do you mean the refence to 
Microsofto Office 1x.0 Access database Object Library
 is not selected in the VBE Tools- References. If so, select it.

How are you creating the PDF?
Are you using something like:
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, ReportFolder() & 
strFileName

I'd do something like this:

Create a function
STATIC FUNCTION EmplName(sName AS STRING) As STRING
     Dim strStore As STRING
    If sName <> "" Then strStorre = strsName
   EmplName = strStore
END FUNCTION

Set the Report source to a query that has EMployeeName = EMplName()

Step through a recordset/list of EmployeeNames.
If you REALLY don't have DAO, populate a listbox with the EmployeeNames and
step 
through it)

For Each .....
EmplName(!EmployeeName)
strFilename  = EMplName()
Docmd.Output......
Next

On 25 Oct 2024 at 21:12, DENISE A DECENSO via AccessD wrote:

> Hello, I have a simple access database used only to create and export
reports as pdf files.
> It  produces a single large multi-page report for many employers (each
having its own page) and I would like to save each employer as its own
separate pdf, with the employer as the file name. Currently it is being done
manually print/save 1 at a time.
> 
> Here is the relevant information:
>    the data source to be output to pdf is a report named [FundingReport]
>    the critical field in the report is [EmployerName].  The report is
grouped and sorted on that field.
>    the report has a natural page break with the content for each
[EmployerName].
>    there are about 550 pages (employers) in total.
> 
>    the goal is to output to pdf a file for each Employer report with that
[EmployerName] as the file name.
> 
>   Other notes:
>   Using Access 2016. There is no "DAO" in the library, so any code I've
found that has DAO is failing.
> 
> I appreciate any help you can give me.
> 
> Thank you,
> Denise DeCenso
> 
> 
> Disclaimer:
> 
> 
> This electronic message and its contents and attachments contain
information from Sentara Health and is confidential or otherwise protected
from disclosure. The information is intended to be for the addressee only.
> 
> If you are not the addressee, any disclosure, copy, distribution or use of
the contents of this message is prohibited. If you have received this
electronic message in error, please notify us immediately and destroy the
original message and all copies.
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

------------------------------

Message: 3
Date: Sat, 26 Oct 2024 12:29:29 +0000
From: John Bodin <jbodin at sbor.com>
To: Access Developers discussion and problem solving
	<accessd at databaseadvisors.com>
Cc: DENISE A DECENSO <DADECENS at sentara.com>
Subject: Re: [AccessD] Access VBA code (loop) needed
Message-ID:

Content-Type: text/plain; charset="us-ascii"

I use PDF995 for any PDF programming.  In your case, you could use the Burst
feature to separate the single PDF into multiple PDF's.  Call the EXE with
parameters.  Free to use but you get a browser popup nag every time you call
it.  $9.95 to buy.   I've been using it for many years.  Good luck.

John

-----Original Message-----
From: AccessD <accessd-bounces+jbodin=sbor.com at databaseadvisors.com> On
Behalf Of DENISE A DECENSO via AccessD
Sent: Friday, October 25, 2024 5:13 PM
To: AccessD at databaseadvisors.com
Cc: DENISE A DECENSO <DADECENS at sentara.com>
Subject: [AccessD] Access VBA code (loop) needed