Results 1 to 7 of 7
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Could someone walk me through how to split this sub?

    I have the below code to export a report to PDF via a command on a form. I wanted to split it into a public sub/module and then just call it on the form.

    Admittedly, I've struggled to find a good guide to walk me through that. Could any one supply one or use my below code as an example?

    Thanks



    Code:
    Private Sub btnExportReport_Click()
    On Error GoTo btnExportReport_Click_Err
    
    
        Dim ReportName As String
        Dim criteria As String
        Dim fd As Object
        Dim FileName As String
        Dim k As String
       
        ReportName = "rptExport"
        criteria = "[ComplaintNumber]= " & Me.[ComplaintNumber]
    
    
        Set fd = Application.FileDialog(2)
        FileName = "Exported Report" & Me.ComplaintNumber & ".pdf"
        
        With fd
            .Title = "Save to PDF"
            .InitialFileName = "\Documents\" & FileName
            If .Show = -1 Then
                FileName = fd.SelectedItems(1)
                If InStr(FileName, ".") = 0 Then
                    FileName = FileName & ".pdf"
                ElseIf Right(FileName, 4) <> ".pdf" Then
                    k = InStrRev(FileName, ".") - 1
                    FileName = Left(FileName, k)
                    FileName = FileName & ".pdf"
                End If
            
            DoCmd.OpenReport ReportName, acViewPreview, , criteria, acHidden
            DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
            DoCmd.Close acReport, ReportName, acSaveNo
            
            End If
        
        End With
    
    
        Set fd = Nothing
        
    btnExportReport_Click_Exit:
        Exit Sub
    
    
    btnExportReport_Click_Err:
    
    
        MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
        
        Resume btnExportReport_Click_Exit
        
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd create a function and have it accept the complaint number as an input argument. That way it can be called from anywhere:

    http://www.baldyweb.com/Function.htm

    You'd use the argument instead of the "Me" references.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    It seems that only the complaint number is variable. That is the only thing you need to pass as a parameter to the sub.

    Code:
    Sub ExportReport(Complaint as Variant)
    
    On Error GoTo ExportReport_Err
    
        Dim ReportName As String
        Dim criteria As String
        Dim fd As Object
        Dim FileName As String
        Dim k As String
       
        ReportName = "rptExport"
        criteria = "[ComplaintNumber]= " & Complaint
    
    
        Set fd = Application.FileDialog(2)
        FileName = "Exported Report" & Me.ComplaintNumber & ".pdf"
        
        With fd
            .Title = "Save to PDF"
            .InitialFileName = "\Documents\" & FileName
            If .Show = -1 Then
                FileName = fd.SelectedItems(1)
                If InStr(FileName, ".") = 0 Then
                    FileName = FileName & ".pdf"
                ElseIf Right(FileName, 4) <> ".pdf" Then
                    k = InStrRev(FileName, ".") - 1
                    FileName = Left(FileName, k)
                    FileName = FileName & ".pdf"
                End If
            
                DoCmd.OpenReport ReportName, acViewPreview, , criteria, acHidden
                DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
                DoCmd.Close acReport, ReportName, acSaveNo        
            End If
        
        End With
    
    
        Set fd = Nothing
        
    ExportReport_Exit:
        Exit Sub
    
    
    ExportReport_Err:
        MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"   
        Resume ExportReport_Exit   
    End Sub
    Groeten,

    Peter

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If the report name needs to be specified as well as the complaint:
    As Paul mentioned, the Me. notation won't work for this in a code module.

    Code:
    Public Sub ExportReport(rptname as string, complaint as variant)
    On Error GoTo ExportReport_Err
        Dim ReportName As String
        Dim criteria As String
        Dim fd As Object
        Dim FileName As String
        Dim k As String
       
        ReportName = rptname
        criteria = "[ComplaintNumber]= " & complaint
    
    
        Set fd = Application.FileDialog(2)
        FileName = "Exported Report" & Complaint & ".pdf"
        
        With fd
            .Title = "Save to PDF"
            .InitialFileName = "\Documents\" & FileName
            If .Show = -1 Then
                FileName = fd.SelectedItems(1)
                If InStr(FileName, ".") = 0 Then
                    FileName = FileName & ".pdf"
                ElseIf Right(FileName, 4) <> ".pdf" Then
                    k = InStrRev(FileName, ".") - 1
                    FileName = Left(FileName, k)
                    FileName = FileName & ".pdf"
                End If
            
               DoCmd.OpenReport ReportName, acViewPreview, , criteria, acHidden
               DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName
               DoCmd.Close acReport, ReportName, acSaveNo
            End If
        End With
        Set fd = Nothing
        
    ExportReport_Exit:
        Exit Sub
    ExportReport_Err:
        MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
        Resume ExportReport_Exit
    End Sub

  5. #5
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by davegri View Post
    If the report name needs to be specified as well as the complaint:
    As Paul mentioned, the Me. notation won't work for this in a code module.
    So the ReportName, Criteria, and FileName all can vary. I have this code on probably 40-50 different reports/forms throughout my DB and all three of those elements can vary. Some of the code actually doesn't even have a criteria defined (but all have a ReportName and FileName) This is while simplifying everything has been a goal of mine

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by templeowls View Post
    So the ReportName, Criteria, and FileName all can vary. I have this code on probably 40-50 different reports/forms throughout my DB and all three of those elements can vary. Some of the code actually doesn't even have a criteria defined (but all have a ReportName and FileName) This is while simplifying everything has been a goal of mine
    Don't see any question here, so can't see problem.
    The existing code supports the criteria and reportname arguments. The FileName doesn't need to be passed - it's created via the code in the sub. The file dialog simply allows user to select a folder; the filename is already provided in the dialog.

    From a form:
    Call ExportReport("rptSample01", "TooSour")
    results:
    Click image for larger version. 

Name:	rpt.png 
Views:	13 
Size:	43.6 KB 
ID:	49247

  7. #7
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    So make a function with 3 parameters. One of them will be optional.
    In the function you ask whether the complaint number has been entered. If so, then you use a criterion for the report, otherwise you don't.
    Groeten,

    Peter

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

Similar Threads

  1. Replies: 1
    Last Post: 07-17-2019, 03:13 PM
  2. Replies: 3
    Last Post: 08-01-2017, 01:14 PM
  3. Replies: 3
    Last Post: 11-03-2015, 11:19 PM
  4. Replies: 3
    Last Post: 10-26-2011, 07:02 AM
  5. Add date OnChange Event - A to Z Walk Through
    By JeffG3209 in forum Programming
    Replies: 7
    Last Post: 08-12-2011, 04:40 PM

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