Using Combobox to filter table based on a query

I ll like to be able to use a drop down combobox to filter data from a table based on queries.
combobox has values;
combobox:
Report Pending
complex reports

I want that when 'Report Pending' is selected from the dropdown, a filter should apply and the table is filtered based on a query like:
select * from TableA where status not in (Complete','Deferred','Cancelled','Delivered). Same idea applies when 'complex reports'  is selected from drop down.
peterslove53Asked:
Who is Participating?

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

x
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.

Dale FyeOwner, Developing Solutions LLCCommented:
I generally create a table that contains these types of pre-defined filter critieria.  The table might have value like: ID, Title, ReportName, Criteria  and values like:

ID  Title                       ReportName                     Criteria
1    Report Pending   rpt_Report_Pending        [Status] NOT IN ('Complete', 'Deferred', 'Cancelled', 'Delivered')

Then, in the combo box, I include the Title and the criteria, and in the AfterUpdate event, I would open the report like:

Private Sub cbo_Report_AfterUpdate

    Dim strReportName as string
    Dim strCriteria as string
    
    if me.cbo_Report & "" = "" Then Exit Sub

    strReportName = me.cbo_Report.column(1)
    strCriteria = me.cbo_Report.column(2)

    docmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

Open in new window

peterslove53Author Commented:
I am not creating a report. Just filtering data in the table
Dale FyeOwner, Developing Solutions LLCCommented:
OK, so leave the report stuff out and just include the title and the criteria.  If you are filtering the current form, and the combo is in the forms header or footer, you might simply use:

Private sub cbo_YourCombo_AfterUpdate

    if me.cbo_YourCombo & "" = "" then
        strFilter = ""
    Else
        strFilter = me.cbo_YourCombo.column(1)
    endif

    me.filter = strFilter
    me.filteron = (strFilter & "" <> "")

End Sub
Helen FeddemaCommented:
For fancier filtering using combo boxes (including concatenated filters), see my Fancy Filters sample database.  

http://www.helenfeddema.com/Files/accarch129.zip

Here is a screen shot of the form:

Fancy Filters form

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
peterslove53Author Commented:
Excellent work!!!
thank you
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.