troubleshooting Question

Need to prompt to save file from VBA if file exists

Avatar of dgravitt
dgravittFlag for United States of America asked on
PDFVBAMicrosoft Access
6 Comments1 Solution450 ViewsLast Modified:
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, _

End function
There is more code for emailing the file, but this is the creation of the file.
Joe Howard

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros