storing a variable and filtering a report from a closed form

Posted on 2011-10-27
Last Modified: 2012-06-22
Hi everyone,

I have a Access 2010 DB based on a MS Sales Pipeline template.  The print button on the form is set to check the form for dirty, save it, close it, and open a report based on the [ID] which was set prior to the form closing.

What I can't understand is why MS coded it this way as I can't see how the variable for the filter gets to the report if the form is closed. I took out the close comman and now the filter is set correctly, however as the forms are pop up, the reports are behind the form.

Can a filter criteria be passed to a report if the form is closed before the report opens?

If the form is Pop Up, can the form be made to fall back when the report is run?

In general, what is the best way to handle reports arising from reports and how to handle the windows.  I didn't see a window mode...only pop up.

Question by:MCinOH
    LVL 61

    Expert Comment

    If you set the criteria in your open report statement, the form does not need to be open when the report runs.  Like this:

    Docmd.OpenReport "yourReportName", acviewpreview,, "[ID] = " & me.ID
    LVL 61

    Expert Comment

    In otherwords, set the criteria in the OpenRport statement, rather than the report's recordsource.

    Author Comment

    I agree.  What you describe is how the form is set up now in the Macro.

    I select the record, click print and this is how the Macro runs.

    Close the Form.

    UserInterfaceMacro For="cmdPrint" Event="OnClick"><Statements><Action Name="CloseWindow"/></Statements></UserInterfaceMacro></UserInterfaceMacros>

    Open the report and set the filter.

    cmdPrint" Event="OnClick"><Statements><Action Collapsed="true" Name="OpenReport"><Argument Name="ReportName">Opportunity Details</Argument><Argument Name="View">Print Preview</Argument><Argument Name="WhereCondition">="[ID]=" &amp; [ID

    I just can't get this to work when the form is closed.  If I remove the close statement from the Macro, the report filters correctly.   How is the [id] filter criteria passed to the report if the form is closed?
    LVL 61

    Accepted Solution

    Reverse the order of the statements.

    First open the report
    Then close the form (the form does not need to remain open if the report has no references to it)

    In VBA, if would look like this:

    Docmd.Open report "rptReportName" ...
    Docmd.Close acform, me.Name

    The form closure is immediate, so the visual effect should be what you are looking for.

    Author Closing Comment

    I figured out the same thing. I was so set on the fact the MS must know something I didn't about passing criteria to reports that I didn't challenge it. Thanks again.
    LVL 61

    Expert Comment

    Ha!  Don't sell yourself short like that.

    After playing around with a few sample DBs and templates like that, you'll find that they do often teach you good table design, but that the UIs are frequently thrown together quickly for the purpose of showing you a variety of features while teaching you something about table design.

    The best/most efficient UIs are going to be the ones that you come up with independently after exceeding the limitations of those templates.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    731 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

    18 Experts available now in Live!

    Get 1:1 Help Now