Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter Combobox Options based on Checkboxes (Access 2000)

    Hi,

    I have a form that includes a number of check boxes. I would like to filter a drop-down list to include only the options that remain relevant based on the choices made in the check boxes.

    For example, let's say that the check box options are "cars" and "boats".

    If I choose "cars", I want the combo box to only display a list of cars; if I choose "boats", then I want the combo box to only display a list of boats. But I also want the combo box to display both boats and cars if both check boxes are checked.

    I am using check boxes because I want the choices to be saved in the table, which is something I understand you can't do with a list-box in Access 2000.

    Can this be done? If so, can you point me to a resource that explains how this can be achieved?

    Thanks,

    JoeK

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    What should happen if neither check box is ticked? Should the combo box be empty?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Ideally, if the checkboxes are not ticked then the combobox should be empty, causing the checkboxes to be required fields.

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Do you have only the two check boxes you mentioned, or are there more? If so, how many?

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    What I mentioned in my original question was just an example. I actually have 23 checkboxes, but that may increase or decrease by the time I have finished developing this database. Users will be able to tick any combination of checkboxes.

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    I assume that the 23 check boxes correspond to 23 Yes/No fields in the table. That is not the best way to handle it. You should use a different design:

    - Your original table without the Yes/No fields. It should have an ID field that uniquely identifies the records.
    - A table with an ID (autonumber) field and a text field containing the description (boat, car, etc.)
    - A junction table with two fields: one that links to the ID field of the original table, and one that links to the ID field of the new table. Plus additional fields if needed.

    The junction table will list the unique combinations of IDs.

    See <post#=364,203>post 364,203</post#> for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Hans,

    I think that I have succeeded in creating a many-to-many relationship, as you suggested. I still can't figure out how to create a series of check boxes based on this new table structure. Is there another resource for this that you could point me to.

    Thanks,

    JoeK

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    I don't think you should use check boxes. It's a complicated and inflexible design. Use a subform with a combo box instead, as in the example I pointed to.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Thanks. I got it working using combo boxes. Not exactly what the boss wanted, but I guess if it is a more flexible design, he'll be happier in the long run (well, he'll have to because I'm not changing it again!)

    I just have a question about this setup:

    Where is the information on each record stored? I want to create a query to generate statistics on the choices users make, but I can't seem to find where the record/table stores the information for me to build a query from.

    Thanks,

    JoeK

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    In the sample database attached to <post:=364,203>post 364,203</post:>, the "central" table is named Participation. Although at first sight it contains only pairs of numbers, these represent which courses a student takes, or conversely, which students take a course. You can create meaningful queries by combining the Participation table with the Students and Courses tables, joined on the appropriate fields.

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Thanks for your help on this, Hans.

    JoeK

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Hans,

    I thought I had this working (the form works fine), but I think I've really messed this one up on a deeper level.
    1) In the relationships screen the relationships do not have an infinity sign next to them, although they are "one to many" relationships
    2) The options to enforce referential integrity have been grayed out
    3) Multiple tables show in the relationship screen, even though there is only one table with that name (eg: keywords, keywords1, keywords2) and I can't get rid of the extra ones
    4) I cannot create queries based on the data in the joiner table together with either of the two tables it is connected to. I get an error: "Type mismatch in expression"

    I think that I have done something terribly wrong.

    JoeK

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

    Re: Filter Combobox Options based on Checkboxes (Access 2000)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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