Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    Hello,

    I need some help using a combo box on a user form to filter the records.

    I currently have a userform with a combo box that can be used to filter the records that appear on the form. It works great if I want a specific group, but I would also like to have the option to display all records. However, I cannot get the all of the records to display. Please help me determine how I can get all records to display.

    PS: The way that I am filtering is by using the Combo box as the filter criteria on the query and then using the requery function to update the form.

    Thanks,

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    Another way to do it is to provide an option group as shown below.

    Generally I make the "All" option the default, and the combo hidden. In the afterupdate event for the option group I show/hide the combo according to the selected option.
    Attached Images Attached Images
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    How about this:

    Generally, when I want an All selection, I will build the criteria for the combo box (For example, Select Region from tblRegions), then zoom into the results and add Select "(All)" as Region from tblRegions UNION at the top of the query. Then, in code, when the cboRegion="(All)" you can set the FilterOn to False.

    Everything else should work the same.

    HTH,

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    I am having some trouble following the previous two posts. I do most of my work in Excel and am trying to expand into Access. I am attaching a sample of the Database that I need help with the Household Form, which uses the qryHousehold query to populate. I would like for all records to appear when the form is opened but would like the option to use two filters to narrow the records. The filters are Category and Sub-Category.

    Any help will be greatly appreciated.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    I would have expected the sub-categories to be what the name suggests: a subdivision of categories. The combo boxes would be used to narrow down the selection.

    But in your database, there seems to be no relationship, either implicit or explicit, between categories and sub-categories. So it's impossible to know what to do with the combo boxes...

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    When you say you want two filters to select the records, do you want to select one and/or the other then a command button to select the records you wish?
    If that's what you want, it should be fairly straight forward.
    Pat

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    <P ID="edit" class=small>(Edited by patt on 02-Apr-03 10:13. I forgot the DB)</P>What Hans says makes sense (when doesn't it?) in that there is no relationship between Categories and SubCategories.

    I am enclosing your amended database with the following changes.
    1. I changed the unbound combobox cboSubCategory to SELECT from SubCategories and provided an AfterUpdate for it to to q requery of the form, much like you had done with Category).
    2. I deleted the default of "Family" from the combobox control cboCategory.
    3. I changed the form's underlying query (qryHousehold) to:
    SELECT Household.AddressID, Household.Category, Household.HouseholdName, Household.Address, Household.City, Household.StateOrProvince,Household.PostalCode, Household.Country, Household.HomePhone, Household.WorkPhone, Household.WorkExtension, Household.FaxNumber, Household.MobilePhone, Household.Notes, Household.[Sub Category]
    FROM Household
    WHERE (((Household.Category)=[Forms]![Household]![cboCategory]) AND ((Household.[Sub Category])=[Forms]![Household]![ComboSubCategory])) OR (((Household.[Sub Category])=[Forms]![Household]![ComboSubCategory]) AND (([Forms]![Household]![cboCategory]) Is Null)) OR (((Household.Category)=[Forms]![Household]![cboCategory]) AND (([Forms]![Household]![ComboSubCategory]) Is Null)) OR ((([Forms]![Household]![cboCategory]) Is Null) AND (([Forms]![Household]![ComboSubCategory]) Is Null))
    ORDER BY Household.Category DESC , Household.HouseholdName;

    You will notice that this query now tests for a cboCategory (as it used to) and also if the cboCategory is Null, I also put in a test for the ComboSubCategory and also if ComboSubCategory is Null.

    Now when you change anything in either combobox it requeries the form based upon both combo boxes.

    HTH
    Pat
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Combo Box as a Filter on a Form (Access 2000 SR1)

    That is perfect! Exactly what I was looking for.

    Thank You So Much.

Posting Permissions

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