How to filter a Query from multiple Forms?

Posted on 2008-11-04
Last Modified: 2013-11-28
I have a query called qryLoan that I am trying to filter.  My query is filtered based on a form called frmReportGenerator.  The user selects a combo box value (cboINVID) on this form.  So to filter the query I use this code in the query criteria:  [Forms]![frmReportGenerator]![cboINVID].  I would like the ability to filter my query from multiple forms.  For example, my second form is frmViewGenerator and the user can select the combo box value (cboINVID), and I use this code in the query criteria: [Forms]![frmViewGenerator]![cboINVID].  But, the issue I run into is that the qryLoan prompts for both values when both criteria are entered in the query.  I only want the query to prompt for the values based on the form that I'm using, since both forms will not be used at once.  Note I'm trying to do this on a larger scale where I may have 5 different forms that could filter the same query.  What is the best way to approach this?

Thank you,

Question by:T1080
    LVL 10

    Expert Comment

    How about letting one query with the first filter be the underlying recordsource for the second query with the second filter.

    in other words, create a second query that uses form #1's query as the source. In this way, you can add another filter to this query.

    Good Luck!!
    Lenny Gray

    Author Comment

    Let me re-explain my question, as I think your response does not answer my question.  
    I have 2 forms and each form has a combo box.  I want this combo box field to filter my parameter query.  But, I will only be using one form at any given time.  This being the case if I put both combo box form fields as criteria in my query it will prompt for both.  How can I set up the criteria so it will not prompt for both query parameters, but filter for them if values exist?  
    I have attached a word doc with my query and parameters for a visual of my structure.  

    LVL 10

    Expert Comment

    T1080 -

    Sorry that I did nbot get back to you faster.

    I will respond tomorrow.

    Accepted Solution

    The code below works well to create optional parameters.  When the form is open the prompts are used, if the form is not open the default value is "*".  For this to work the statement in the form will need to begin with Like.  

    Function fOptionalParam(strForm As String, strControl As String) As Variant 
    On Error GoTo errHere 
        Const cDefault = "*" 'Default to return if form value not valid 
        fOptionalParam = cDefault 
        If CurrentProject.AllForms(strForm).IsLoaded Then 
            fOptionalParam = Forms(strForm).Controls(strControl).Value 
        End If 
        Exit Function 
        If Err = 2467 Then 
            'Invalid form reference - ignore ?
            MsgBox "Uh oh - Error " & Err.Description 
        End If 
    End Function 

    Open in new window


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 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