Link to home
Start Free TrialLog in
Avatar of ladycoleen
ladycoleenFlag for United States of America

asked on

Change filter property of report in VBA without opening it first.

Is there a way to dynamically through VBA change a reports filter property BEFORE opening it or without using the docmd.openreport report, conditions etc. ? Thank you in advance
Avatar of dannywareham
dannywareham
Flag of United Kingdom of Great Britain and Northern Ireland image

You could set teh SQL source before you open.

Dim mySQL as STring
mySQL = "SELECT * FROM blah ...."

DoCmd.OpenReport "ReportName",,,,,,OpenArgs:=mySQL


Then, in the report on open/format

Me.recordsource = mySQL


Idea...?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

You can:
1. Set this in the Report's Record Source query
2. Do an SQL in a Form, then set this as the Report's Record Source
3. Do it "On Format" of the Report

jaffer
Alternately, on open of report:
me.filter = yes
Me.Filter = "somefield = " & InputBox("enter desired value")

Although this is trickiest way to do it.
Avatar of ladycoleen

ASKER

I ended up using a parameterized query for the report using global variables. jimhorn has the best idea, as this code sequences through several parameters, writing the reports in pdf in  specified or dynamically created folders on the network. thanks for all the speedy responses.
Thanks for the grade.  Good luck with your project.  -Jim