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


How to filter a Query from multiple Forms?

Posted on 2008-11-04
Medium Priority
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
  • 2
  • 2
LVL 10

Expert Comment

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

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

ID: 22899694
T1080 -

Sorry that I did nbot get back to you faster.

I will respond tomorrow.

Accepted Solution

T1080 earned 0 total points
ID: 22904772
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

Independent Software Vendors: 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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

834 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