Report Export still saves even if dialog box is cancelled

[templeowls]

I've got some code to export a report and it works great, except if the user exits out of the windows dialog box, the report still saves in whichever folder they're in. Any advice anyone can provide to stop this from happening? Thanks!

Private Sub btnExportReport_Click()    
    Dim reportName As String
    Dim criteria As String
    Dim strfolder As String
    Dim strfilename As String
    
    reportName = "rptComplaintLog"
    criteria = "[ComplaintNumber]= " & [Forms]![frmComplaintDetails]![frm2021]![ComplaintNumber]


    Set fd = Application.FileDialog(2)
    filename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.ComplaintDate, "m.d.yyyy") & ".rtf"
    With fd
        .InitialFileName = "F:\Documents" & filename
        .Show
    End With


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

[June7]

If .Show = True Then

Probably need a backslash between Documents and filename.

You have not declared fd variable.

You declare strfilename but use filename.

Need Option Explicit in every module header.

      With fd
            .InitialFileName = "F:\Documents\" & filename
            If .Show = True Then
                'export code here
            End If
      End With

[templeowls]

'export code here

Thank you for the help. I'm confused about what goes in this line

[templeowls]

Okay so I changed it up a bit and its allowing me to exit out of the dialog box without saving, but now it is not enforcing the criteria for whatever reason when I do save it. I'm guessing I have it in the wrong spot but I'm unsure where to change it to.

Private Sub btnExportReport_Click()

    Dim reportName As String
    Dim criteria As String
    Dim fd As Object
    Dim filename As String
   
    reportName = "rptComplaintLog"
    criteria = "[ComplaintNumber]= " & [Forms]![frmComplaintDetails]![frm2021]![ComplaintNumber]


    Set fd = Application.FileDialog(2)
    filename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.ComplaintDate, "m.d.yyyy") & ".rtf"
    
    With fd
        .Title = "Save to RTF"
        .InitialFileName = "F:\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            If InStr(filename, ".") = 0 Then
                filename = filename & ".rtf"
            ElseIf Right(filename, 4) <> ".rtf" Then
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".rtf"
            End If
        
        DoCmd.OutputTo acOutputReport, reportName, acFormatRTF, filename


        MsgBox "Report saved to " & filename
        
        End If
    
    End With


    Set fd = Nothing
End Sub

[June7]

'export code here

would be your OpenReport, OutputTo, Close lines.

Suggest not using commas and periods in file name path - use underscore instead.

Why do you set filename variable with a concatenated string then replace it with selection?

Have you step debugged?

[templeowls]

I inherited this database tbh. I didn't write the code. I'm familiar with Access, but not VBA, so I'm kind of teaching myself.

In terms of the filename and path, the idea is that the file dialog will open in a preset folder with a preset name, but the user can change either if they'd like. If there's duplicity or things that don't make sense in the code, I'm all for changing it.

I was able to figure it out with the below code. The criteria is applied and it allows me to exit the dialog without saving the report. That said, do you see any code that is duplicative or out of place? I want to be as clean as possible. Thanks!

Private Sub btnExportReport_Click()

    Dim reportName As String
    Dim criteria As String
    Dim fd As Object
    Dim filename As String
   
    reportName = "rptComplaintLog"
    criteria = "[ComplaintNumber]= " & [Forms]![frmComplaintDetails]![frm2021]![ComplaintNumber]


    Set fd = Application.FileDialog(2)
    filename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.ComplaintDate, "m.d.yyyy") & ".rtf"
    
    With fd
        .Title = "Save to RTF"
        .InitialFileName = "F:\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            If InStr(filename, ".") = 0 Then
                filename = filename & ".rtf"
            ElseIf Right(filename, 4) <> ".rtf" Then
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".rtf"
            End If
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
        DoCmd.Close acReport, reportName, acSaveNo
        
        End If
    
    End With

    Set fd = Nothing
End Sub

[June7]

Congrats! Glad you got it working. Looks good to me.

[Gicu]

Sorry to ask, but isn't this the same as we seemed to solve in this thread awhile ago?
https://www.access-programmers.co.uk.../#post-1791995

Cheers,

[templeowls]

Sorry to ask, but isn't this the same as we seemed to solve in this thread awhile ago?
https://www.access-programmers.co.uk.../#post-1791995

Cheers,

Similar but different, such as there's no criteria in that other one. There's several different export buttons in this DB and none of them allow for the user to exit it out without saving. Sorry if I did anything wrong

[Gicu]

No worries, nothing wrong , I was just sure I answered something similar recently ...
Cheers,

[templeowls]

No worries, nothing wrong , I was just sure I answered something similar recently ...
Cheers,

I guess one last question I have is, is it possible to alter the criteria line so that it isn't directly calling out the specific form? That way if I use this code in other forms, I don't have to change this line. For instance, I used the below code with the alteration in the criteria but I'm getting an 3464 error on the DocCmd.OpenReport line....I'm assuming due to the criteria. I'm not sure what

Private Sub btnExportReport_Click()

    Dim reportName As String
    Dim criteria As String
    Dim fd As Object
    Dim filename As String
   
    reportName = "rptComplaintLog"
    criteria = "[ComplaintNumber]='" & Nz(Me.[ComplaintNumber], 0) & "'"




    Set fd = Application.FileDialog(2)
    filename = Me.CustomerLastName & ", " & Me.CustomerFirstName & " " & Format(Me.ComplaintDate, "m.d.yyyy") & ".rtf"
    
    With fd
        .Title = "Save to RTF"
        .InitialFileName = "F:\Documents\" & filename
        If .Show = -1 Then
            filename = fd.SelectedItems(1)
            If InStr(filename, ".") = 0 Then
                filename = filename & ".rtf"
            ElseIf Right(filename, 4) <> ".rtf" Then
                k = InStrRev(filename, ".") - 1
                filename = Left(filename, k)
                filename = filename & ".rtf"
            End If
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, filename
        DoCmd.Close acReport, reportName, acSaveNo
        
        End If
    
    End With


    Set fd = Nothing
End Sub

[June7]

Put the code in an independent procedure in a general module and pass arguments to it. Declare as many argument variables as needed for the code to process.

Sub ExportReport(strRpt AS String, strCust AS String, strDate AS String, intNum AS Integer)

Modify code to use those variables instead of form controls.

Then call the procedure from button Click event:

ExportReport("rptComplaintLog", Me.CustomerLastName & ", " & Me.CustomerFirstName, Format(Me.ComplaintDate, "m.d.yyyy", Me.ComplaintNumber)

[templeowls]

Put the code in an independent procedure in a general module and pass arguments to it. Declare as many argument variables as needed for the code to process.

Sub ExportReport(strRpt AS String, strCust AS String, strDate AS String, intNum AS Integer)

Modify code to use those variables instead of form controls.

Then call the procedure from button Click event:

ExportReport("rptComplaintLog", Me.CustomerLastName & ", " & Me.CustomerFirstName, Format(Me.ComplaintDate, "m.d.yyyy", Me.ComplaintNumber)

Is there any way you could show that in an example DB or post the full code here? Apologies; I'm just not very VBA-versed yet and am still learning

[June7]

What exactly did you not understand about my instructions? No better way to learn than by doing. Make attempt then provide your code for analysis if still an issue.

Apparently, ComplaintNumber is actually a text field, so a change in declaration:

Sub ExportReport(strRpt AS String, strCust AS String, strDate AS String, strComp AS String)

Using those variables:

reportName = strRpt
criteria = "[ComplaintNumber]='" & strComp, 0) & "'"

filename = strCust & " " & strDate & ".rtf"

[templeowls]

I'm extremely new to VBA and am very bad at creating my own code. It sounds like you're suggesting to create two whole new sections of code, correct?

My original code above seems to work fine; it's just the alteration to the criteria line is causing the error. It's not possible to just fix that one piece and leave the rest of the code intact?