Auto Generated PDF Printing Wrong

[HansV]

It's always tricky to develop a database on a later version of Access than that of the end users. Ideally, you should develop on the oldest version that you need to support. Apart from discontinued features such as FileSearch and the Calendar Control, code generally behaves better when going from an older version to a newer one than the other way round.
I don't see anything wrong with the code that you posted, but Access 2003 will not always recognize code saved in Access 2007 or later...

[Leesha]

The database is was developed in 2003 and it's only in the past 6 months that I've been using 2010 and have no issues deploying it on their server. I recall when I would open a 97 DB in 2003 I was given the option of whether I wanted to convert it or not. I don't recall is that option is available in 2010. Also I don't see that there is an option to convert a 2010 DB to a previous version as there is in 2003. They will eventually need to bite the bullet and have to upgrade all of their workstations but I know finances are tight right now so I was trying to avoid that.
Thanks!
Leesha

[HansV]

All versions of Access starting with Access 2000 support the Access 2000 .mdb format without needing conversion, and all versions starting with Access 2002 support the Access 2002-2003 .mdb format without needing conversion, but still, problems arise because opening a .mdb database in Access 2010 may cause references in the Visual Basic Editor to change, and because it is possible to use newer features in .mdb databases that won't be recognized by older versions...
Don't you have an older computer with Office 2003 around? If so, you could develop the database on that computer. Or you could uninstall Office 2010, then install Office 2003, and finally reinstall 2010, but although it is quite possible to work that way, it is not ideal.

[Leesha]

I do have it on another computer but the issue I'm running into is that it won't let me import the forms I created in 2010. Also its not a computer that is convenient to get to.

[HansV]

I do have it on another computer but the issue I'm running into is that it won't let me import the forms I created in 2010.

That suggests that you used features available only in Access 2010 on those forms - precisely the reason for not using a newer version to develop a database to be used on an older version!

[Leesha]

Big Sigh...................

[Leesha]

Hi Hans,
I can't believe I'm back on this one. The user would like the ability to have the invoice print to print preview before it's actually attached to the email and sent. First is this even possible? I was wondering if the code below were set to print preview would that stop the rest of the code from running until the preview is closed? Ideally that is what they are looking for esp. during this test phase.
Thanks,
Leesha

DoCmd.OutputTo acOutputReport, "rptInvoiceAuto", acFormatPDF, strFilename

[HansV]

Insert the following line above the line that you mention:

Code: Select all

        DoCmd.OpenReport ReportName:="rptInvoiceBevAuto", View:=acViewPreview, WindowMode:=acDialog

Specifying WindowMode:=acDialog causes the code to pause until the user closes the report preview.

[Leesha]

OMG Hans, you are the most wonderful man!!! Thank you not only for the code but also the explanation
Leesha

[Leesha]

Back again!! If the invoice is incorrect and they want to stop it from printing and sending the invoice, would I put code in on the on close event of the report that asks if they wish to email this email or not?
Leesha

[HansV]

The first loop could look like this:

Code: Select all

    Do While Not rst.EOF
        glngInvoiceID = rst!InvoiceID
        strFilename = gstrPath & rst!WalmartNumber & "-" & _
            rst!Store_ID & Format(Me.txtStart, " m.yy") & ".pdf"
        ' Preview the report
        DoCmd.OpenReport ReportName:="rptInvoiceBevAuto", View:=acViewPreview, WindowMode:=acDialog
        ' Ask user for permission to send
        If MsgBox("Send this report?", vbQuestion + vbYesNo) = vbYes Then
            ' Convert report to PDF
            DoCmd.OutputTo acOutputReport, "rptInvoiceAuto", acFormatPDF, strFilename

            ' Create a new e-mail message
            Set outMsg = outApp.CreateItem(0) ' olMailItem
            With outMsg
                ' Use the e-mail address field
                .Recipients.Add outApp.Session.CreateRecipient(rst![BILLING EMAIL])
                ' Add CC recipients
                .Recipients.Add(rst![OTHER EMAIL1]).Type = 2
                .Recipients.Add(rst![OTHER EMAIL2]).Type = 2
                .Recipients.Add(rst![OTHER EMAIL3]).Type = 2
                .Recipients.Add(rst![OTHER EMAIL4]).Type = 2
                ' Change the subject as needed
                .Subject = "Monthly Invoice"
                ' Change the body text as needed
                .Body = "Please see the attached invoice." & vbCrLf & "Sincerely, Rebecca Turner"
                ' Attach the PDF file
                .Attachments.Add strFilename
                ' Send the message
                .Send
            End With

            ' Set EmailFaxSent field to True, and EmailDateTimeSent to Now
            rst.Edit
            rst!EmailFaxSent = True
            rst!EmailDateTimeSent = Now
            rst.Update

            ' Optional: delete the PDF file after creating the e-mail
            ' Delete or comment out the next line if you don't want to delete the file
            Kill strFilename
        End If
        rst.MoveNext
        Me.lbxInvoice.Requery
    Loop

and similar for the other loop.

[Leesha]

Hi Hans!! There are three women here is the USA who have fallen in love with you!
Leesha

[Rudi]

You go Hans...

[HansV]

Wow!

[andymcca]

HI,
I have a database this will be used to autogenerate invoices. It is a 2003 database. I am working in Access 1010. I'm using Stephen Lebans code to generate the PDF's. All has been going well until recently when I was testing the code before deploying at work. The pdf's are only printing 1/2 of the page. I've attached one here to view. The only difference is that I'm using a window's 8 laptop vs the windows 7 laptop that it was created on. I don't know if that would make any difference. Does anyone know of anything that would keep the full page form printing?
Thanks,
Leesha

I registered here (and elsewhere) to reply to threads like this and hopefully help anyone still looking for answers around the Lebans ReportToPDF function, and why it sometimes produces Landscape Only / Half a page in Access 2007/2010 etc

The problem is caused by the way Access 2007/2010+ creates Snapshot files. Snapshot files are no longer supported from Access 2007 onwards, but VBA code that creates them will still run and will still produce a Snapshot.

Snapshot files are actually Compound File Binary Format (CFBF) files, which are designed to store multiple binary files in a single file. In a Snapshot file, each page is actually a standalone Windows Enhanced Metafile (EMF) entry in the CFBF. The last entry in the CFBF is a 'HEADER' file which contains information about the Snapshot. The first 20 bytes contain a signature and the number of pages in the file. The remaining 156 bytes or so contain a DEVMODE structure, which is a standard definition for printing and contains information such as orientation, paper size etc etc

Because DEVMODE is a standard definition structure, it also contains information which is a lot less relevant to a Snapshot file, such as driver version and device name. This is retrieved from the default printer on the client machine at the time the Snapshot file is created and stored in the file.

The key issue is that when creating Snapshots in Access 2007/2010, the DEVMODE structure is slightly different - specifically the deviceName field is actually stored as 64 bytes rather than 32 bytes. The Lebans StrStorage.dll expects this to be a 32 byte string, so will read the latter 32 bytes expecting these to contain size/orientation information! And this is why the resulting PDF files are converted incorrectly. This problem may also cause Snapshot files created by Access 2007/2010+ to exhibit odd behaviour when opened using Snapshot Viewer.

The solution is to fix the Snapshot prior to conversion by reducing the deviceName field back to 32 characters. I don't think I'm allowed to post links here, but I've created a small executable to fix affected Snapshot files prior to conversion, and I've also forked a Snapshot to PDF converter that I found on GitHub to apply the same solution. I believe this could be added to the original Lebans VBA code relatively easily, and I'll do it myself if I can get round to it.

Anyway, hope this information helps someone - especially if you already have broken Snapshot files in existence.

[HansV]

Welcome to Eileen's Lounge and thanks for the information!

[andymcca]

Hans - no problem!

Even though the Lebans ReportToPDF function is no longer supported by Stephen, and has been superseded by Microsoft's in-built PDF conversion in Access 2010 and above, it still produces a nicer PDF output in some instances than the Microsoft implementation.

So it still has its uses and I thought this information would be useful to anyone who still wishes to use the function. It is still used in some of the older Access frontends we use at my company.