Removing a query filter

Question

I have a Projects form that uses a query to hide projects that are complete. Right now in order for me to view the completed jobs I have a second form and query that is the same but shows all records. Is there something I can do to just remove the filter with the click of a button on the form so I don't need multiple forms? With this I need the form to reset back to hide completed projects once the form is closed and re-opened.

Answer 1

I'd suggest changing your combo box to a multi-select list.  Set its MultiSelect property to 'Simple'.  You'll then be able to select any of the options singly or in combination:

Add a 'Confirm' button to the form to restrict the results to the selected option(s) and in its Click event procedure put code like this, where lstOptions is the name of the list box:

    Dim varItem As Variant

    Dim strOptionList As String

    Dim strCriteria As String

    Dim ctrl As Control

    Set ctrl = Me.lstOptions

    If ctrl.ItemsSelected.Count > 0 Then

        For Each varItem In ctrl.ItemsSelected

            strOptionList = strOptionList & ",""" & ctrl.ItemData(varItem) & """"

        Next varItem

        ' remove leading comma

        strOptionList = Mid(strOptionList, 2)

        strCriteria = "Complete In(" & strOptionList & ")"

        ' restrict form to selected options

        Me.RecordSource = "SELECT * FROM Projects" & _

             " WHERE " & strCriteria & _

             " ORDER BY ProjectNum DESC"

    Else

        ' show all records if no options selected

        Me.RecordSource = "SELECT * FROM Projects" & _

             " ORDER BY ProjectNum DESC"

    End If

BTW are you using a value list as the combo box's RowSource?  If so that's not a good idea as you are hard coding data.  You should have a single column Options table with each option as value in a row and use the table to fill the combo (or list) box's list.

---

Ken Sheridan, Stafford, England

Answer 2

I apologize but I am not sure if this is what you are referring to as my Query SQL...

My Query brings up the projects table info and then there is a criteria that tells it that using the "Complete Field" to only selected the files marked "no" or "Ready to Ship".

I think this may be what you wanted...

SELECT Projects.ProjectID, Projects.ProjectManager, Projects.ProjectNum, Projects.ProjectTitle, Projects.CustomerID, Projects.CompanyContactsID, Projects.SiteName, Projects.SiteAddress, Projects.SiteCity, Projects.SiteContact, Projects.SitePhone, Projects.PONum, Projects.ContAmt, Projects.StartDate, Projects.NumDays, Projects.Status, Projects.InstBegin, Projects.InstFin, Projects.[Shop/InstMeth], Projects.ShopDrwgs, Projects.SiteMeas, Projects.MatOrdrd, Projects.Cutlists, Projects.Shop, Projects.Inst, Projects.Complete, Projects.Invoiced

FROM Projects

WHERE (((Projects.Complete)="No" Or (Projects.Complete)="ready to ship"))

ORDER BY Projects.ProjectNum DESC;

Answer 3

It is not an 'Option Query' but is an object much like a combo but displays like this --

Yes  □   No  □    On Hold  □    Ready to Ship  □  No or Ready to Ship  □  Cancelled   □  All  √ 

 You would pick one.   Post your query SQL.

---

Build a little, test a little.

Answer 4

I have a Combo Box called complete that has 5 choices: "Yes";"No";"On Hold";"Ready to Ship";"Cancelled".

My query is set to only pick up projects that say "No"or "Ready to ship".

How do I create an option Query as you stated and is it something that everyone on the front end can use or do you have to go through the back-end?

Answer 5

You could use an Option Group with selections of All, Open, Complete and requery on change.

What do you have in the records that indicates complete?  Post the SQL of your query.

---

Build a little, test a little.