Results 1 to 14 of 14

Search using a multi select listbox

  1. #1
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22

    Search using a multi select listbox

    Hi all, i hope you all can help me with this!

    i am actually working on a search field that allows users to search for information that they want. i have 1 drop down list(pricePoint) and 3 listbox(businessNature, Concept and Parks) .

    the first listbox(business nature) will show the results in the 2nd listbox(concept), meaning that when i click on example "F&B" in businessNature, Concept Listbox will generate all the fields that are related to "F&B". Which, Concept listbox and parks listbox are multi select listbox, whereas, business nature is not.

    this search field is suppose to help users to search for information that they want by using all the fields provided to narrow down their search fields. However, i cant seem to get the multi select listboxes to work.

    Can you guys help me out with this? i tried many ways and many suggestion from others, but it just cant seems to work. And i have no idea why?

    really will appreciate if you guys can give me a hand! Please!!!



    this is my codes:

    In the Search_btn

    Private Sub SearchBtn_Click()


    DoCmd.Close acQuery, "Query1" 'close the query if not the new records wont appear
    PricePoint.SetFocus 'set focus to businessNature so that value will be assign to text




    If PricePoint.Text = "Above 40" Then
    Me.Tag = 40
    DoCmd.OpenQuery "Query2", , acReadOnly
    Else


    If PricePoint.Text = "Below 10" Then
    Me.Tag = 10
    Else
    If PricePoint.Text = "Below 20" Then
    Me.Tag = 20
    Else
    If PricePoint.Text = "Below 40" Then
    Me.Tag = 40
    Else
    End If
    End If
    End If
    DoCmd.OpenQuery "Query1", , acReadOnly
    End If


    End Sub

    [Basically, pricepoint is to search for the prices that are below $10, $20, $40 or above $40]


    This is my query:

    Below 10,20,40

    SELECT ProspectsTable.PricePoint, ProspectsTable.BusinessNature, *


    FROM ProspectsTable
    WHERE (((ProspectsTable.PricePoint)<=[Forms]![Prospect Table search].[Tag]) AND ((ProspectsTable.BusinessNature)=[Forms]![Prospect Table search]![BusinessNature]));



    Above 40

    SELECT ProspectsTable.PricePoint, ProspectsTable.BusinessNature, *
    FROM ProspectsTable
    WHERE (((ProspectsTable.PricePoint)>=[Forms]![Prospect Table search].[Tag]) AND ((ProspectsTable.BusinessNature)=[Forms]![Prospect Table search]![BusinessNature]));




    Even if i add the listboxes into my query it still doesnt work.


    Anyone out there please help me!!!!!



    Thank you in advance!!!!!



    [attach is my search table, it may give you a clearer pic of what i am talking about. if you are wondering, park listbox is on its on, it does not take any values from any listbox]




    Click image for larger version. 

Name:	1.png 
Views:	21 
Size:	38.0 KB 
ID:	10158

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    For a multi-select list box, you have to loop through the list box and accummulate the selections into a variable and then pass that to the query. I typically build the query in code as well.

    Here is a code snippet that does that:

    Code:
    Dim lngloop as long
    Dim strWhere as string
    Dim strIDs as string
    
        If Me.projtype.ItemsSelected.Count <> 0 Then
            If Me.projtype.ItemsSelected.Count > 0 Then
                For lngLoop = 0 To Me.projtype.ItemsSelected.Count - 1
                  If lngLoop = 0 Then
                    strIDs = strIDs & Me.projtype.ItemData(Me.projtype.ItemsSelected(lngLoop))
                  Else
                    strIDs = strIDs + "," & Me.projtype.ItemData(Me.projtype.ItemsSelected(lngLoop))
                  End If
                Next lngLoop
            End If
            strWhere = strWhere & "tblProjLog.fkProjType in (" & strIDs & ") AND "
            strIDs = ""
        End If
    The list box in the above is called projtype. The bound field of the list box is the primary key of the table which is the row source of the list box. For each item that is selected in the list box, the code will take the key value and put it in the variable called strIDs. The nested IF..ELSE...ENDIF checks to see if the selection is the first selection, if so it puts the value directly in strIDs. Each subsequent time through the loop if a selected item is found, a comma is added and then the value. So you will end up with a string that look like this: 1,3,6

    You would then use the strIDs variable in constructing the WHERE clause of the query (strWhere).

    You would then bring a variable that has the appropriate SELECT & FROM clause and concatenate the strWhere variable to that to create your query.

    You would then use the query as the record source for the form that displays the search results & open the form

  3. #3
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    Hi jzwp11,

    Thanks for taking time to help! but i have a question.


    strWhere = strWhere & "tblProjLog.fkProjType in (" & strIDs & ") AND "


    tblProjLog is the table name where i stored my information? and

    fkProjType is the name of my column that i want to get my values from?

    Am i right? Please correct me if i am wrong.



    "You would then use the query as the record source for the form that displays the search results & open the form"

    Opening up the form means a subform or a new form? If its not a subform, is it possible to use a subform?




  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm sorry that I did not clarify that. tblProjLog would be equivalent to your ProspectsTable and fkProjType would be the field (column) in that table that matches what is in the list box. I use numeric key values rather than text values. If you plan on using text values, you will have to adjust the code to include single quotes around each value that gets put into the strIDs variables.

    You can pass the SQL text to the record source of a subform. I assume the subform is on the same form as the controls you are using for the searching. You will not have to open the subform since it will already be open when you open the main form. What I typically do is to hide the subform (set its visible property to No). When someone clicks on the search button, I then reset the visible property to Yes to display the search results.

  5. #5
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    ooohh... Yep! i tried your method, but it still didnt manage to get the results that i want.

    The problem that cause my search result to not work i think is the multiple values that i have inside the column. So when i tried to search it does not search for any results but if i were to search using another field without multiple values i am able to do it.

    i replace the codes you gave me with my own listbox name, is there something wrong with my codes?

    Dim lngloop As Long
    Dim strWhere As String
    Dim strIDs As String


    If Me.ConceptLst.ItemsSelected.Count <> 0 Then
    If Me.ConceptLst.ItemsSelected.Count > 0 Then
    For lngloop = 0 To Me.ConceptLst.ItemsSelected.Count - 1
    If lngloop = 0 Then
    strIDs = strIDs + "" & Me.ConceptLst.ItemData(Me.ConceptLst.ItemsSelected (lngloop))
    Else
    strIDs = strIDs + "," + "" & Me.ConceptLst.ItemData(Me.ConceptLst.ItemsSelected (lngloop))
    End If
    Next lngloop
    End If
    strWhere = strWhere & "ProspectsTable.Concept in (" & strIDs & ") AND "
    strIDs = ""
    End If


    or is there something wrong in my SQL statement that doesnt allow my search to work?

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...multiple values that i have inside the column
    What do you mean by the above statement? Could you provide an example?

  7. #7
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    Click image for larger version. 

Name:	Untitled.png 
Views:	6 
Size:	11.7 KB 
ID:	10207


    take a look at the highlight part.. it has multiple values in the column.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The way you have your data structured in the list box will cause some problems. Is the list box based on a table?

    From the looks of it, you have various nationalities. By normalization rules, each nationality should be a separate record in a table

    tblNationalities
    -pkNationalityID primary key, autonumber
    -txtNationality

    Now, if multiple nationalities can apply to an item, you would need a table to hold the related nationalities

    tblItemNationalities
    -pkItemNationID primary key, autonumber
    -fkItemID foreign key to tblItems
    -fkNationalityID foreign key to tblNationalities

    From looking at your list box further, you have nationalities as well as other descriptors that are not nationalities such as cafe, coffee house etc. Logically speaking these should not be grouped together.

    I think it is time to look more into how you have your tables structured as well as to get more information about what your application is attempting to do & the type of data with which you are dealing. Could you please provide some more details?

  9. #9
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    the reason why there were other descriptions because it all belongs to the concept column in my tblProspectsTable. When i actually click on "F&B" in my the first listbox, the second listbox will generate according to the values that are selected in the first listbox, thats where you get "asian, indian, chinese" in the tblProspectsTable under the column Concept. (do you understand this? if not, please feel free to clarify ) The attach picture's values are based on a multi select listbox.

    is it really very tough to do the codes and query if i want to search for records using a multi select listbox?

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If a column in your prospects table has varying types of entity data, it indicates that you have not normalized that table, so I would recommend you take another look at your table structure.

    I would recommend separating the prospect data out by category and then tie the applicable ones to whatever you are evaluating. BTW, can you explain what you are trying to do with your application, so that we can better able to help you?

    is it really very tough to do the codes and query if i want to search for records using a multi select listbox?
    I would say that it is possible to search for a string within a field, but what would happen if someone changes the order of the items in the string? The search would fail to find all of the records. That is why it is better to separate out the various items.

  11. #11
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    But wouldnt it be alot more confusing and messy to see so many of the same records in the table if we were to separate the records? correct me if i am wrong.

    i have 1 combo box, 3 listbox. which the first and second listbox(multiple selection) are in a way "connected" to each other and the last listbox is a stand alone listbox with multiple selection function. What i want to do is for users to search for records that they want from the tblProspectsTable, so for example a user wants to search for Price that are less than $20 in a combo box, "F&B" in the first listbox which will then generate the second listbox displaying the concept, which the user will choose "Asian, chinese, indian"(example) and in the last listbox the user selects "AMK parks". When the user clicks on the search button, a table should displays the results that fits the user's search.

    i hope you understand what i meant, please clarify with me if you dont

    I really appreciate your help! thanks alot!!

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    But wouldnt it be alot more confusing and messy to see so many of the same records in the table if we were to separate the records?
    The basic prospect info would be in 1 table, the related information (such as the nationalities) describing the prospect would be in a separate but related table. There could be any number of nationalities related to a particular prospect. That table would only be a linking table; the actual list of nationalities (all nationalities) would be in another table.

    As an example, let's say that your prospect table contains a list of eating establishments of various types. Each establishment can serve foods of various nationalities. To structure this, you would need 3 tables as shown in the attached database. Take a look at the frmProspects in the attached database.
    Attached Files Attached Files

  13. #13
    noobaccess is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    22
    ohhh.. i found out another method to search for the results that i want!

    You have helped me alot too! really thank you very much!!!!

    Hope you will have a good day!! ^^

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Glad you found a solution. Best of luck on your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-02-2012, 12:48 PM
  2. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  3. Looping through a multi select listbox - how do I do it?
    By shabbaranks in forum Programming
    Replies: 4
    Last Post: 04-02-2012, 11:56 AM
  4. Update Column from Multi-select listbox
    By jhargram in forum Forms
    Replies: 4
    Last Post: 02-27-2012, 12:03 PM
  5. Need Multi-Select listbox code example
    By Buakaw in forum Forms
    Replies: 4
    Last Post: 08-21-2011, 08:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums