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,

Troy
T1080Asked:
Who is Participating?
 
T1080Connect With a Mentor Author 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 
 
errHere: 
    If Err = 2467 Then 
        'Invalid form reference - ignore ?
    Else 
        MsgBox "Uh oh - Error " & Err.Description 
    End If 
End Function 

Open in new window

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

Query-example.doc
0
 
LennyGrayCommented:
T1080 -

Sorry that I did nbot get back to you faster.

I will respond tomorrow.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.