Optional query filter criteria

[epardo87]

Hello All,

I have a query that needs to be able to filter a single column through two different forms, I'm thinking to use an IIF function in the critaria field based on wether one form is loaded or not. this is the formula I did but it doesn't work

IIf([Forms]![frm22SelectStation].[IsLoaded],[Forms]![frm23StationDetail]!
[List13],[Forms]![frm30UserData]!
[List31])

any suggestions?

thanks for your time!

[ranman256]

This idea fails if both are open.
just make 2 queries. 1 for each form.

[epardo87]

This idea fails if both are open.
just make 2 queries. 1 for each form.

Thanks for your response,

I actually did that, the problem is that I have a series of forms which filter information in cascade as you go from one to another.

• form 1 filters query 1 and opens form 2 which shows the filtered data in query 1
• form 2 filters query 2 and opens form 3 which shows the filtered data in query 2
• form 3 filters query 3 and opens form 4 which shows the filtered data in query 3

now I need that form can 4 filter again query 2 and open form 3 showing the new filtered data in query 2. I need this loop to happen, and by having this conditional I believe the loop is solved. I would add an event so that before form 4 filters query 2, it first closes form 2 and vice-versa, this way the condition would work,Otherwise I'd have to create a lot of alternative queries and forms to open up if they move through it...

I'm open to other alternatives, but the main goal is to have that loop possible.

[aytee111]

When I need a common field I add that field to my main form which is always open. If you don't have a main form that is always open, create one and have it hidden. Then on the subsequent forms in the AfterUpdate I populate the field on the main form. My query will then read that field instead of worrying about which form it came from.

[Bulzie]

Good suggestion aytee111, another variation might be to create a hidden field on each form and pass the criteria value to the new form after you open it and before you close the previous one. Then your info on the current form is based on that field.

Also seems like a lot of forms opening/closing. Is it possible to just have 1 form with multiple criteria for filtering the data?

[epardo87]

Thanks for your answers,

at the end I used only two froms, the firstone with 3 combobox cascades, one listbox, and the second one with a listbox only, using this code I was able to make the forms loop the way I needed:

Private Sub btnStation_Click()
    Dim AreaLookUp As String
      AreaLookUp = DLookup("Area", "Qry-xreference", "StationDescription='" & Forms!frm30UserData!lstStation & "'")
    [Forms]![frm20MainStatus]![cmbArea] = AreaLookUp
    Dim SubLookUp As String
      SubLookUp = DLookup("[Subarea]", "[Qry-xreference]", "[StationDescription]='" & [Forms]![frm30UserData]![lstStation] & "'")
    [Forms]![frm20MainStatus]![cmbSub] = SubLookUp
[Forms]![frm20MainStatus]![cmbStation] = [Forms]![frm30UserData]![lstStation] & ""
[Forms]![frm20MainStatus].Refresh
End Sub