Adobe PDF Output File directory keeps resetting

[jasdem]

I manage a database with many users. Front end users routinely print a series of reports to pdf and from those pdf's emails are generated. I have continually directed the printing to the correct folder used to pull the attachment, saved and published, then asked users to bring a new copy onto there desktop. However, each time they bring it onto there desktop the directory doesn't stick and I have to go to each users computer and manually direct the output file to the correct folder. How can make this directory stick?





Thanks for your assistance.

J

[ItsMe]

I use VBA to tell Access where to place reports/files on the server. I have never experienced this symptom.

Can you post some code to show how Access produces and saves the PDF? Maybe a screen shot or description of what the user sees when they encounter their problem. I am having a hard time imagining what the user could be experiencing.

[jasdem]

What I have been doing in design view for the report going to page set-up > page > printer > properties and setting the output file to the correct directory on the server. I then back out and save the changes that I made to the report. When I have finished I save and publish the database. When the users put a new version of the database on their desktop the changes don't stick, the output file directory default to their documents folder. This is problematic because the .pdfs need to be warehoused in the correct folder for archiving and also generating the email. All users have me as a trusted publisher, so I don't think that it's that. Here's some code that is driving the form that the report is drawing form :

Private Sub cmdSend_Click()


    Dim strQuery As String
    Dim rcdQuery As DAO.Recordset
   
    Dim intCardholder As Integer
    Dim strCaption As String
    Dim dat48Hour As Date
    Dim strAttachment As String
    Dim strBody As String
    
    DoCmd.RunCommand acCmdSaveRecord
    
    intCardholder = [Forms]![FormMain]![cboCardholder]
    
    strQuery = "SELECT TableCardholder.DBN, TableCardholder.Cardholder_FirstName, TableCardholder.Cardholder_LastName, TableCardholder.Cardholder_Email, TablePCard.Principal_Email, QueryNetwork_Operations.[E-Mail], [TableStaff].[FirstName] & ' ' & [TableStaff].[LastName] AS Auditor, TableStaff.Alias, TableStaff.Fax, TableStaff.Extension " & _
    "FROM QueryNetwork_Operations RIGHT JOIN (TableStaff INNER JOIN (TableCardholder INNER JOIN TablePCard ON TableCardholder.ID_Schools = TablePCard.ID_Schools) ON TableStaff.ID_Staff = TableCardholder.Auditor) ON QueryNetwork_Operations.CFN = TablePCard.Network " & _
    "WHERE (((TableCardholder.ID_Cardholder)=" & intCardholder & "));"
    
    Set rcdQuery = CurrentDb.OpenRecordset(strQuery, dbOpenDynaset)
    
    If rcdQuery.EOF = False Then
        rcdQuery.MoveFirst
       
        strCaption = "Request For Additional Documentation—" & rcdQuery![DBN] & "(" & rcdQuery!Cardholder_LastName & ")"
        
        'Set Application.Printer = Application.Printers("Adobe PDF")
            DoCmd.OpenReport "Report48Hour", acViewDesign, , , acHidden
            Reports!Report48Hour.Caption = strCaption
            DoCmd.OpenReport "Report48Hour", acViewNormal
    
        'Set Application.Printer = Nothing
        
        DoCmd.Close acReport, "Report48Hour", acSaveNo
                            
        DoCmd.OpenReport "Report48Hour", acViewDesign, , , acHidden
        Reports!Report48Hour.Caption = ""
        DoCmd.Close acReport, "Report48Hour", acSaveYes
    
        MsgBox "Click 'OK' once the PDF generates."

[ItsMe]

Because I still use older versions, I have a special function to convert PDF. I don't send to printer.

Let me take a look at what I have. I interact with printers quite a bit in VBA. I just need to find an example of storing the result on the network. I think I have some .prn and .raw examples. Give me a few....

[jasdem]

I noticed that I never had this problem using 2007, since upgrading to 2010 I have had a number of other issues that have come up.

[ItsMe]

Here is an example of code I have to save a file on the server as Text format. The code opens a report, focuses on it, and then outputs it as Text (saving on the server)

'example of directory
strDirectory = "\\ServerName\FolderName\" & gstrContainer & ".pdf"
'example of output code
DoCmd.OpenReport stDocName, acViewPreview, , strFilter, acHidden
DoCmd.SelectObject acReport, stDocName, False
DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, strDirectory
DoCmd.Close acReport, stDocName

Here is what I believe your code would look like. From what I have been reading, the newer version us acFormatPDF. So there is no need to call on a PDF printer object.

DoCmd.OpenReport "Report48Hour", acViewDesign, , , acHidden
Reports!Report48Hour.Caption = strCaption
DoCmd.SelectObject acReport, "Report48Hour", False
DoCmd.OutputTo acOutputReport, "Report48Hour", acFormatPDF, "\\ServerName\FolderName\FileName.pdf", True
DoCmd.Close acReport, "Report48Hour", acSaveNo

Give that a try and see what happens. I use string variables to dynamicaly update the directory. So, depending on what record the user is currently looking at and what report they run, the directory updates and creates a file name unique to the user's report. Then an email program or a hyperlink can retrieve the report at a later time.

[jasdem]

Alright, thanks. It's going to have to wait till tomorrow. I'm still pretty green with VBA so it may take some time for me to figure out what to comment out and where to place this new code. Thanks for all your help today.

[ItsMe]

Probably comment out this

        'Set Application.Printer = Application.Printers("Adobe PDF")
            DoCmd.OpenReport "Report48Hour", acViewDesign, , , acHidden
            Reports!Report48Hour.Caption = strCaption
            DoCmd.OpenReport "Report48Hour", acViewNormal
    
        'Set Application.Printer = Nothing
        
        DoCmd.Close acReport, "Report48Hour", acSaveNo
                            
        DoCmd.OpenReport "Report48Hour", acViewDesign, , , acHidden
        Reports!Report48Hour.Caption = ""
        DoCmd.Close acReport, "Report48Hour", acSaveYes

I don't know what other activity is going on with the form being open and controls triggering events, etc.

Maybe there is a way you can make a copy and test it to a temp folder somewhere.

[ItsMe]

Here is some code that should stand alone. It does not filter out specific records within a report. Maybe you can build a blank DB and test it, fool around with it first. All you need is a report that generates one or two pages from a small table.

Dim strFileName As String
strFileName = "TestFile" 'This will create a PDF named TestFile
Dim strDirectory As String
strDirectory = "\\ServerName\FolderName\TempFolderName\" & strFileName & ".pdf" 'Just replace the server name and folders here
Dim strReportName As String
strReportName = "TestAccessReport" 'This will be the name of your test report
    DoCmd.OpenReport strReportName, acViewPreview, , , acHidden
    DoCmd.SelectObject acReport, strReportName, False
    DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, strDirectory
    DoCmd.Close acReport, strReportName

[jasdem]

I got it to work. I commented out all the code opening the report, changing the name and saving the report and replaced it with one line. The docmd.outputto satisfied everything.

[ItsMe]

That is good to know. If this thread is solved, do you want to update as "Solved"?