Solved

Access 2013 button to save report to Desktop as PDF and use a field from form as the name

Posted on 2014-09-03
21
2,742 Views
Last Modified: 2014-09-17
Hi all, I have a simple Access 2013 project with a single table.  I have setup an entry form that has a field on it called Job#.  Currently I have a button at the bottom of the form called 'Print Report' which prints the current record on a named report.  What I need to do now is create another button called 'Save' which will pull the Job# off the current form and use that as the name of the file to save, make the file a PDF, and save the file to the Desktop.  I am a novice and have never used it before so any detailed help is greatly appreciated.  I am looking for the VBA code to use to do the Save.  Again, it needs to be saved as whatever the value is in Job# on the current form, on the desktop, and saved as a PDF.  Thank you so much!
0
Comment
Question by:mjchevalier
  • 10
  • 8
  • 3
21 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40301908
I don't like saving files to the desktop.  That just clutters it.  I prefer to have files saved to a directory.  In this example, the form control txtPath contains the path name.  You can either have the user populate the control each time or you can create a table with default directories for each user and populate the field with the default.  Then let the user override the default if he wants a particular file saved else where.  So in the Current event of the form, populate txtPath with a DLookup:

Me.txtPath = Dlookup("defaultPath", "tblDefaults", "UserID = " & Forms!frmLogin!UserID

                If Me.txtPath & "" = "" Then
                    MsgBox "Please select a path.", vbOKOnly
                    Me.cmdBrowse.SetFocus
                    Exit Sub
                End If
                
                strReport = "myreportname"
                strFileName = Me.txtPath & "\" & strReport & "_" & Format(Date, "yyyymmdd") & ".pdf"
                
                DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

Open in new window

0
 

Author Comment

by:mjchevalier
ID: 40301937
Thank you PatHartman, I understand your code for the most part and although I agree with you completely about not saving to the desktop, this specific application requires it to be saved to the desktop because further processing of the file will be made immediately after the Save operation and then the desktop file will be deleted.  Also, can you help me with using a field from the form as the name of the saved file?  The field name on the form is Job#, so the final name of the saved filed should be 'Job# value'.PDF.  Thank you
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40302274
The other problem with saving to the desktop is that you MUST know the user name.  On my computer, my user name is "Remote FoxPro" so the path to my desktop is:

C:\Users\remote foxpro\Desktop
To get the name of the logged in user, you can interrogate the Windows username using the environ() function.

strReport = "Job#" & Me.[Job#]
                strFileName = environ("username") & "\" & strReport & ".pdf"                

Open in new window

PS - including punctuation or embedded spaces in object/column names is poor practice.
0
 

Author Comment

by:mjchevalier
ID: 40302309
Thanks Pat, I am going to give this a shot and get back to you.  I want to make it clear for you what I am trying to accomplish.  The entry form I speak of is set to open in new record mode when the database is opened.  The current print button I have will print whatever has been newly entered into this blank form.  The Job# field value coming from this entry form is what I need to name the report as along with the extendsion of .pdf.  I assume this is how you understand it to work?  Thank you.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40303619
If you are using a string that might contain characters that are forbidden (or just unadvisable) in file names, you can use the function below to strip them out before using them as part of a file name:
Public Function StripFileNameChars(strText As String) As String
'Strips characters not allowed in a file name from a text string
'Created by Helen Feddema 10-15-97
'Modified by Ruud H.G. van Tol 6-18-99
'Modified by Brad Beacham 6-Feb-2005
'Last modified by Helen Feddema 17-Nov-2011

On Error GoTo ErrorHandler

    Dim strTestString As String
    Dim strBadChar As String
    Dim i As Integer
    Dim strStripChars As String

    strStripChars = "/\:@$#-"
    strTestString = strText

    For i = 1 To Len(strStripChars)
        strBadChar = Mid(strStripChars, i, 1)
        strTestString = Replace(strTestString, strBadChar, vbNullString)
    Next

    StripFileNameChars = strTestString

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in StripFileNameChars procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40303625
If you want to get fancy, you could modify this code to replace # with "No", $ with "Dollars", etc.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40304000
Also don't forget to save the current record before printing the report.

DoCmd.RunCommand acCmdSaveRecord
0
 

Author Comment

by:mjchevalier
ID: 40306089
Thanks to all.  Here is what I have so far and unfortunately I am only getting an error message with no save operation at all.  I should mention that the field named Job# in the database is a number type field, not short text.  I think this may have something to do with it.  Here is the code behind the button I am using.  I only get the error message of "There was an error export the report to" and nothing else.

Private Sub SavetoPDF_Click()
On Error GoTo ErrorHandler
Dim strReport As String
Dim strFileName As String

strReport = "Job#" & Me.[Job#]
strFileName = "C:\Users\Zund\Desktop\" & strReport & ".pdf"
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

Exit Sub
ErrorHandler:
MsgBox "There was an error export the report to " & strFileName
End Sub

Thanks in advance for help with this.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40306530
Try removing the # from the file name.

strReport = "Job_" & Me.[Job#]

I never include special characters in file names and I also don't use embedded spaces.  I use the underscore or the dash if I need a separator but mostly, I use CamelCase to separate parts of a name.
0
 

Author Comment

by:mjchevalier
ID: 40306670
How do I do that without messing up the database or existing data?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
ID: 40306813
You don't have to change the names in the database.  You just need to not include the # in the string you are building to use as the file name for the pdf.  So instead of being named Job#1234.pdf.  You want to name it Job_1234.pdf.

If you decide to rename objects, the best tool is Rick Fishers Find and Replace.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40310028
Just replace the # with No in the file name, and use the ! character for the field, like this:
strReport = "Job No " & Me![Job#]
It might be helpful to display strReport in the Immediate Window to make sure it doesn't contain any characters not allowed in a file name.
0
 

Author Comment

by:mjchevalier
ID: 40310764
Thanks for the help but I keep getting the error message?  What did change is now the error message shows the path that I am trying to save the report to.  I am thinking that maybe the problem is because I have created a button for this 'SAVE' operation and put the code above behind the button but there is no code telling what report should be run?  I do have another button at the bottom of the same form which works to print the current data using the 'JobTicket' report and send it to the printer.  Should I add the save code to that 'PRINT' button?  Confused now?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40310907
I looked back at the last piece of code we reviewed and I agree, the report name is missing.
Private Sub SavetoPDF_Click()
 On Error GoTo ErrorHandler
 Dim strReport As String
 Dim strFileName As String

 strReport = "rptYourReportName"
 strFileName = "C:\Users\Zund\Desktop\" & "Job_" & Me.[Job#] & ".pdf"
 DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

 Exit Sub
 ErrorHandler:
 MsgBox "There was an error export the report to " & strFileName
 End Sub

Open in new window

0
 

Author Comment

by:mjchevalier
ID: 40324171
Despite all your help and knowledge, I have somehow managed to screw things up.  I have been able for the report to save correctly but it is saving the wrong record?  It names the pdf correctly and saves it to the desktop without any errors but it pulls a record from the table, not the one that is showing in the form on the screen?  I am clueless on how to fix this now.  Please help, anyone?
0
 

Author Comment

by:mjchevalier
ID: 40324186
Excuse me but it does not save a random record to the pdf, but all the records from the database.  It prints a report for all records and then saves it as a multipage report PDF to the desktop.  Somehow it is not picking up the current record on the screen showing on the input form?  I need the users to enter a record on the form and then press the save button to 'print' the report with the current data showing in the form to a PDF saved on the desktop.  Seems like it is close but missing something to pick up the current record instead of all the records in the database.  Thank you for your help please.
0
 

Author Comment

by:mjchevalier
ID: 40324190
This is my current code:

Private Sub PrintCurrent_Click()
'Print current record
'using JobTicket report
On Error GoTo ErrorHandler
Dim strReport As String
Dim strFileName As String
Me.Dirty = False
If IsNull(Me!ID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "JobTicket", acViewNormal, , "ID =" & Me.ID
strReport = "JobTicket"
strFileName = "C:\Users\Zund\Desktop\" & "Job_" & Me.[Jobnum] & ".pdf"
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName

ErrorHandler:
MsgBox "There was an error exporting the report to " & strFileName
End Sub
0
 

Author Comment

by:mjchevalier
ID: 40324245
The above code will print the JobTicket report out using whatever data is on the screen of the entry form correctly and then gives me an error message saying "There was an error exporting the report to".  I can't seem to get the report to save to the desktop as a PDF with current form data yet the printed report prints with the correct data.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40325453
Unlike the OpenReport and OpenForm methods which support a filter or Where argument, the OutputTo has no such option.  That means that to get the OutputTo to output only specific data, the report MUST use a query with its own selection criteria.

Assuming the report is always printed from the same form, you can refer to the ID field of that form in the query.

Select ...
From ...
Where JobID = Forms!yourform!cboJobID;
0
 

Author Comment

by:mjchevalier
ID: 40328185
Thank you PatHartman.  I have gone into design view on the form and have seen how I can add criteria and then run the query to show the selected data.  I am however not seeing how I can make the query select the current data on the form, which is what I need.  The form is used to enter new data, print it on media, and then save it to the desktop.  My form is actually name 'Job# Form'.  The report is 'JobTicket' and the ID field is named ID.  Can you tell me how to go about this please?  Thank you
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40328211
The criteria would reference the form as I showed in my example.

Where ID = Forms![Job# Form]![ID]

You will probably get intellisense if you enter this in the QBE grid.  So in the Where cell under the ID field, type Forms!  - as you start typing the name of the form, the list narrows.  Of course, this might not work if your object names contain embedded spaces and special characters so you may just have to type them but remember, with non-standard characters in your names you are ALWAYS going to have to use square brackets.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Query Challenge 15 49
Access 2010 3 18
Dcount unique 6 22
Set Listbox Selected Items by Bound Column 6 13
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

757 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

20 Experts available now in Live!

Get 1:1 Help Now