Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    May 2008
    Location
    London
    Posts
    25

    Unanswered: Combo Box Filter

    I am trying to filter a form using a combo box

    What i have is a form to display mp3 files on a server and i want to have about 10 combo boxes to filter the results, the combo boxes are named selected_type_1 through to selected_type_10 we have a table called mp3s that has 10 fields for the filter to be associated with so we have a mp3 with 10 words that relate to it i want to be able to select a word in the first list and filter the results if the word is listed in any of the 10 fields in the table....

    If it would be possible i was thinking of replacing the combo boxes with a list box and being able select various words and searching all the feilds for each of these words

    i have tried with some of the examples on the forum but not having much luck as i am new to access..

    I have attached a copy of the file if it helps
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    You should make your form unbound and write an SQL query with 10 filters related to each combobox and when the user select the last combobox in its after update event to write code to bound the form Record Source to the a.m query.
    In addition the details (i.e Artist, Voice Type, File and location) must be in a continuous subform to show you all items found by SQL in case it find more than one file.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by colinod
    If it would be possible i was thinking of replacing the combo boxes with a list box and being able select various words and searching all the feilds for each of these words
    From the user's perspective, this is the right approach. Once you have your listbox set up, you'd have a command button such as "Find Now" that would then list all of the MP3s that have any of the words selected.

    However, I don't think the approach of 10 fields the way you have them is the way to go. It would seem to me to be better to have a subform and an additional table to capture the voice type entries. This way you are not looking at 10 fields when you want to enter one word and at the same time you are not limited to just 10 fields. On top of that, it would make this search a fair bit easier.

    Whenever you have fields that are numbered, like VoiceType1, VoiceType2 etc, it always indicates an error in your table design.

    You're new to Access and your first mission is to conquer a many to many relationship and listbox management with some VBA thrown in to boot.

    I wish you luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2008
    Location
    London
    Posts
    25
    What if i did not want to select a word from all of the combo boxes could i have a button to start the query

  5. #5
    Join Date
    May 2008
    Location
    London
    Posts
    25
    StarTrekker

    The reason for the 10 fields numbered like this is that we can have upto 10 words that relate to each voice clip i could not see another way of doing this i now have a form with the combo boxes and a sub form to show the results but am not sure how to get it to work

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What if i did not want to select a word from all of the combo boxes could i have a button to start the query
    Yes, but with the structure you have, it will get complex since you'll have to do a lot of compensating for nulls.

    The way you would do it is create a many-to-many relationship between MP3s and VoiceTypes. For example, an MP3Voice table that consists of the MP3_ID and the VoiceTypeID. The three tables would related together to form a many-to-many relationship. You should study up on your table design and relationships before proceeding further imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is this your first Access database?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    your SQL should be something like this one:
    Code:
    strSQL = "SELECT yourtablename.* FROM yourtablename " _
                    & " WHERE [Document Number] Like '*" & DocNum & "*' " _
                    & " AND [type1] Like '*" & combo1 & "*' " _
                    & " AND [type2] Like '*" & combo2 & "*' " _
                    & " AND [type3] Like '*" & combo3 & "*' " _
                    & " AND [type4] Like '*" & combo4 & "*' " _
                    & " AND [type5] Like '*" & combo5 & "*' " _
                    & " AND [type6] Like '*" & combo6 & "*' " _
                    & " AND [type7] Like '*" & combo7 & "*' " _
                    & " AND [type8] Like '*" & combo8 & "*' " _
                    & " AND [type9] Like '*" & combo9 & "*' " _
                    & " AND [type10] Like '*" & combo10 & "*' "
    type1~10 is your table fields and combo1~10 is your combo boxes value, it won't be a problem if you dont select some of your combos.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  9. #9
    Join Date
    May 2008
    Location
    London
    Posts
    25
    StarTrekker
    Its my first to have a form that needs to filter results

    I understand about the relationships you are saying so will my MP3Voice table have various listings of each mp3 for each voicetype associated with it or one listing for each mp3 with various voice types attached to it???

    Sorry if this sound stupid but i sort of know what you mean

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    One listing for each mp3 with various voice types attached to it.

    For example:

    MP3
    MP3ID (Primary)
    Detail Fields...
    ...
    ..


    MP3Voice
    MP3ID (PartPrimary)
    VoiceType (PartPrimary)


    VoiceType
    VoiceType (Primary)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Aran's method is probably quicker mind you. I'd personally go with a re-structure but the choice is yours
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    May 2008
    Location
    London
    Posts
    25
    I have tried Arans idea and its nearly there....long sql statement that does not seem to reduce the amount of records in the form depending on how many combo boxes are selected. not sure if the sql is correct, if anyone can have a look and tell me it would be appreciated


    SELECT Table1.*
    FROM Table1
    WHERE [VoiceType1] Like selected_type_1 Or [VoiceType2] Like selected_type_1 Or [VoiceType3] Like selected_type_1 Or [VoiceType4] Like selected_type_1 Or [VoiceType5] Like selected_type_1 Or [VoiceType6] Like selected_type_1 Or [VoiceType7] Like selected_type_1 Or [VoiceType8] Like selected_type_1 Or [VoiceType9] Like selected_type_1 Or [VoiceType10] Like selected_type_1 And [VoiceType1] Like selected_type_2 Or [VoiceType2] Like selected_type_2 Or [VoiceType3] Like selected_type_2 Or [VoiceType4] Like selected_type_2 Or [VoiceType5] Like selected_type_2 Or [VoiceType6] Like selected_type_2 Or [VoiceType7] Like selected_type_2 Or [VoiceType8] Like selected_type_2 Or [VoiceType9] Like selected_type_2 Or [VoiceType10] Like selected_type_2 And [VoiceType1] Like selected_type_3 Or [VoiceType2] Like selected_type_3 Or [VoiceType3] Like selected_type_3 Or [VoiceType4] Like selected_type_3 Or [VoiceType5] Like selected_type_3 Or [VoiceType6] Like selected_type_3 Or [VoiceType7] Like selected_type_3 Or [VoiceType8] Like selected_type_3 Or [VoiceType9] Like selected_type_3 Or [VoiceType10] Like selected_type_3 And [VoiceType1] Like selected_type_4 Or [VoiceType2] Like selected_type_4 Or [VoiceType3] Like selected_type_4 Or [VoiceType4] Like selected_type_4 Or [VoiceType5] Like selected_type_4 Or [VoiceType6] Like selected_type_4 Or [VoiceType7] Like selected_type_4 Or [VoiceType8] Like selected_type_4 Or [VoiceType9] Like selected_type_4 Or [VoiceType10] Like selected_type_4 And [VoiceType1] Like selected_type_5 Or [VoiceType2] Like selected_type_5 Or [VoiceType3] Like selected_type_5 Or [VoiceType4] Like selected_type_5 Or [VoiceType5] Like selected_type_5 Or [VoiceType6] Like selected_type_5 Or [VoiceType7] Like selected_type_5 Or [VoiceType8] Like selected_type_5 Or [VoiceType9] Like selected_type_5 Or [VoiceType10] Like selected_type_5 And [VoiceType1] Like selected_type_6 Or [VoiceType2] Like selected_type_6 Or [VoiceType3] Like selected_type_6 Or [VoiceType4] Like selected_type_6 Or [VoiceType5] Like selected_type_6 Or [VoiceType6] Like selected_type_6 Or [VoiceType7] Like selected_type_6 Or [VoiceType8] Like selected_type_6 Or [VoiceType9] Like selected_type_6 Or [VoiceType10] Like selected_type_6 And [VoiceType1] Like selected_type_7 Or [VoiceType2] Like selected_type_7 Or [VoiceType3] Like selected_type_7 Or [VoiceType4] Like selected_type_7 Or [VoiceType5] Like selected_type_7 Or [VoiceType6] Like selected_type_7 Or [VoiceType7] Like selected_type_7 Or [VoiceType8] Like selected_type_7 Or [VoiceType9] Like selected_type_7 Or [VoiceType10] Like selected_type_7 And [VoiceType1] Like selected_type_8 Or [VoiceType2] Like selected_type_8 Or [VoiceType3] Like selected_type_8 Or [VoiceType4] Like selected_type_8 Or [VoiceType5] Like selected_type_8 Or [VoiceType6] Like selected_type_8 Or [VoiceType7] Like selected_type_8 Or [VoiceType8] Like selected_type_8 Or [VoiceType9] Like selected_type_8 Or [VoiceType10] Like selected_type_8 And [VoiceType1] Like selected_type_9 Or [VoiceType2] Like selected_type_9 Or [VoiceType3] Like selected_type_9 Or [VoiceType4] Like selected_type_9 Or [VoiceType5] Like selected_type_9 Or [VoiceType6] Like selected_type_9 Or [VoiceType7] Like selected_type_9 Or [VoiceType8] Like selected_type_9 Or [VoiceType9] Like selected_type_9 Or [VoiceType10] Like selected_type_9 And [VoiceType1] Like selected_type_10 Or [VoiceType2] Like selected_type_10 Or [VoiceType3] Like selected_type_10 Or [VoiceType4] Like selected_type_10 Or [VoiceType5] Like selected_type_10 Or [VoiceType6] Like selected_type_10 Or [VoiceType7] Like selected_type_10 Or [VoiceType8] Like selected_type_10 Or [VoiceType9] Like selected_type_10 Or [VoiceType10] Like selected_type_10;

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    See now I know why I'd go with the structural modification method ^^

    Does it work?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    May 2008
    Location
    London
    Posts
    25
    yes it does work but if i selc something in the forst combo box and get the filtered results then select a second box it does not seem to filter the results i already have it seems to add the results for the second combo box to the first

  15. #15
    Join Date
    May 2008
    Location
    London
    Posts
    25
    StarTrekker
    I now see what you mean and now have a database set up with an mp3 table holding details of the mp3 voicetype with the various words to describe the mp3s and a mp3voice table that has two fields one for the mp3 name and one for the voicetypes to set to it, i gather i would have upto 10 records for each mp3 in this table if i understand, if this is correct i am now struggling with the sql to get my filtered results????

Posting Permissions

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