[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2006-04-06
Medium Priority
Last Modified: 2008-02-01
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
Question by:ladycoleen
LVL 26

Expert Comment

ID: 16393735
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

LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 16393742
Run the below code anywhere other than the report (will need some editing of course...)

Dim rpt as Report
DoCmd.OpenReport "YourReportName", acViewDesign, , , acHidden
Set rpt = Reports("YourReportName")

With rpt
  .EditAnything = AnyValue   '<--- Whatever you want to change, do it here.
End With

Docmd.Close acReport, "YourReportName"
Set rpt = Nothing

LVL 27

Expert Comment

ID: 16393743

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 26

Expert Comment

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

Although this is trickiest way to do it.

Author Comment

ID: 16393928
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.
LVL 66

Expert Comment

by:Jim Horn
ID: 16393935
Thanks for the grade.  Good luck with your project.  -Jim

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question