Prevent a pdf file from being generated if no transactions

Posted on 2011-04-29
Last Modified: 2013-11-18
I have a database that is currently generating a pdf file based on query criteria, the query is set to only generate records with transactions.  This pdf file is automatically set to be stored in another folder.  See what I think is the code for this procedure.  

I need to prevent a report from being generated and stored in this folder if it is contains no transactions.

    DoCmd.OutputTo acOutputReport, "Peter Marathon - PF Plan",
      "*.pdf", Me.[txtExportFilename] & " Peter Marathon - PF Plan

Any help is appreciated.
Question by:Delores_C
    LVL 77

    Expert Comment

    You need to count the records in your query before generating the pdf.

    If dcount("*", "querynamehere") > 0 then
     DoCmd.OutputTo acOutputReport, "Peter Marathon - PF Plan", "*.pdf", Me.[txtExportFilename] & " Peter Marathon - PF Plan
    ' do nothing
    end if

    Author Comment

    How do I create an independent pdf file without creating a report for each record name?
    LVL 14

    Expert Comment

    You would need an outer loop to go thru the "parent" information , then look at the transactions associated to it.  

    set rs1 = currentdb.openrecordset("Select CompID, CompnayName from Companies")
    while not rs1.eof
        ssql = "Select * From ReportQueryName where CompID = " & rs1!CompID
        set rs2 = currentdb.openrecordset(ssql)
        if not rs2.eof then
             DoCmd.OutputTo acOutputReport, rs1!CompanyName & " - PF Plan", "*.pdf", Me.[txtExportFilename] & rs1!CompanyName & " - PF Plan "
        end if

    Author Comment

    please help,

    how do I create a outer loop, my knowledge is limited,

    Are you saying to create table one with all the information about the records in it,
    and then in the transaction table, link it to table 1 by the unique field,
    and then create a command button with the example you have given?
    LVL 84
    You already have a table with Transactions in it, I would assume. If so, then the suggestions of the other Experts will provide you with code to determine if you have any transactions. As suggested earlier, the simplest way is to use DCount, with the correct criteria, to do this.

    I would strongly suggest that you take the time to learn the basics of Access before getting too much further into your project. REview some of the sample files, etc etc to see how to do basic things like build a recordset, print reports, etc etc.
    LVL 74

    Accepted Solution

    Basically, you need to loop through a collection of Main records that have transactions only.

    For example, this query will generate a list of Customers who have at least one Transactions:

    SELECT Customers.CustomerID
    FROM Customers INNER JOIN Orders ON Customers.CustomerID = Transactions.CustomerID
    GROUP BY Customers.CustomerID;

    ...Or you could simply pull a unique list of Customers from the Transaction Table:
    SELECT Transactions.CustomerID
    FROM Transactions
    GROUP BY Transactions.CustomerID

    So in your case, code *roughly* like this will generate PDFs, only if the Customer has Transactions:

    Dim strReportName As String
    Dim rst As DAO.Recordset
    strReportName = "Peter Marathon - PF Plan"
    Set rst = CurrentDb.OpenRecordset("SELECT Transactions.CustomerID FROM Transactions GROUP BY Transactions.CustomerID")

    Do While Not rst.EOF
        DoCmd.OpenReport strReportName, acViewPreview, , "CustomerID=" & rst!CustomerID, acHidden
        DoCmd.OutputTo acOutputReport, strReportName, "*.pdf", Me.[txtExportFilename] & " Peter Marathon - PF Plan" & ".pdf"
        DoCmd.Close acReport, strReportName

    Set rst = Nothing

    I am not quite sure if the Report Name: Peter Marathon - PF Plan a filter here or just a name, (or even if it is relevant to this question)
    I am also not quite sure if: Me.[txtExportFilename]
    ...changes for each record, (or even if it is relevant to this question)

    ...but this, coupled with the other great Expert Posts should help you get what you are looking for.



    Sample attached

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Thanks, but as I stated, my post also acknowledged the contributions of the other experts.

    You can click the "Request Attention" link and request that the points be divide equally among all of us.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now