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

Posted on 2006-04-06
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

    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 65

    Accepted Solution

    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


    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

    LVL 28

    Expert Comment

    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

    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 65

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now