Avatar of dgravitt
dgravitt
Flag for United States of America asked on

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.
PDFVBAMicrosoft Access

Avatar of undefined
Last Comment
dgravitt

8/22/2022 - Mon
Joe Howard

If dir(strAttachment) <> "" then
' file exists
dgravitt

ASKER
So, if file exists, how would you pop up the save as box?
ASKER CERTIFIED SOLUTION
Joe Howard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
dgravitt

ASKER
Thanks, just what I needed!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Joe Howard

Glad to help :)
dgravitt

ASKER
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?