Results 1 to 15 of 15
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Reports -- don't output reports if query doesn't return records

    Experts:

    I had some great help earlier today with defining a process that allows me to a) select a values or number of values from a listbox and then b) output all reports in batch process based the value(s) selected in the listbox.

    This process works great!!!!

    However, I am opening this new thread as follow-up w/ some additional tweaking that may be required.

    Here's the process with a few more details:


    - The listbox's data source is linked to another table which include a larger list of organization values.
    - For example, I may have 25 org values in the listbox
    - Now, I can simply hightlight all 25 org values and then click "Extract Reports"
    - At this time, the VBA generated 25 separate reports (one after the other... in a few seconds)
    - However, in some instances, there may be a few organization which don't have any records that match the query.
    - Thus, I have a few reports that are empty and show "#Type!" in the report header which shows the organization's name


    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdExportReport_Click()
    
        'Declare variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
    
        Dim ReportPath As String
        Dim ReportPathMsgBox As String
        Dim ReportFileName As String
        Dim OutputPathFileName As String
    
        'Get the database and stored query
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("Q201_CFT_Ownership_Report_Ncode_Gonzales_RPT")
    
        'Reports are saved to the below file path -- upon change, ensure to update both **actual report path** AND **message box report path**
        ReportPath = "C:\Users\dellc\Reports\CFT Ownership\CFT Ownership Report - "
        ReportPathMsgBox = "C:\Users\dellc\Reports\CFT Ownership"
       
        'Loop through the selected items in the list box and build a text string
        If Me!lstCFTOwners.ItemsSelected.Count > 0 Then
            For Each varItem In Me!lstCFTOwners.ItemsSelected
                strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstCFTOwners.ItemData(varItem) & "'"
    
                'Build the new SQL statement incorporating the string
                 strSQL = "SELECT T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, " & _
                          "T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, Count(T00_JunctionTable_BCFT.BilletIDfk) AS NoParticipants, " & _
                          "T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
                          "T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, " & _
                          "NCode_Group([N_Code]) AS NCode_Group FROM (T01_StaffMembers LEFT JOIN T99_Lookup_RankTitle ON T01_StaffMembers.All_RankTitle = T99_Lookup_RankTitle.RankTitle) " & _
                          "RIGHT JOIN ((T99_SortingNCodes INNER JOIN T11_CrossFunctionalTeam ON T99_SortingNCodes.[NCode] = T11_CrossFunctionalTeam.CFT_Owner) " & _
                          "INNER JOIN (T01_Organization RIGHT JOIN ((T01_Billets LEFT JOIN T00_JunctionTable_OBS ON T01_Billets.BilletIDpk = T00_JunctionTable_OBS.BilletIDfk) " & _
                          "INNER JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T01_Organization.OrganizationIDpk = T00_JunctionTable_OBS.OrganizationIDfk) " & _
                          "ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk) ON T01_StaffMembers.StaffMemberIDpk = T00_JunctionTable_OBS.StaffMemberIDfk " & _
                          "GROUP BY T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Owner, T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, " & _
                          "T11_CrossFunctionalTeam.CFT_Description, T99_Lookup_RankTitle.SortIDGroupOther, T00_JunctionTable_BCFT.BilletIDfk, T01_Billets.Ra_Billet_Title, T01_Billets.Ra_BIN, " & _
                          "T00_JunctionTable_OBS.StaffMemberIDfk, T01_StaffMembers.All_LastName, T01_StaffMembers.All_RankTitle, T01_StaffMembers.Mil_PRD, NCode_Group([N_Code]) " & _
                          "" & "HAVING " & strCriteria & " " & _
                          "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
    
                 'Apply the new SQL statement to the query
                 qdf.SQL = strSQL
    
                 'Replaces any potential forward slashes in the N-Code (e.g., N2/N39) since "/" cannot be used as part of the filename
                 ReportFileName = Replace(Me!lstCFTOwners.ItemData(varItem), "/", "_") & ".pdf"
                 OutputPathFileName = ReportPath & ReportFileName
    
                 'Outputs file(s) to specified file path
                 DoCmd.OutputTo acOutputReport, "R_CFT_Ownership_Report_Gonzalez", acFormatPDF, OutputPathFileName, False
    
            Next varItem
    
            'Throw message box upon successful extraction of the PDFs
            MsgBox "The CFT Ownership reports were stored at the following location: " & ReportPathMsgBox, vbInformation, "Information"
    
        Else
        
            'Throw message box in the event user did not select any N-Codes prior to report execution
            MsgBox "Please select one or more N-Codes!", vbInformation, "Information"
            
        End If
    
        'Empty the memory
        Set qdf = Nothing
        Set db = Nothing
    
    End Sub

    My question:
    - For those reports where the query did not return any results, I'd like to skip the report generation. How can the code below be modified to skip any reports where there's no data match?

    Thank you,
    EEH

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could check the record count before running the report

    Code:
    if DCount("*","YourQueryName")>0 Then DoCmd.OpenReport "YourReportName"
    or you can use the Report_NoData event to exit the report automatically

    Code:
    Private Sub Report_NoData(Cancel As Integer)
       MsgBox "There is no data for this report ", vbInformation, "No data"
        Cancel = True
       
    End Sub
    
    If you don't want to see the message, disable or remove that line
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Admittedly I didn't follow your other posts much as there seemed to be a lot of feedback anyway. If you're modifying the sql of a stored query and then outputting a report that is based on that query (but the report has no records based on that query) then I guess you cannot use the report NoData event because you're not actually opening the report. This seems to leave creating a recordset based on the query and counting the records that the recordset contains. If 0, don't output the report. By the looks of it, don't output a pdf either?
    EDIT - forgot about DCount on the query...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    isladogs -- your ** IF DCOUNT ** works great! Thank you for providing a simple yet powerful solution.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Oh btw, I have one follow-up question.

    In some cases, the VBA outputs 20+ reports (within a few seconds). While the code is looping through each potential report to be printed into PDF, I noticed the print output dialogue for **each** report box is flickering very rapidly in the background.

    Given it adds no value combined w/ the many # of reports being output, I'd like to suppress this print dialogue box. I believe it is controlled by Windows (not by Access)... that said, do you know of an elegant (and simple) solution how this dialogue box can be suppressed?

    Thank you
    EEH

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    To suppress unwanted screen updating, try adding this line at the start of the loop
    Code:
    Application.Echo False
    At the end of the loop, add
    Code:
    Application.Echo True
    I would then follow that with something like
    Code:
    MsgBox N & " reports have been saved as PDF files, vbInformation, "Procedure completed"
    where N is an integer for the number of PDF files created

    You can count the value of N automatically (ignoring any reports with no data) by modifying the DCount code to
    Code:
    If DCount("*","YourQueryName")>0 Then 
        DoCmd.OpenReport "YourReportName"
       N=N+1
    End If
    Obviously you need to add the line
    Code:
    Dim N As Integer
    Hope that helps
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Isladogs - while the application.echo did NOT suppress the Print dialogue boxes, adding the report counter itself is very helpful.

    Thank you!!

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    Glad part of it was useful...
    Sorry about the print dialog - I hadn't tried it before posting.
    With hindsight it couldn't work as the print dialog is a Windows system window

    However. this link contains code that is supposed to suppress the print dialog: http://access.mvps.org/access/api/api0037.htm
    I haven't tested this either but the author Dev Ashish is always reliable

    However it involves the use of APIs so if you are using 64-bit Access the API declarations will need modifying using PtrSafe & LongPtr
    I'd be interested to hear whether it works for you.

    Good luck
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Hello - I came across Dev's posting on this topic. Unfortunately, I couldn't get it to work. It's my lack of development expertise.... I 'm sure Dev's solution works fine once properly implemented.

    Cheers

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Did you convert the API declarations for 64-bit?
    What did happen when you tried it?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Honestly, I wasn't really sure how to implement the general solution using other hidden forms. If you know of an implemented example I certainly will give it another try.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If I have time I'll try it & let you know
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you so very much... I appreciate your help.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've got this working in 32-bit Access in an example database.

    Its good but not perfect. There's a brief flicker before the the print dialog is hidden and another later.
    However its probably less annoying than scrolling through the print dialog.

    Unfortunately, the API conversion for 64-bit is proving more complicated than expected.
    For reasons I'm unclear about, it starts creating the PDF in 64-bit then crashes Access.

    As you seem to be using 64-bit, there's little point me uploading it as it stands ...unless you can test it in 32-bit Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    isladogs -- as I'm using 64-bit, the 32-bit solution won't add much value. I truly appreciate you taking at stab at this and solving in in 32-bit. Unless I come up w/ another solution working for my Windows version, I have to put up w/ the 25 x flickering window.

    Again, thank you!
    EEH

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

Similar Threads

  1. Replies: 7
    Last Post: 09-21-2017, 07:30 PM
  2. Replies: 13
    Last Post: 02-08-2017, 04:11 PM
  3. query doesn't return anything for some records
    By dylan_dog in forum Queries
    Replies: 3
    Last Post: 02-08-2012, 09:55 PM
  4. Output multiple reports to one PDF
    By webfactoryuk in forum Access
    Replies: 3
    Last Post: 12-29-2011, 07:19 PM
  5. Query output for forms & reports
    By Dega in forum Forms
    Replies: 2
    Last Post: 08-20-2010, 12:45 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