Applying a filter to DoCmd.OpenReport

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to provide the user the capability, from menu choices, to print either
one page of a mailing label report or the entire report. I have made a second
query for the report that produces only the top 30 records (one page). I am
using the following code to produce the full report:
DoCmd.OpenReport "rptStickerLabels", acViewNormal, "qryStickersMail"
When I substitue the following, the report still prints out in its entirety:
DoCmd.OpenReport "rptStickerLabels", acViewNormal, "qryStickersTestPrint"
The query in the last line is the same as the first except that it calls for
the top 30 records.
How can I make this work? Or is there a better way?
Thak you for your considration.
 
Generally it's better to use the "Where" argument (the fourth argument) of
the DoCmd.OpenReport method for providing a filter to the report. Base the
report on a query that returns all records, then you can pass a string that
represents the WHERE clause of an SQL statement to the report in that
argument.

DoCmd.OpenReport "ReportName", acViewNormal, , "FieldName=SomeValue"

See Help file for more info, or post back with questions.
 
Not for an OpenReport action, sorry.

In that case, if you're using ACCESS 2002 or later version, you can pass the
name of the query that you want to use by putting the name in the OpenArgs
argument; then use Open event of report to read that value and set the
report's RecordSource to that query.

DoCmd.OpenReport "ReportName", acViewNormal, , , , "NameOfQuery"


The code in the report's Open event procedure would be this:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then Me.RecordSource = Me.OpenArgs
End Sub
 
Pardon me, but you could also use the third argument in versions since at
least Access 97. Then you don't need to do anything in the Report Open
event.

FilterName - A filter that restricts the report's records. You can enter the
name of either an existing query or a filter that was saved as a query.
However, the query must include all the fields in the report you are opening
or have its OutputAllFields property set to Yes.

DoCmd.OpenReport "ReportName", acViewNormal, "NameOfQuery"
 
That is what the OP was trying to do initially, but said it didn't give the
desired results.
 
Now that you've pointed that out I do see the reference. I would really love to
be able to test this out as I can't see how one method would work and the other
won't.

I guess if I get some time at work, I'll try to run up a test.
 
Back
Top