Report based on "Filter by form" filtered recordset

Posted on 2012-08-21
Last Modified: 2012-09-07
I have a access form that has approximately 35 form elements.  The filter by form element, and the Advanced Filter, allows me to reduce the recordset from the full table to a reduced recordset.

I have a report that has the same query as that form.  What I would like to do is untie the query from the report, and tie the report's recordset come from the form.  If the form's filter function is applied, then I want that recordset to be what goes to the report, not the whole recordset.

Can this be done?
Question by:Evan Cutler
    LVL 61

    Accepted Solution

    If the report's query is the same as the form's, without changing the report's record source, try the following VBA in a command button click event on the form:

    If me.FilterOn = true then
         Docmd.OpenReport "YourReportName", acviewpreview, , Me.Filter
         Docmd.OpenReport "YourReportName", acviewpreview
    End if

    Open in new window

    LVL 9

    Author Closing Comment

    by:Evan Cutler
    That worked perfect.

    now, What I need to do is put the Me.Filter value into a field (which I've done), and then transform it to something human readable.  I'll make that another question for more points.

    Thanks Much.
    LVL 2

    Expert Comment

    by:Chi Is Current
    Well, that's beautiful.  Elegant simplicity!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    759 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

    12 Experts available now in Live!

    Get 1:1 Help Now