Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23

    Smile I need help with VBA

    Hello all,
    I need some help with the code below.
    I am trying to use the code to print a report to .pdf (which it does) however it does not ask me for a folder on where to place the output, I had to put the location on the code.
    I would like the user to have the choice of where to save the report. any help with this will be greatly appreciated.

    Code:
     Private Sub CmdPrint_Near_Miss_User_Report_Click()
    Dim stDocName As String
    Me.Refresh
    stDocName = "Near_Miss_Data_Table_Report"
    DoCmd.OpenReport stDocName, acNormal
    DoCmd.OutputTo acReport, stDocName, acFormatPDF, "L:\Near Miss Unsafe Conditions\" & DatePart("yyyy", Date_of_Incident) & " Safety Incidents\Incident Number " & Incident_Number & ".pdf"
    Exit_CmdPrint_Near_Miss_User_Report_Click:
    End Sub


  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You need to open the Browse window first to get the folder name. There are lots of examples out there, such as https://stackoverflow.com/questions/...ms-access-form

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    'call the file dialog. It may need referenced

    Code:
    Private Sub CmdPrint_Near_Miss_User_Report_Click()
    Dim stDocName As String
    Dim vRet, vFile
    
    On Error GoTo errPrt
    
    Me.Refresh
    stDocName = "Near_Miss_Data_Table_Report"
    
    vRet = UserPickFile()
    If vRet <> "" Then
        If InStr(vRet, ".pdf") = 0 Then vRet = vRet & ".pdf"
        vFile = vRet
        DoCmd.OpenReport stDocName, acNormal
        DoCmd.OutputTo acReport, stDocName, acFormatPDF, vFile
    End If
    Exit_CmdPrint_Near_Miss_User_Report_Click:
    Exit Sub
    errPrt:
    MsgBox Err.Description
    End Sub
    
    
    '---------------
    Public Function UserPickFile()
    '---------------
    
    
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    With Application.FileDialog(msoFileDialogSaveAs)       'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    'With Application.FileDialog(msoFileDialogFilePicker)       'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = False
        .Title = "Save report as"
        .ButtonName = "Save"
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
         
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPickFile = Trim(.SelectedItems(1))
    End With
    End Function

  4. #4
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    Thank you so much for all the help. I'll give the Function provide a try.

  5. #5
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    Quote Originally Posted by ranman256 View Post
    'call the file dialog. It may need referenced

    Code:
    Private Sub CmdPrint_Near_Miss_User_Report_Click()
    Dim stDocName As String
    Dim vRet, vFile
    
    On Error GoTo errPrt
    
    Me.Refresh
    stDocName = "Near_Miss_Data_Table_Report"
    
    vRet = UserPickFile()
    If vRet <> "" Then
        If InStr(vRet, ".pdf") = 0 Then vRet = vRet & ".pdf"
        vFile = vRet
        DoCmd.OpenReport stDocName, acNormal
        DoCmd.OutputTo acReport, stDocName, acFormatPDF, vFile
    End If
    Exit_CmdPrint_Near_Miss_User_Report_Click:
    Exit Sub
    errPrt:
    MsgBox Err.Description
    End Sub
    
    
    '---------------
    Public Function UserPickFile()
    '---------------
    
    
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    With Application.FileDialog(msoFileDialogSaveAs)       'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
    'With Application.FileDialog(msoFileDialogFilePicker)       'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
        .AllowMultiSelect = False
        .Title = "Save report as"
        .ButtonName = "Save"
        .InitialFileName = "c:\"
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
         
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPickFile = Trim(.SelectedItems(1))
    End With
    End Function
    Hello, I tried the code and it works just like I expected to work, it prompts for a location to save the pdf file. The only question I have is how do I stop it from printing a paper copy?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Check the arguments of OpenReport. acNormal prints the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    Thanks, I did check the arguments and I made it work. Something that's bothering me though is the fact that when I print the record on the form for example record 101 it prints fine, then I got to enter a new record 102 and when I print, it still prints record 101 any advice that is causing this?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It just prints 101, or it prints all? You may need to filter the report, either via parameter query or:

    BaldyWeb wherecondition
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    Thanks for the quick reply pbaldy, this is how it goes, I enter data in the form, then I print that, this is the code that is behind the print button.
    Code:
     
    Private Sub cmdPrinttoPDF_Click()
    
    Dim strwhere As String
    
    Dim stDocName As String
    
    Dim vRet, vFile
    
    On Error GoTo ErrPrt
    
     
    
     
    
    stDocName = "rpt_Letters_Pass"
    
    vRet = UserPickFile()
    
    If vRet <> "" Then
    
        If InStr(vRet, ".pdf") = 0 Then vRet = vRet & ".pdf"
    
        vFile = vRet
    
        strwhere = "[ID] = " & Me.[ID]
    
        DoCmd.OpenReport stDocName, acViewReport, , , acHidden
    
        DoCmd.OutputTo acReport, stDocName, acFormatPDF, vFile
    
        DoCmd.GoToRecord , , acNewRec
    
    End If
    Then the form goes to new record and I enter the data and when I print, it will not print that new record that I just entered, it prints my previous record.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you haven't said how the report should be restricted. You create a wherecondition but don't use it:

    strwhere = "[ID] = " & Me.[ID]

    You may need to save the record before printing it:

    If Me.Dirty Then Me.Dirty = False
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    sample.zipsample.zip

    I have attached a sample, if anyone would like to take a look.
    thank you guys for all the help.

    Quote Originally Posted by pbaldy View Post
    Well, you haven't said how the report should be restricted. You create a wherecondition but don't use it:

    strwhere = "[ID] = " & Me.[ID]

    You may need to save the record before printing it:

    If Me.Dirty Then Me.Dirty = False

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds to me that the reason is because the report is still open. You don't open a report, see a record, add a record and go back to the report window and expect to see the new record. Your code should reopen the report. IIRC, you don't need to close it first, but I might be wrong about that. Alternatively, the Refresh All ribbon button should be available in print preview (not sure about report view) and you could test this theory before altering code. Click it to see if your new record shows up.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02....

    Why are you opening the report in the first place? You don't need to open the report to print it to the PDF. Since you are opening the report hidden, save yourself some grief and delete "DoCmd.OpenReport stDocName, acViewReport, , , acHidden"


    The top two un-commented lines in EVERY module should be
    Code:
    Option Compare Database
    Option Explicit

    Object naming: "#" is an extremely bad object name. "Date_" is not much better.

  14. #14
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23
    Thank you guys for all the help.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    Why bother with coding the saving to pdf at all. Download CutePDF Writer (it's freeware) and install it. It looks like ordinary printer in your Devices and printers window. Now run your report (or open anything you want to save as PDF), select CutePDF Writer as your printer (when you didn't set it as default one) and click Print button. You are asked for location and file name - make your selections and press OK. It's done!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

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