Where Condition and Output to PDF file

[FrankT]

Hi there!

My first post!

I would like to print a report file from a database into PDF format.

Currently I am using a macro with a "where condition" to select the particular file which currently outputs to the default printer. Converting the macro to VBA reads as follows:

DoCmd.OpenReport "Receipt - full pay new", acViewNormal, "", "[ConsultID]=[Forms]![Payment Record Type]![ConsultID]"

However, if I do an output to PDF file as below, I can't insert a "where condition" to pick the specific file. Instead, it outputs the whole unfiltered report which is 12,000 pages and 29 MB!

DoCmd.OutputTo acOutputReport,"Receipt - full pay new",acFormatPDF,"C:\Access\ReceiptPDF.pdf"

Any suggestions on how to combine the 2, i.e. have the "where condition" to select the particular report page, but output to a PDF file in a selected directory.

Thanks for your help in advance.

Frank:banghead:

 

[pbaldy]

One way is to use both lines, but change the first to open in Preview mode. After the OutputTo line, close the report.

 

[FrankT]

Thank you, Paul. That works brilliantly!

I have a simple code that now says:

Private Sub PrintPDF_Click()

DoCmd.OpenReport "Receipt - full pay new", acViewPreview, "", "[ConsultID]=[Forms]![Payment Record Type]![ConsultID]"
DoCmd.OutputTo acOutputReport, "Receipt - full pay new", acFormatPDF, "C:\Access\ReceiptPDF.pdf"
DoCmd.Close acReport, "Receipt - full pay new"

End Sub

And it prints just the 1 page PDF!

Thanks again.

Frank

 

[pbaldy]

Happy to help Frank!

 

[bsm2th]

If the report has no results, I am getting a hundred page report. Openreport sees no records and throws an error 2501 which I catch and print a message box asking the user to make sure the month and year are correct. OutputTo on the other hand ignores the where conditions that openreport used and makes a hundred page report of my whole database. This works perfectly if Openreport finds records.

strReport = "Weekly Report"
strQuery = "Weekly Report Query" - Gets all records from visits where there was a visit
strWhere = ([Visits].[visit_month]=6) AND ([Visits].[visit_year]=2020) AND ([Visits].[weekofmonthly]=3) - Only keeps records for the month/year/week wanted
FileName = "c:\Reports\" & strReport & " for Week " & Me.Thisweekbox & " of " & MonthName(Me.datemonth) & " " & Me.dateyear & ".pdf"


Call DoCmd.OpenReport(reportName:=strReport _
, view:=acViewPreview _
, FilterName:=strQuery _
, WhereCondition:=strWhere _
, Windowmode:=acHidden) 'Use acViewNormal to print instead of preview.
DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, FileName
DoCmd.Close acReport, strReport, acSaveNo


Please help!

 

[Gasman]

So do not OuputTo if you get that error?

 

[zeroaccess]

Maybe in the No Data event:

Private Sub Report_NoData(Cancel As Integer)
    Cancel = True
End Sub

 

[Gasman]

Maybe in the No Data event:

Private Sub Report_NoData(Cancel As Integer)
    Cancel = True
End Sub

Is that going to work, as that is what happening now?
The report is not open, so it is opened by the DoCmd.OutputTo ?

 

[zeroaccess]

Is that going to work, as that is what happening now?
The report is not open, so it is opened by the DoCmd.OutputTo ?

It was a reply to:

If the report has no results, I am getting a hundred page report.

 

[strive4peace]

good catch, Gasman

bsm2th, if report is Canceled in the first statement, it will throw an error

   'if an error happens, exit the procedure
   On Error GoTo Proc_Err
    'this will get an error if Cancel=True in the NoData event
    DoCmd.OpenReport …
    'therefore code will exit and this won't run
    DoCmd.OutputTo …

Proc_Exit:
   on error resume next
   Exit Function ' sub
  
Proc_Err:
   Resume Proc_Exit

 

[Gasman]

We are not getting the full story here.?
I just tried with criteria that would produce an empty report and that is exactly what I got, an empty report.?

 

[strive4peace]

Perhaps the Report_NoData event isn't running? Report View doesn't run all VBA. Needs to open in Print Preview.

If there is no data, Report_NoData should cancel opening the report. If it is indeed Print Preview, perhaps property sheet doesn't say [Event Procedure] on On No Data? Or code isn't compiled and saved?