Help with creating a button to export PDF file

[113hans]

Hi guys, wish you all a merry Christmas!
I know on the Print Preview, we can create a PDF file through right click and choose "Export".
But on the Table, I wonder can we make 1 button which automatically does 2 things:
1. Exports the PDF file to a folder on our PC
2. Names the exported file following the information of the clients
I mean the name of the file could be like: "Client name-Age-Phone-Test day.pdf"
Hope someone can help.

 

[Gasman]

Have you even tried to Google?

access export to pdf - Google Search

Plenty of good videos on Access on Youtube.
However here is a little snippet I use in one of my DBs

Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click

    Dim stRptName As String, stParam As String, stLinkCriteria As String, stDBpath As String, stFTPpath As String
    Dim iPreview As Integer, iDialog As Integer, blnPrintIt As Boolean
    
    stDBpath = CurrentProject.Path & "\"
    stFTPpath = stDBpath & "Gazette\"
    iPreview = acViewPreview
    If Me.ChkPreview Then
       ' iPreview = 2
        iDialog = acWindowNormal
    Else
        iDialog = acHidden
    End If
    
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.Value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.Value & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
        
    If Me.ChkPreview Then
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
    Else
        DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
        DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
        DoCmd.Close acReport, stRptName
    End If
    'DoCmd.DeleteObject acReport, stParam

Exit_cmdShip_Click:
    Exit Sub

Err_cmdShip_Click:
    MsgBox Err.Description
    Resume Exit_cmdShip_Click
    
End Sub

 

[113hans]

Yeah but the videos only help with "1. Exports the PDF file to a folder on our PC"
the "2. Names the exported file following the information of the clients" still the unsolved part

 

[113hans]

In fact, the button on the clips just help to save all the records at the same time, not to run the report for every customer individually.

 

[Gasman]

In fact, the button on the clips just help to save all the records at the same time, not to run the report for every customer individually.

It is highly unlikely you are going to find anything that you can 'just use as is'
As well as the videos, there are plenty of links on how to do this.

You need to find something that is close, and adapt it, after understanding how it works.

If you take the time to look at the code I posted the logic is exactly what you want, now you have expanded on what you want, however you still need to understand it and change it to suit your needs.
Even if it does not do exactly what you want, you can still adapt if for your needs.

When anything seems too large a task, break it down into smaller tasks and get each working before moving on to the next.

 

[theDBguy]

But on the Table, I wonder can we make 1 button which automatically does 2 things:
1. Exports the PDF file to a folder on our PC
2. Names the exported file following the information of the clients

I don't know how you would do this at the table level, but you can use a form instead. Are you trying to export a report (you mentioned "print preview")?

 

[GPGeorge]

Spoiler:

Hi guys, wish you all a merry Christmas!
I know on the Print Preview, we can create a PDF file through right click and choose "Export".

But on the Table,

Spoiler:

I wonder can we make 1 button which automatically does 2 things:
1. Exports the PDF file to a folder on our PC
2. Names the exported file following the information of the clients
I mean the name of the file could be like: "Client name-Age-Phone-Test day.pdf"
Hope someone can help.

I agree with theDBGuy. You are probably not going to find a usable way to do this from the table. That's also the wrong approach to using an Access relational database application.

Tables are data storage objects. Period. Users should not be trying to do things like exporting data directly from the tables.

Forms are interface objects. They are specifically designed to support interactions between users and the data in the tables. The appropriate method for using any Access relational database application is to put your code to create the PDF on a form.

Note that I try not to refer to "Access databases", but to "Access relational database applications" or "Access database applications" for precisely that reason. You have an application, which includes both data storage and user interface objects, along with code to automate them.

 

[Pat Hartman]

Here's a snippet that gets its list of who to report on from a listbox in order to print commission reports. It prints two reports for each salesperson.

    FileDir = Forms!frmMain!txtOutputPath      'DLookup("OutputPath", "tblFilePaths", "RecID = 1")
    If Right(FileDir, 1) = "\" Then
    Else
        FileDir = FileDir & "\"
    End If
    rNow = Format(Date, "yyyymmdd")
    tDate = rNow & "_" & Format(sD, "yyyymmdd") & "_" & Format(eD, "yyyymmdd") & "_"
    RecCount = 0
    If iT > 0 Then
        For Each sPD In Me.lstSPD_Billings.ItemsSelected
            RecCount = RecCount + 1
            Me.txtCounter1 = RecCount
            strSPD = Me.lstSPD_Billings.ItemData(sPD)
            Me.txtWorkingRecord = strSPD
                       '''Summary
            stDocName = "rptBillingsWithoutDetails"
            FileName = "BillingsWithoutDetails_" & strSPD
            XFile = FileDir & tDate & FileName & " .pdf"
                DoCmd.OutputTo acOutputReport, "rptBillingsWithoutDetails", acFormatPDF, XFile, False
                    stDocName = "qryGenerateBillingsByUsage"
            FileName = "BillingsDetailsByUsage_" & strSPD
            XFile = FileDir & tDate & FileName & " .xlsx"
                DoCmd.OutputTo acOutputQuery, "qryGenerateBillingsByUsage", acFormatXLSX, XFile, False
            Next sPD
        Set sPD = Nothing
    End If

 

[113hans]

Private Sub Command100_Click()
Dim FileName As String
Dim FilePath As String

FileName = Me.CustomerName
FilePath = "D:\" & FileName & ".pdf"
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, FilePath

This code worked. But I have 2 problems:
-I don't know how to capitalize the first letter of the exported file, I mean if the customer's name is "customer", how the name of the PDF file could be "Customer" or "CUSTOMER"?
-Another problem is when I add the time (Its Data Type in the Table is Date/Time) on the code:

FileName = Me.CustomerName & Me.BuyingDate

I get the error message: Run-time error '2501' The OutputTo action was canceled
Someone could help please.

 

[theDBguy]

Check out the UCase() and Format() functions. When you added the date to the filename, you were introducing illegal characters.

 

[Gasman]

Give your controls meaningful names.
Command100 is not going to mean much in a few days.

 

[113hans]

My sample db

 

[arnelgp]

you have Non-English characters on Hoten field, so it Might not create the file with diacritic characters and you need to format Ngaysieuam field to YYY_MM_DD on your output filename. it will not create a filename with forward slash (/)

 

[113hans]

you have Non-English characters on Hoten field, so it Might not create the file with diacritic characters and you need to format Ngaysieuam field to YYY_MM_DD on your output filename. it will not create a filename with forward slash (/)

I deleted the "Ngaysieuam" in the code, the code still works with the non-English characters, please try to click the button to see that it worked.
Other problems I put in the sample db, hope you take a look if convenient, thank you for your time.

 

[arnelgp]

here try this one.
i created two queries and created also two reports (based on the original report).

 

[Pat Hartman]

-Another problem is when I add the time (Its Data Type in the Table is Date/Time) on the code:

If you notice, my code formatted the date as yyyymmdd. That enables you to sort it.

You cannot use the "/" in a file name and that is what the problem is.

 

[113hans]

here try this one.
i created two queries and created also two reports (based on the original report).

The button worked well.
But it seems the query makes the letter in the table to be capitalized, but the PDF file name still in lowercase, here my result:

How can the PDF file name could be: Nguyễn Thị Vân.pdf or NGUYỄN THỊ VÂN.pdf

 

[arnelgp]

i use UCase(!Hoten) or Ucase(Me.Hoten), see the VBA code of the button.

the text on Hoten field is in lower case that is why it create a lower case filename.
it only appears in Capital since you put "<" on the field's (Hoten) Format property.

 

[113hans]

Always great like you did @arnelgp
I try to make a new folder name "Clients" with the code: MkDir "D:\Clients", the code worked.
But if I want to make a new folder that its name follows the name of the field "Hoten"
like the way of making the name of the pdf file following the name of the field "Hoten"
Can you help me with that please.

 

[arnelgp]

here try this, it will ask for a folder (or you can create new folder).