Can a rpt name be changed to a name in a table?

J

Johanvdw

New member
Local time
Tomorrow, 00:45
Joined
Jun 23, 2025
Messages
13
1770026589859.png
 
use vba to change the name of the pdf either when it is created or after (use the name function)

I use outlook automation to creat the email and the pdf

Away from my computer at the moment and that does all that so share your vba code for creating the pdf and I can provide a more focused response
 
Last edited:
Here is some code from my first Access database.
This produces a report for the selected ship.
Code: 
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
 
If you're using the SendObject method, I agree with the suggestion of changing the Caption of your report before using it.
 
CJ_London said:
use vba to change the name of the pdf either when it is created or after (use the name function)

I use outlook automation to creat the email and the pdf

Away from my computer at the moment and that does all that so share your vba code for creating the pdf and I can provide a more focused response
Click to expand...
Private Sub cmdDeliveryNote_Click()
10 On Error GoTo cmdDeliveryNote_Click_Err

20 If Me.Dirty = True Then Me.Dirty = False
30 'MsgBox "A copy of the Delivery Note will be saved to the Y Drive Delivery Notes Folder", vbInformation

Dim strDocname As String
Dim strDocname2 As String
Dim strDocname3 As String
Dim strWhere As String
Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim strtxtName As String
Dim strDir As String
Dim O As Outlook.Application
Dim M As Outlook.MailItem

strMsgBody = "Find attached your Delivery Note Details"

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatHTML
.HTMLBody = strMsgBody


End With

Set M = Nothing
Set O = Nothing


40 strDocname = "rptDeliveryNote"
45 strDocname3 = [FSEJobNo]
50 strDocname2 = "rptGatePass"
60 strWhere = "[DeliveryID]=" & Me.DeliveryID
70 strSubject = "Delivery Note: " & Left([FSE], 1) & "C" & [NoteNumber] & " / Job No: " & [FSEJobNo]
80 strToWhom = Nz(Me![ToEmail])
90

95 'strMsgBody = "Find attached your Delivery Note Details"

100 'strDir = "Y:\DATA\DeliveryNotes\" & Format(Date, "yyyymmdd") & " - Del Note No. - " & [NoteNumber] & ".pdf"
101 strDir = "Y:\DATA\DeliveryNotes\" & [NoteNumber] & " " & Format(Date, "yyyymmdd") & ".pdf"
105 'strDir = "Y:\DATA\GatePass\" & Format(Date, "yyyymmdd") & " - GatePass - " & [GatePassNumber] & ".pdf"

110 'DoCmd.OutputTo acOutputReport, "rptDeliveryNote", acFormatPDF, "Y:\DATA\DeliveryNotes\" & Format(Date, "yyyymmdd") & " - Del Note - " & [NoteNumber] & ".pdf", False
111 DoCmd.OutputTo acOutputReport, "rptDeliveryNote", acFormatPDF, "Y:\DATA\DeliveryNotes\" & "Del Note" & [NoteNumber] & " " & Format(Date, "yyyymmdd") & ".pdf", False
115 DoCmd.OutputTo acOutputReport, "rptGatePass", acFormatPDF, "Y:\DATA\GatePass\" & Format(Date, "yyyymmdd") & " - Del Note - " & [NoteNumber] & " - Gate Pass - " & [GatepassNumber] & ".pdf", False


120 Debug.Print strDir
130 DoCmd.OpenReport strDocname, acPreview, , strWhere
140 DoCmd.OpenReport strDocname2, acPreview, , strWhere
145 'DoCmd.OpenReport strDocname3, acPreview, , strWhere

150 DoCmd.SendObject acSendReport, "rptDeliveryNote", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True
151 'DoCmd.SendObject acSendReport, "[FSEJobNo]", acFormatPDF, strToWhom, , , strSubject, strMsgBody, True


cmdDeliveryNote_Click_Exit:
160 Exit Sub

cmdDeliveryNote_Click_Err:
170 MsgBox Error$
180 Resume cmdDeliveryNote_Click_Exit

End Sub
 
The following code is from one of my demo files and outputs the report as a PDF file with a path such as:

C:\Users\kenws\Documents\Databases\InvoicePDF\ACME Flanges\ACME Flanges 20150424134202.pdf

The file name is obtained from the second column of a bound Customer combo box in the current form concatenated with the value of the bound InvoiceNumber control in the form. The invoice number is simply the date/time when the invoice was raised in the format yyyymmddhhnnss.

Code: 
Private Sub cmdPDF_Click()

  On Error GoTo Err_Handler
 
    Const FOLDER_EXISTS = 75
    Const MESSAGE_TEXT1 = "No current invoice."
    Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
    Dim strFullPath As String
    Dim varFolder As Variant

    ' Ensure current record is saved
    If Me.Dirty Then Me.Dirty = False
 
    If Not IsNull(Me.InvoiceNumber) Then
        ' build path to save PDF file
        varFolder = DLookup("Folderpath", "pdfFolder")
        If IsNull(varFolder) Then
            MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
        Else
            ' create folder if does not exist
            varFolder = varFolder & "\" & Me.Customer.Column(1)
            MkDir varFolder
            strFullPath = varFolder & "\" & Me.Customer.Column(1) & " " & Me.InvoiceNumber & ".pdf"
            DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFullPath, True
        End If
    Else
        MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
    End If

Exit_Here:
    Exit Sub
 
Err_Handler:
    Select Case Err.Number
        Case FOLDER_EXISTS
        Resume Next
        Case Else
        MsgBox Err.Description
        Resume Exit_Here
    End Select

End Sub

In the unlikely event of a conflict between two users inserting a new invoice record at exactly the same time, the resulting key violation is handled in the form's Error event procedure with:

Code: 
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const DUPLICATE_KEY = 3399
    Const MESSAGE_TEXT = "The invoice number has been amended due to " & _
        "another user having created an invoice with the same number before you " & _
        "attempted to save this invoice record."
 
    Select Case DataErr
        Case 0
            'no error
        Case DUPLICATE_KEY
            ' anticipated error
            Me.InvoiceNumber = CStr(Val(DMax("InvoiceNumber", "Invoices")) + 1)
            Response = acDataErrContinue
            MsgBox MESSAGE_TEXT, vbInformation, "Warning"
        Case Else
            'unknown Error
            MsgBox Err.Description, vbExclamation, "Error"
    End Select

End Sub
 
Last edited:
Gasman said:
Here is some code from my first Access database.
This produces a report for the selected ship.
Code: 
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
Click to expand...
 
Dear Sir,

If you look at my VBA code above (I had a friend writing this for me), what will you add (or take out for this to happen. I am still learning Access and dont know much (anything)about VBA. The report must chage from "rprtDeliveryNote.pdf" to [FSEJobNo].pdf. Will you help me Please?
 
You seem to want to do two things, (a) save a report's output as a PDF file and (b) email a report as a PDF attachment. For the first you need to build the full path to the folder where the file will be saved, and then concatenate the current FSEJobNo value and the .pdf extension to it as the file name. In the code I posted earlier from one of my demo files, having built the path to the folder and assigned it to a varFolder variable, the relevant lines of code are then:

Code: 
    strFullPath = varFolder & "\" & Me.Customer.Column(1) & " " & Me.InvoiceNumber & ".pdf"
    DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFullPath, True

You can forget about the & Me.Customer.Column(1) and substitute Me.FSEJobNo for Me.InvoiceNumber, assuming FSEJobNo is a control in the current form.

To email a report you don't need to refer to the file you've created. In the demo from which my code is taken, the code to email a report is:

Code: 
    Dim strTo As String
    Dim strSubject As String
    Dim strMessageText As String
    
    Me.Dirty = False
    
    strTo = Me.Email
    strSubject = "Invoice Number " & Me.InvoiceNumber
    strMessageText = Me.Customer.Column(1) & ":" & _
        vbNewLine & vbNewLine & _
        "Your latest invoice is attached." & _
        vbNewLine & vbNewLine & _
        "Customer Accounts Department, Widget Supply Company"


    DoCmd.SendObject ObjectType:=acSendReport, _
        ObjectName:="rptInvoice", _
        OutputFormat:=acFormatPDF, _
        To:=strTo, _
        Subject:=strSubject, _
        MESSAGETEXT:=strMessageText, _
        EditMessage:=True

In the demo the rptInvoice report is restricted to the current invoice by referencing the InvoiceNumber control in the form as a parameter in the report's RecordSource query:

SQL: 
WHERE Invoices.InvoiceNumber = [Forms]![frmInvoice]![InvoiceNumber];

You'd do the same by referencing the FSEJobNo control as a parameter.
 
You must log in or register to reply here.

Similar threads

R
Importing a spreadsheet to existing table where column names don't match
Replies
15
Views
1,559
gemma-the-husky
gemma-the-husky
E
Output data from tables into a directory or phonebook
Replies
13
Views
760
MarkK
MarkK
T
Solved Link to a query in Access BE?
Replies
7
Views
378
CJ_London
C
2
Auto Fill Last Name and First Name
2
Replies
28
Views
929
LarryE
LarryE
M
Query Field Name based upon Text Box value
2
Replies
29
Views
1,758
Ken Sheridan
Ken Sheridan

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom