Report Output - Prompt for a Folder Location

[Pat Hartman]

You can let them enter a file name if they really need to but NOT the way you are doing it.

You concatenate the name of the directory chosen by the picker with the file name:

Me.FullName = Me.txtDirectory & "\" & "Invoice_" & Me.CustShortName & "_" & Format(Me.InvoiceDT, "yyyymmdd") &".pdf"

Then use Me.FullName in the OutputTo.

Instead of using "Invoice_", you can let them choose the file name but I'm not sure why you think this is a good idea. Users are notorious for using bizarre names and poor directory structures. Therefore, I almost always control both within reason.

For invoices, for example, I would probably create a structure on the server that organized them. Then I would find a place to define the name of the folder and just use that as the highest level. A lower level might be CustShortName or 202211 for november. Those two would be generated on the fly and if the subfolders didn't already exist, I would create the subfolders.

 

[autumnnew]

EUREKA, I finally got it using PSSMargaret's code!!! I decided to take a break from this issue and revisit this later, to work on other parts of the db. Every new feature I add takes a ton of time and research, questioning the forums, trial and error, testing, etc etc. and it's exhausting.

Firstly, I think you might have my post mixed up with another. I'm not working with any invoices in my db; I previously stated that I'm exporting Work Orders and that I wanted to export the PDF file with the right Work Order number in the file name, for the sake of preventing potential mistakes made by complacent users. However, I also wanted to give the users the freedom to choose to name the file something else in the unlikely event that they need to. The report is a simple one and the export procedure is just what you would expect in any other program: click a button to export a report, and the file name is already generated in the filename box. MOST users would accept the file name and choose where to save. But some users might prefer to save with another name and that's okay too, I just didn't know how to provide that ability.

Below is the code I went with. Using PSSMargaret's code, I defined a new variable Dim fso As New FileSystemObject and added the line strFileName = fso.GetFileName(Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)) to get the file name from path set by the user. I also had to turn on a new reference in VBA editor (Microsoft Scripting Runtime) in order for this to work.

Here is the link where I found this method: https://stackoverflow.com/questions/25006283/get-only-the-file-name-from-ms-access-file-browse

Private Sub btnPDFWorkOrder_Click()
    Dim strReport As String
    Dim strWhere As String
    Dim strFileName As String
    Dim intChoice As Integer
    Dim fso As New FileSystemObject

    strReport = "rptWorkOrder"
    strWhere = filterByTmpWorkOrder    'this is a global variable defined when the db opens.
    strFileName = Me.[Project Number] & "-" & Me.[Work Order Number] & " Work Order" & ".pdf"

    DoCmd.OpenReport strReport, acViewPreview, , strWhere, acHidden

   'Insert the file name in the File Dialog window
    Application.FileDialog(msoFileDialogSaveAs).InitialFileName = strFileName

   'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogSaveAs).Show

   'determine what choice the user made
    If intChoice <> 0 Then
        strFileName = fso.GetFileName(Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1))
    Else
        DoCmd.Close acReport, strReport
        Exit Sub
    End If

    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, strFileName, False
    DoCmd.Close acReport, strReport
End Sub

 

[Pat Hartman]

Firstly, I think you might have my post mixed up with another. I'm not working with any invoices in my db; I previously stated that I'm exporting Work Orders and that I wanted to export the PDF file with the right Work Order number in the file name, for the sake of preventing potential mistakes made by complacent users.

I wasn't confused. "invoices" was simply an example.
If you are concerned about the user incorrectly changing the name, you should not be giving them the option. If you don't need the dialog to navigate to the save location, you don't need the dialog at all. It just gives the user the opportunity to mis-file the document.