Solved

Microsoft Access 2013 Create PDF with File Name to include Date Range

Posted on 2015-01-28
9
144 Views
Last Modified: 2015-01-29
I need to create a report that will output a file name to a pdf file. The title will be TotalByOrganization_mm.dd.yy_to_mm.dd.yy  The field for the first date is StartDate the field for the second date is StopDate. The form name is frmreportmenutab. The User will type the StartDate and Stop Date and click on the command button to generate report. I have it partially working but just about every other test I get Output cancelled error message. Here is the code I am using.
On Error GoTo Command126_Err
'Dim myfilename As String
'myfilename = "w:\Total by Organization.pdf"
'DoCmd.OutputTo acOutputReport, "Total by Organization", "PDFFormat(*.pdf)", myfilename, True, "", , acExportQualityScreen
Dim strFileName As String
Dim strFileName2 As String
Dim strFileName3 As String
Dim strFileName4 As String
Dim strFileName5 As String
Dim myfilename1 As String
Dim strmonthstart As String
Dim strdaystart As String
Dim stryearstart As String
Dim strmonthstop As String
Dim stryearstop As String
Dim MyNewStart As Date
Dim strdaystop As String
Dim myfilename As String

DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryrpt1dborgid")
DoCmd.OpenQuery ("qryvisreqnumdetails")
DoCmd.OpenQuery ("qrytotalrequest")
strmonthstart = DatePart("m", StartDate)
strdaystart = DatePart("d", StartDate)
stryearstart = DatePart("y", StartDate)
MsgBox (stryearstart)
'strdaystop = DatePart("dd", StopDate)
strmonthstop = DatePart("m", StopDate)
strFileName = "W:/Report/"
strFileName2 = strFileName & "TotalbyOrganization"
strFileName3 = strFileName2 & "." & strmonthstart
strFileName4 = strFileName3 & "." & strdaystart
strFileName5 = strFileName4 & "." & stryearstart
myfilename = strFileName5 & ".pdf"


DoCmd.SetWarnings True
'DoCmd.OutputTo acOutputReport, "rptendofday", "PDFFormat(*.pdf)", myfilename, True, "", , acExportQualityScreen
 'DoCmd.OpenReport "TotalbyOrganization", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "TotalbyOrganization", "PDFFormat(*.pdf)", myfilename, True, "", , acExportQualityScreen

Command126_Exit:
    Exit Sub

Command126_Err:
    MsgBox Error$
    Resume Command126_Exit

Open in new window

I am having trouble withstryearstart = DatePart("y", StartDate) I it seems to have pick up 365 for some reason for the year. Thanks for the help.
0
Comment
Question by:marlind605
  • 6
  • 3
9 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40575094
DatePart("y", YourDate) gives the Day of the Year. If you want the year:

DatePart("yyyy", YourDate)

See this:

https://support.office.com/client/DatePart-Function-26868a79-5505-4e5a-8905-6001372223fa
0
 

Author Comment

by:marlind605
ID: 40575103
That fixes the year problem Pat Thanks. Any idea on what causes the Output to action was cancelled  to come up. I click the button and it works I click it again and i get the error message.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40575148
Probably because the Report is already open. Instead, try this:

DoCmd.OpenReport "TotalbyOrganization", acViewPreview, "", "", acHidden
DoCmd.OutputTo acOutputReport, "TotalbyOrganization", "PDFFormat(*.pdf)", myfilename, True, "", , acExportQualityScreen
DoCmd.Close acReport, "TotalByOrganization"
0
 

Author Comment

by:marlind605
ID: 40575224
Scott I can run it once using your code. If I close the PDF file when I run it again I get another error message. "The database engine could not lock table tbltotalreq because it is already used by another person or persons. I create tbltotalrequest on line 22 of the code.
SELECT tblvisreqnum.DBORGID, Count(tblvisreqnum.VisReqNum) AS TotalRequests INTO tbltotalrequest
FROM tblvisreqnum
GROUP BY tblvisreqnum.DBORGID;

Open in new window

This creates a table so I can get a total count for an organization. Not sure if this is the root of my problem? Do I need to close the table at a certain point after the report is created. The report uses qryorganizationcount and uses a couple of different tables to get the output of the report. I am doing further testing but I hope I get some more advice.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:marlind605
ID: 40575878
Still getting the "The database engine could not lock table tbltotalreq because it is already used by another person or persons. " error. Returning to my main menu and going back to report menu does not help. Exiting database several times does not help. I can go back into now after several minutes may be not get the error once but going back a second time gets the error. What do I need to do to clear up this error. Thanks for any help.
0
 
LVL 84
ID: 40576066
I would suggest you make a backup of the database, and then perform maintenance:

1. Compact the database.
2. Compile your code - from the VBA Editor click Debug - Compile. Fix any errors, and continue doing this until the menu item is disabled.
3. Compact again.

You sometimes need to Decompile your database. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that shortcut, then do the 3 steps again.

Finally, sometimes it's a good idea to create a new, blank database and import everything into that new db.

Also be sure your Windows/Office installs are fully up to date.
0
 

Author Comment

by:marlind605
ID: 40577199
Attempting that now Scott. Thanks.
0
 

Author Comment

by:marlind605
ID: 40577394
When you create a pdf file using
DoCmd.OutputTo acOutputReport, "TotalbyOrganization", "PDFFormat(*.pdf)", myfilename, True, "", , 

Open in new window

Does this line check to see if the file already exists?
After following the decompile routine Scott suggested I followed the same logic about opening the report and closing the report and said what if it is seeing the PDF file and giving that error message. I found the following code to delete the file
If Dir(myfilename) <> "" Then
     Kill (myfilename)
End If

Open in new window

This code is now working but not sure if it is part of the proper fix. Does anyone have any input?
0
 

Author Closing Comment

by:marlind605
ID: 40577695
The key to the solution "Probably because the Report is already open." The PDF report existed and it kept giving me that error message. I found this code on experts exchange to delete the existing file If Dir(myfilename) <> "" Then
     Kill (myfilename)
End If
Thank you Scott. No more errors.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now