How can i save a access report to pdf with a variable name and let the user choose the location?

Question

I want to save a access reports as a pdf with a specific name (get this from the form) but that the user still can choose the location where to save it.

I can save the report with the standard name of the report, i can save the report with a specific name of the form but then the word "Filename" isn't in the code anymore and the there is no pop up to were to save From the moment i try to combine a string with the word "filename" i think access does something but i can't read what because it goes away to fast and don't find my file anywhere.

The last coed i try was this and i think i tried every combination but i can't find it Please help :

Private Sub KnPdoductieopdrachtPDF_Click()

Dim strReportName As String
Dim strPathName As String
Dim Locname As String

strReportName = "Productieopdracht" & [Taaknummer] & ".pdf"
strPathName = Filename
Locname = strPathName & strReportName

DoCmd.OutputTo acOutputReport, "Productieopdracht R", acFormatPDF, Locname, False, "", , acExportQualityPrint


End Sub

score 0 · Answer 1 · answered Jul 13 '23 at 09:33

Add a reference to Microsoft Office 16.0 Object Library and use the FileDialogue:

' Open the file dialogue for the user to select a folder.
' Returns the folder name chosen or an empty string if the user cancelled.
'
' 2023-03-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function SelectFolderName( _
    Optional ByVal InitialFolderName As String, _
    Optional ByVal Title As String) _
    As String
    
    Const DefaultTitle      As String = "Select folder"
    
    Dim FileDialog          As FileDialog
    
    Dim FolderName          As String

    Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    If Trim(Title) = "" Then
        Title = DefaultTitle
    End If
    
    FileDialog.Title = Title
    FileDialog.InitialFileName = InitialFolderName
    FileDialog.AllowMultiSelect = False
    FileDialog.Filters.Clear
    FileDialog.Show

    If FileDialog.SelectedItems.Count = 0 Then
        ' User cancelled.
    Else
        FolderName = FileDialog.SelectedItems(1)
    End If
    
    Set FileDialog = Nothing
    
    SelectFolderName = FolderName
    
End Function

Then use the filename to build the full path for the PDF file to save.

where do i put your code ?? Because i tried to put it on the button but then i get errors that the sub has to end above your code, if i put your code above the sub i get error on the sub code for the output and he doesnt recognise i think the foldername . — Tommy, Jul 16 '23 at 12:25

How can i save a access report to pdf with a variable name and let the user choose the location?

1 Answers1