Need to prompt to save file from VBA if file exists

I have a function to create and save a PDF file. The file name is the company name + the date. The creates a "request " file. If two requests are amde for the same day, the first file gets overwritten. How do I prompt the user if the file name already exists?

Function email_binder()
        Dim db As Database
        Dim rs As DAO.Recordset
        Dim MyOutlook As Outlook.Application
        Dim MyMail As Outlook.MailItem
        Dim address As String
        Dim EmailTo As String
        Dim EmailCc As String
        Dim EmailSubject As String
        Dim EmailMsg As String
        Dim EmailAttach As String
        Dim DisplayEmail As Boolean
        Dim spar1 As String
        Dim spar2 As String
        Dim spar3 As String
        Dim spar4 As String
        Dim Cust As String
        Dim strAttachment As String
        Dim strReport As String
        On Error Resume Next
        Cust = Form_Client.Text82
        address = Form_Client.Text115
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT * " & _
                                "FROM info " & _
                                "WHERE id = 1")
       
         strAttachment = Application.CurrentProject.Path & "\Requests\Request " & Cust _
        & " " & Format(Date, "m-d-yyyy") & ".pdf"

   
        Debug.Print "Attachment file name and path: " & strAttachment
        strReport = "rpt_request"
        DoCmd.OutputTo objecttype:=acOutputReport, _
        objectname:=strReport, _
        outputformat:=acFormatPDF, _
        outputfile:=strAttachment

End function
There is more code for emailing the file, but this is the creation of the file.
dgravittAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
If dir(strAttachment) <> "" then
' file exists
0
dgravittAuthor Commented:
So, if file exists, how would you pop up the save as box?
0
MacroShadowCommented:
strAttachment = Application.CurrentProject.Path & "\Requests\Request " & Cust _
        & " " & Format(Date, "m-d-yyyy") & ".pdf"
If dir(strAttachment) = "" then
    strAttachment  = SaveAsDialog()
end if

Function SaveAsDialog() As String
    Dim dlgSaveAs As Object
    Set dlgSaveAs = Application.FileDialog(2)
    With dlgSaveAs
        .InitialFileName = "C:\docs\this is the default name"
        .Show
    End With
    SaveAsDialog = dlgSaveAs.SelectedItems(1)
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dgravittAuthor Commented:
Thanks, just what I needed!
0
MacroShadowCommented:
Glad to help :)
0
dgravittAuthor Commented:
Just encountered an issue. This code works fine in Access 2010, but on an Access 2016 machine, it gives an error "Access has stopped working" and closes the application. Any ideas?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PDF

From novice to tech pro — start learning today.