How to filter a Query from multiple Forms?

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,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
T1080Author Commented:
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.  

T1080 -

Sorry that I did nbot get back to you faster.

I will respond tomorrow.
T1080Author Commented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.