Results 1 to 8 of 8
  1. #1
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    57

    Save report as pdf.

    Hi,

    I have a Button that saves a report 'rpt_R_Review' as a PDF. This was done using the macro builder.

    How do I change the Report name depending on the information from where the report gets its data?

    I would like the file name to be the contents of "cbo_T_Trainee" & "," & "cbo_D_Section" from "frm_R_Review"

    I don't want to specify the save location, but allow the user to choose the location.



    Many Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use vb code instead of the macro...
    Code:
    sub btnPrint_Click()
    dim vFile, vTrain, vSec,vRpt, vDir, vName, vDat
    dim i as integer 
    
    vDat = Format(now(),"yymmdd-hhnn")
    vRpt = "rpt_R_Review
    vTrain = me.cbo_T_Trainee
    vSec = me.cbo_D_Section
    vName = vRpt & "_" & vTrain & "_" & vSec & "_" & vDat & ".pdf"
    vDir = "c:\"                         'default folder
    vFile = vDir & vName
    
    vFile = Save1File (vFile)      'ask user where to save
    i = InStrRev(vFile, "\") 
    vDir = left(vFile,i)
    vFile = vDir & vName
    
       'save new folder 
    docmd.OutputTo acOutputReport ,vRpt,"acFormatPDF",vFile
    
    end sub
    'save this code in a module for user to pick a folder to save to.
    'note: user must enter ANY filename inside the folder to save to. Even if they change the default file name to "xyz", the code will save programmed file name into the folder.

    Code:
    Public Function Save1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogSaveAs)
        .AllowMultiSelect = False
        .Title = "Save to"
        .ButtonName = "SAVE"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        Save1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    57
    Hi,
    Thanks for the quick response,
    I have tried the code and all goes well until hitting the SAVE on the dialogue box. I then get the following error.

    I didn't quite understand the following instruction so this may be the problem.

    'note: user must enter ANY filename inside the folder to save to. Even if they change the default file name to "xyz", the code will save programmed file name into the folder.
    I also have Microsoft Office 15 Object Library referenced as Office 11 is not present.




    Click image for larger version. 

Name:	Error1.png 
Views:	11 
Size:	45.9 KB 
ID:	27386Click image for larger version. 

Name:	Code.png 
Views:	11 
Size:	154.7 KB 
ID:	27385

    Thanks for the help.

  4. #4
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Try:

    Code:
    DoCmd.OutputTo acOutputReport, vRpt, acFormatPDF, vFile

  5. #5
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    57
    Thank you that works fine now.

    New problem now.....

    I use the following code to select the current record displayed on a form to print.

    Code:
    Private Sub Command496_Click()
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "rpt_R_Review", acViewPreview, , "[ReviewID] = " & [ReviewID]
    
    
    End Sub
    When using the code provided by 'Ranman256' the full record set is selected & saved as PDF.

    Is there a way to incorporate the code above to convert only the current record?

    Thanks again.

  6. #6
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    You simply add

    Code:
    DoCmd.OpenReport "rpt_R_Review", acViewPreview, , "[ReviewID] = " & [ReviewID]
    Before

    Code:
    DoCmd.OutputTo acOutputReport, vRpt, acFormatPDF, vFile
    so it looks like

    Code:
    DoCmd.OpenReport vRpt, acViewPreview, , "[ReviewID] = " & [ReviewID]
    DoCmd.OutputTo acOutputReport, , acFormatPDF, vFile

  7. #7
    Brightspark98 is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2016
    Posts
    57
    Excellent,

    Thank you very much.

  8. #8
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    You're welcome. Glad we got it working together.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-15-2015, 08:35 PM
  2. Replies: 3
    Last Post: 06-23-2015, 12:23 PM
  3. Replies: 2
    Last Post: 04-21-2015, 11:33 AM
  4. Report won't Save or Save As
    By DMJ in forum Reports
    Replies: 1
    Last Post: 02-03-2015, 05:46 PM
  5. Replies: 10
    Last Post: 06-20-2012, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums