Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2015
    Posts
    3

    Lightbulb Unanswered: How to filter report using combobox on a form

    Hi guys, it's my first time to post here. I'm a self-taught MS Access newbie and such, I'm having trouble with filtering. I created a form that houses a report from a query, which has fields such as Month, Year, Product, and Category. What I would like to do is create several combo boxes that will filter records based on those fields.

    I've been able to do this but my approach is definitely inefficient. Here's what I did so far:

    - I created the 4 comboboxes, and added row sources from the different tables that the query is based upon.
    - In the query, I set as criteria each of the 4 comboboxes corresponding to their fields.

    It works smoothly, as I can filter the four fields in the report when I open the form. The problem is when I include the form in a navigation form, the enter parameter value dialog box pops out. I believe it's because the query should run based on a choice in each combobox, so upon loading the form as a subform, the query is running without initial input. Actually, when I run the query itself, the enter parameter value comes up also.

    I tried to set a default value for the query fields but I don't think that's possible?

    Anyway, I know this approach is really wrong. Also, I don't know how to do VBA, i just copy and paste if i can find a solution on the net, and try to guess through logic.

    Thanks in advance guys!

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Try using 'Filter by Form' option without comboboxes.

    Open the Form with the Data.
    Select the Option: Home - - > Sort & Filter - - > Advanced - - > Filter by Form.
    Select appropriate values from required fields (Each field becomes a Combox Box) to filter and click on the Filter icon on the Toolbar.

    Search in Microsoft Access Help documents for 'Filter by Form' topic for more details.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Jul 2015
    Posts
    3
    Quote Originally Posted by apr pillai View Post
    Try using 'Filter by Form' option without comboboxes.

    Open the Form with the Data.
    Select the Option: Home - - > Sort & Filter - - > Advanced - - > Filter by Form.
    Select appropriate values from required fields (Each field becomes a Combox Box) to filter and click on the Filter icon on the Toolbar.

    Search in Microsoft Access Help documents for 'Filter by Form' topic for more details.
    Sorry but I do want to have comboboxes so users can just select from them and automatically sort the report inside the form.

  4. #4
    Join Date
    Jul 2015
    Posts
    3
    I also want to add that I want the report to load after being filtered inside the form. I know how to actually create a dialogue box that will let me select from a combobox then open the report with the selected filter, but I saw on the Northwind sample database that its possible to create a form with a combobox and a report inside, then choose items off the combobox and the report will be filtered without leaving the same form. As i have mentioned I was able to do this but the enter parameter dialog keeps popping up so i know my approach was wrong. thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •