Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Prevent a pdf file from being generated if no transactions

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.
1 Solution
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
Delores_CAuthor Commented:
How do I create an independent pdf file without creating a report for each record name?
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Delores_CAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Jeffrey CoachmanCommented:
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
...is 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

Jeffrey CoachmanCommented:
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.



Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now