I need a little help with the syntax here. I'm trying to get a message box to pop-up warning the user that a file already exists and also asks if they want to overwrite it. If they choose no, then the export is cancelled. If they choose yes the file is exported. There is also a message box that pops-up when the file is exported successfully.
I have tried writing this different ways, but not successfully. I would really appreciate some help. I need something like this (only something that works):
Private Sub btn_exportList_Click()
Const existingFilePrompt As String = "This file already exists in folder X:\EADB\Exports\. Do you want to overwrite the file?"
If Dir("X:\EADB\Exports\Agent_List_" & Format(Date, "yyyymmdd") & ".xlsx") <> "" Then
MsgBox(existingFilePrompt, vbQuestion + vbYesNo) = vbNo
Cancel = True
DoCmd.OutputTo acOutputForm, "agencyListTblForm", acFormatXLSX, "F:\EADB\Exports\Agent_List_" & Format(Date, "yyyymmdd") & ".xlsx"
MsgBox ("Successful Export"), vbInformation, "Transfer Complete"