DoCmd.OpenReport with a Filter??

[eRed]

From eRed the Noobe

I have a report that I am trying to save as a PDF using DoCmd.OutputTo

This code properly saves the report:

DoCmd.OutputTo acOutputReport, "LTCFarNF", acFormatPDF, "M:\ARMGR\Reports\" & Me.ltcfname.Value & Chr(34) & ".pdf", True

However, I need to filter the report. The code below properly lets me preview the report:

DoCmd.OpenReport "LTCFarNF", acViewPreview, , "[LTCFarNF].[ltcfname]=" & Chr(34) & Me.ltcfname.Value & Chr(34)

How can I apply the filter/where to the DoCmd.OutputTo

Thanks for your help!

eRed

 

[John Big Booty]

From here and other reading I believe that you will need to apply your filter to the Report's Record Source prior to calling DoCmd.OutputTo.

 

[eRed]

Thanks for the reply.

I am trying to use the following code but I am missing something.

I get an "run-time error '424' object required" on this line: Set QueryDef = Object.CreateQueryDef("QDLTCFarNF", query)

"some SQL String" is much longer but did seem relevant for this post.

 Dim query As String


      query = "some SQL String"
  

Set QueryDef = Object.CreateQueryDef("QDLTCFarNF", query)
  

DoCmd.OpenQuery "QDLTCFarNF"
  

DoCmd.OutputTo acOutputReport, "LTCFarNF_QD", acFormatPDF, "M:\ARMGR\Reports\" & Me.ltcfname.Value & ".pdf", True

eRed

 

[DCrake]

DoCmd.OpenQuery only relate to action queries. Action queries canot be the underlying recordsoure of a report.

You need a select query with a condition applied to it. Thereby applying the fitler to the report before the report is generated.

 

[vbaInet]

Actually you can use OpenQuery for any kind of query - both Action and Select queries.

Here's a way of getting your report filtered when you use OutputTo:

Declare a variable in a Module (as Public):

Public strRptFilter As String

In the Open event of your report put this:

If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If

In the Close event of the report, remember to reset the variable:

strRptFilter = vbNullString

Now for the OutputTo part:

strRptFilter = "[LTCFarNF].[ltcfname]=" & Chr(34) & Me.ltcfname.Value & Chr(34)
DoCmd.OutputTo acOutputReport, "LTCFarNF", acFormatPDF, "M:\ARMGR\Reports\" & Me.ltcfname.Value & ".pdf", True

NB: I took out the Chr(34) you put in the OutputTo line because a double quote is not a valid character for a file name.

 

[TimTDP]

Hi vbaInet

May I join this discussion?

I have exactly the same problem.
In the reports On Open event I have:
Me.Filter = strRptFilter
Me.FilterOn = True
but the code never runs!

The reports recordsource is a query built into the report. By clicking ... on the right of the recordsource property and then building the query

 

[vbaInet]

TimTDP, there's a reply to your thread already:

http://www.access-programmers.co.uk/forums/showthread.php?t=204524

Follow it up on that.

To answer your question, did you put that variable in a Module? I don't mean the form's module or the report's module.

 

[TimTDP]

Me.Filter = strRptFilter
Me.FilterOn = True

This is in the reports On Open Event

strRptFilter
this is a public variable

Public strRptFilter as string

 

[vbaInet]

As previously stated, follow it up on your thread.

 

[eRed]

vbaInet,

Thank you for the reply. I followed your suggestion and it worked!

eRed

 

[vbaInet]

Happy to hear eRed.