Results 1 to 7 of 7
  1. #1
    broadwat is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    1

    Question Multi-Select List Box as Criteria in Query

    Hi There,

    I am having trouble creating a query with advanced criteria that will be able to populate a listbox based on both a keyword search in two of the fields, (question and answer) and the criteria that says that a field called topic will be one of the topics selected from a list box. I have a button in my form that filters the entire form according to what topics are selected in the multi-select list box that looks like this:

    Private Sub Command433_Click()
    Dim varItem As Variant
    Dim strWhere As String

    For Each varItem In Me![multitopic].ItemsSelected
    strWhere = strWhere & "Topic =" _
    & Chr(39) & Me![multitopic].Column(0, varItem) & Chr(39) & " Or "
    Next varItem

    strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

    DoCmd.OpenForm "RFPDatabase", , , strWhere
    End Sub

    What I would like to do is apply this where condition to a query. The query (called topiclinkedlist) is more complicated but I created a query on top of it (topinclinkedlist2) that simplies to:

    SELECT topiclinkedlist.ID, topiclinkedlist.group, topiclinkedlist.topic, topiclinkedlist.question
    FROM topiclinkedlist;

    How can I make the query called topiclinkedlist2 also have a where condition like strWhere? Ultimately I will be populating a listbox with the topiclinkedlist2 query so that records can be navigated to in the form after clicking them within the list box. I do not want the whole form to be filtered based on the topics selected.

    Very much appreciate your help. Hope to hear from people soon.



    Best,
    Mark

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think that would involve modifying the query definition with VBA code using the QueryDefs collection. Here is an example from my project:
    Private Sub btnExcel_Click()
    Dim qdfUser As DAO.QueryDef
    CurrentDb.QueryDefs.Delete ("UserQuery")
    Set qdfUser = CurrentDb.CreateQueryDef("UserQuery", Me.tbxFilter)
    DoCmd.OpenQuery "UserQuery", , acReadOnly
    DoCmd.RunCommand acCmdExportExcel
    End Sub

    Otherwise, build a form or report based on the query and filter the form/report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jfbuller is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    10
    Hello,

    I have a similar issue dealing with the difficulty of setting up a multi-select list box. I have little to no experience working with Access and I've come this far being self-taught, now I'm in need of some help.

    The gist of the project is that I'm building a form (dialog box) with multiple queries to essentially filter the db for desired results for users with no access experience.

    In order from top to bottom the form structure is as follows: two linked combo boxes, a linked multi-select list box, two more linked combo boxes and an unlinked text box.

    I have already built a working sister form with non linked combo boxes and the text box, but multiple selections are now needed and linking everything is a much cleaner way of narrowing down the search results for the user.

    My problem lies in that I don't know how to populate the multi-select list box from the linked combo boxes above it. Those boxes must return all results (no filter) when left empty (is null). Then I need the subsequent linked combo box to use the concatenated selection from the list box as the basis for the query to return its own row source. I've done most of my work so far in SQL and now I'm getting lost in VBA.

    I haven't found an online resource that answers the multi-select list box as a slave situation and any help from the community here would be much appreciated.

    Thanks,

    Justin
    Last edited by jfbuller; 09-16-2011 at 01:24 PM. Reason: Corrections

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not sure if I understand what you want to do... is the following code close??

    Code:
    Private Sub Command433_Click()
       Dim varItem As Variant
       Dim strWhere As String
       Dim sSQL As String
    
       For Each varItem In Me![multitopic].ItemsSelected
          strWhere = strWhere & "Topic =" _
                     & Chr(39) & Me![multitopic].Column(0, varItem) & Chr(39) & " Or "
       Next varItem
    
    
       strWhere = Left(strWhere, Len(strWhere) - 4)   'Remove the last " Or "
    
       DoCmd.OpenForm "RFPDatabase", , , strWhere
    
       '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       
       'default row source for list box
       sSQL = "SELECT topiclinkedlist.ID, topiclinkedlist.group, topiclinkedlist.topic, topiclinkedlist.question"
       sSQL = sSQL & " FROM topiclinkedlist"
    
       'add the where clause, if available
       If Len(Trim(strWhere)) > 0 Then
          sSQL = sSQL & " WHERE " & strWhere
       End If
    
       sSQL = sSQL & ";"
    
    
       'this sets the row source for a list box 'lstTopic2'
       ' on form "RFPDatabase"
       Forms!RFPDatabase.lstTopic2.RowSource = sSQL
    
    End Sub

  5. #5
    jfbuller is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    10
    Steve,

    Thanks for your prompt post! I have figured out part one of my problem: A) Have selection from combo box 1 dictate choices available in combo box 2
    B) Have selection from combo box 2 dictate choices available in Multi-select list box.

    Since I understand SQL better than VBA so far, it's written using SQL queries and simple VBA to refresh the boxes and clear contents (on change).

    Part 2 of the problem is get the selection made in the list box to dictate the choices available in combo box 3.

    Once there I can finish combo box 4 and the text box with SQL.

    Ideas?

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Actually, I was responding to broadwat's question.

    By posting your problem under broadwat's, you have hijacked his thread. By rights you should have started your own thread.

    I have been thinking about your question, but I am not sure what you mean by
    "two linked combo boxes, a linked multi-select list box, two more linked combo boxes and an unlinked text box."

    Do you mean the controls are bound to a field in the form's record set? Not a good idea - any change in a combo box (for example) will change the data stored in the current record.

    What you are describing is called "cascading combo boxes".
    Set the row source for combo box 1, then set the row source for combo box 2 with criteria (the WHERE clause) looking at combo box 1. For combo box 3 (or the list box), set the row source with the criteria from combo box2.

  7. #7
    jfbuller is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    10
    Sorry to be stepping on toes and highjacking threads, it's poor forum etiquette. I already have what you described with the cascading cb, but now I don't know how to deal with the list box selection. Please see my own thread.

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

Similar Threads

  1. Multi select list box
    By foxtet in forum Forms
    Replies: 1
    Last Post: 05-30-2011, 02:13 PM
  2. Passing Multi Select string to Query criteria
    By oleBucky in forum Queries
    Replies: 4
    Last Post: 05-15-2011, 02:11 PM
  3. Replies: 1
    Last Post: 01-28-2011, 07:59 PM
  4. Replies: 1
    Last Post: 10-22-2010, 10:11 AM
  5. Multi-select List Boxes
    By Rawb in forum Programming
    Replies: 6
    Last Post: 09-21-2010, 09:02 AM

Tags for this Thread

Posting Permissions

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