marlind605
asked on
Microsoft Access 2013 Create PDF with File Name to include Date Range
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.d d.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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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;
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.
ASKER
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.
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.
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.
ASKER
Attempting that now Scott. Thanks.
ASKER
When you create a pdf file using
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
DoCmd.OutputTo acOutputReport, "TotalbyOrganization", "PDFFormat(*.pdf)", myfilename, True, "", ,
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
This code is now working but not sure if it is part of the proper fix. Does anyone have any input?
ASKER
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.
Kill (myfilename)
End If
Thank you Scott. No more errors.
ASKER