Issue with export button on a report

[templeowls]

Hi all. I've got a button on a report that allows the user to quickly export the report via PDF for their use outside of Access. It works great but has minor hiccup. The report is linked to a query that uses criteria. Basically its a report that allows the user to show all complaints from a particular customer. The user just has to type in the Customer ID.



The issue is that the user has to put the ID in when opening the report and then after pressing the export button and selecting a file location to save, Access prompts the user again to input the customer ID. It would be ideal for it to not ask on the second time but I can't seem to figure out why its doing that or how to get it to stop.

Here's the code for the button:

Private Sub btnExportReport_Click()    
    Dim reportName As String
    Dim fd As Object
    Dim filename As String
   
    reportName = "rptCustomerID"

    Set fd = Application.FileDialog(2)
    filename = "Customer Complaint History" & ".pdf"
    With fd
        .InitialFileName = "\Documents\" & filename
        .Show
    End With

    DoCmd.OpenReport reportName, acViewPreview, , acHidden
    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub

[pbaldy]

I suspect the OutputTo re-renders the report. Most of us use forms to gather user input. You have more control and it would solve this problem.

[ssanfu]

@templeowls,
Did you study the DoCmd.OutputTo command syntax? It took me a while to understand what was happening (it was a few years ago).

I've cut out the non-necessary lines in your sub for this explanation.

Private Sub btnExportReport_Click()
   
    reportName = "rptCustomerID"

1    DoCmd.OpenReport reportName, acViewPreview, , acHidden
2    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
    DoCmd.Close acReport, reportName, acSaveNo
End Sub

Line 1 - opens the report named "rptCustomerID" hidden. This report is now the active object. Apparently the Report has a parameter query as the report record source, which prompts for the Customer ID.

Line 2 - the Output command - since you have the ObjectName paraneter as "reportName", the Output command re-opens the same report, resulting in the parameter request of the Customer ID (again).


What worked for me is leaving the ObjectName blank as below.

Private Sub btnExportReport_Click()
   
    reportName = "rptCustomerID"

    DoCmd.OpenReport reportName, acViewPreview, , acHidden
              'OutputTo(ObjectType,   ObjectName, OutputFormat, OutputFile
    DoCmd.OutputTo acOutputReport,                 , acFormatPDF, filename   '<<-- ObjectName parameter is blank
    DoCmd.Close acReport, reportName
End Sub

From Help: ObjectName parameter - A string expression that's the valid name of an object of the type selected by the ObjectType argument. If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank



Also......

DoCmd.Close acReport, reportName

The ", acSaveNo" is not needed - it for saving programmatic changes to the design of the object (in this case the report).




Hope I am clear in my explanation....

[pbaldy]

Somehow my eye thought the OpenReport had a wherecondition, thus making it a necessary line. My eye was wrong.

I wouldn't think you'd need anything but the OutputTo line since there's no wherecondition.

[templeowls]

Steve, I gave that a try but no luck. It's still requesting the customer ID after I save the export. Here's the code I used:

Private Sub btnExportReport_Click()        Dim reportName As String
    Dim fd As Object
    Dim filename As String
   
    reportName = "rptCustomerID"


    Set fd = Application.FileDialog(2)
    filename = "Customer Complaint History" & ".pdf"
    With fd
        .InitialFileName = "\Documents" & filename
        .Show
    End With


    DoCmd.OpenReport reportName, acViewPreview, , acHidden
    DoCmd.OutputTo acOutputReport,                 , acFormatPDF, filename
    DoCmd.Close acReport, reportName
End Sub

[pbaldy]

Have you tried deleting the OpenReport and Close lines?

[templeowls]

Have you tried deleting the OpenReport and Close lines?

That worked!! Thank you!

One more minor question...do you know of any way to get the dialog box to inherently 'know' that you're saving a PDF file? I ask cause when manually saving a PDF the dialog box has some unique settings shown. When exporting this report however, the dialog box isn't recognizing that its a pdf. It states file type as "all files"

[pbaldy]

Shot in the dark:

.Filters.Add "PDF files", "*.PDF"

[templeowls]

I added it but am getting '438' error. See below code. No worries though, it's just a small issue

Private Sub btnExportReport_Click()        
    Dim reportName As String    Dim fd As Object
    Dim filename As String
   
    reportName = "rptCustomerID"


    Set fd = Application.FileDialog(2)
    filename = "Customer Complaint History" & ".pdf"
    With fd
        .InitialFileName = "\Documents" & filename
        .Filters.Add "PDF files", "*.PDF"
        .Show
    End With


    DoCmd.OutputTo acOutputReport,                 , acFormatPDF, filename
End Sub

[ssanfu]

After doing some searching, I found out that the msoFileDialogSaveAs dialog does NOT support file filters (msoFileDialogSaveAs=2) See this

This code works for me with the report I created

Private Sub btnExportReport_Click()
    Dim fd As Object
    Dim reportName As String
    Dim filename As String
    Dim k As Integer

    reportName = "rptCustomerID"
    '    reportName = "Report1"  '<<-- my test report

    Set fd = Application.FileDialog(2)   ' 2 = msoFileDialogSaveAs
    filename = "Customer Complaint History" & ".pdf"

    With fd
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            'check for ext errors
            If InStr(filename, ".") = 0 Then   'no extension entered
                filename = filename & ".pdf"
            ElseIf Right(filename, 4) <> ".pdf" Then
                'change the current ext to pdf if ext <> ".pdf"
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".pdf"
            End If

        End If
    End With

    DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename

    MsgBox "Report saved to " & filename

    'clean up
    Set fd = Nothing
End Sub

[pbaldy]

I guess it worked for me because I'm using the file picker (value 3). Curiously, according to this:

https://docs.microsoft.com/en-us/off...ion.FileDialog

the save as option isn't supported in Access:

The msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.

Obviously your test says otherwise.

[pbaldy]

Or I wonder if that means you can't use msoFileDialogSaveAs, but you can use 2.

[ssanfu]

From what I have read at different times, sometimes the MsoFileDialogType enumeration name is not available, but the VALUE (2) will work.

Also in the link I posted:

- The open file dialog will in fact not open any files! It will just allow the user to select files to open. You need to open the files for reading / writing yourself.
- The save file dialog will in fact not save any files! It will just allow the user to select a filename for the file. You need to open the files for reading / writing yourself.
- The msoFileDialogSaveAs dialog does NOT support file filters


I don't use the FileDialog functions enough to remember what I can and can't do. I end up reading and searching for a while until I get something to work.

[templeowls]

Ssanfu...I tried your code but no luck. It still is not considering it a PDF for whatever reason

[ssanfu]

Do you get any errors?
Have you stepped through the code to see the values of the variables?
I created 5 test pdf's no problem.

Maybe post your dB for analysis.....