Results 1 to 12 of 12
  1. #1
    jfbuller is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    10

    Exclamation Multi-Select list box as parameter query for cb

    Hello,
    I'm building a form with cascading combo boxes, where the selection in the 1st cb effects the choices available in the 2nd cb, which, in turn effects the choices avalable in the multi-select lb. I've made it this far successfully.
    The problem I have is taking the m-s lb, and having that cascade to the 3rd cb. Meaning that selections made in the multi-select lb will dictate what choices are available in the 3rd cb... This will go to a 4th cb and then a "value exceeded" text box, before using the selections made from the whole form to generate a query of the DB.

    Cascading form structure:
    combo box 1
    combo box 2
    multi select list box


    combo box 3
    comb box 4 or multi select list box 2
    text box

    From what I've gathered thus far, it seems that I have to find some VBA that will concatenate my selection into "item1", "item4", "item5" so it can be read via SQL in the row source of my 3rd cb.

    I've been using Access for about 2 weeks now and I can read and write SQL with moderate proficiency, but I need help with VBA. Maybe this is close?

    Code:
    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
        Set frm = Form!frmMyForm
        Set ctl = frm!lbMultiSelectListbox
        strSQL = "Select * from Employees where [EmpID]="
        'Assuming long [EmpID] is the bound field in lb
        'enumerate selected items and
        'concatenate to strSQL
        For Each varItem In ctl.ItemsSelected
            strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
        Next varItem
    
        'Trim the end of strSQL
        strSQL=left$(strSQL,len(strSQL)-12))
    Any help and patience are appreciated, thanks to the community.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I attached an example of how I set up cascading combo boxes. The only difference with using a Multi-Select List box is that you need to use a button to continue after selecting options in the list box instead of code in the after update event of the list box.

    The code (since I don't know your form and control names) would look like:

    Code:
    Public Sub ButtonName_Click()
         Dim frm As Form, ctl As Control
         Dim varItem As Variant
        Dim strSQL As String
    
        Set frm = Form!frmMyForm
        Set ctl = frm!lbMultiSelectListbox
        
        'change the SELECT statement to what you want to see in combo box 3
        strSQL = "Select * from Employees where [EmpID]="
        'Assuming long [EmpID] is the bound field in lb
        'enumerate selected items and
        'concatenate to strSQL
        For Each varItem In ctl.ItemsSelected
        
        'change [EmpID] to your field name
            strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
        Next varItem
     
        ' change the '12' to the number of your field name between the quotes
        ' in the example above, " OR [EmpID]=" has 12 chard (include spaces)
        'Trim the end of strSQL
        strSQL = Left$(strSQL, Len(strSQL) - 12)
    
    ' clear the following controls and requery
       me.comboBox3=NULL
       me.comboBox4=NULL
       me.TextBox=NULL
    
        Me.[Combo box 3 name].RowSource = strSQL
        me.comboBox4=Requery
       me.comboBox3=Requery
    
    
    End Sub

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

    Great work, thanks for the post. A few things I'm wondering about though, as I'm having some trouble getting everything to fall into place.

    Where you define the SQL string:
    'change the SELECT statement to what you want to see in combo box 3
    strSQL = "Select * from Employees where [EmpID]="

    Is this written like a normal SQL query?
    strSQL = "SELECT [field displayed in combo box 3] FROM tbl_MyTable WHERE [Field in list box]="

    Is there anything required after the " = " in ( ...where [EmpID]=" )?

    I keep getting a syntax error (missing operator) when I'm trying to process the selection using the code you offered. It seems like I'm missing quotes or commas, because it's not concatenating the selected items as one search.

    The third line from the last sets the row source for combo box 3 as the SQL string, but the row source info within the properties of this cb is still blank-is this a sign that something is amiss or is it fine because the code takes care of it once run?

    I like the select list box example you sent, very clear, nothing hidden from the user. However, none of your list boxes are multi-select to plagiarize as examples.



    For clarification:

    combo box 1: Data set.(which entity collected the data)
    combo box 2: Region. (each contains many sites)
    Multi-Select list box: sampling location. (sites within the region)
    combo box 3: sampling type. (types of samples collected at a given location)
    combo box 4: parameter. (various parameters analyzed from the sample)
    text box: guideline. (user inputs a value to see if that value is exceeded at a given site by the chosen parameter)

    Thanks again for your help!
    Last edited by jfbuller; 09-19-2011 at 03:06 PM. Reason: additional info

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I forgot to put in: "Change the blue text to your object names"

    Where you define the SQL string:
    'change the SELECT statement to what you want to see in combo box 3
    strSQL = "Select * from Employees where [EmpID]="

    Is this written like a normal SQL query?
    strSQL = "SELECT [field displayed in combo box 3] FROM tbl_MyTable WHERE [Field in list box]="
    Yes, it is written like a normal SQL query.

    Is there anything required after the " = " in ( ...where [EmpID]=" )?
    That is taken care of in the FOR...NEXT loop

    I keep getting a syntax error (missing operator) when I'm trying to process the selection using the code you offered. It seems like I'm missing quotes or commas, because it's not concatenating the selected items as one search.
    I'll look at the code again.
    The third line from the last sets the row source for combo box 3 as the SQL string, but the row source info within the properties of this cb is still blank-is this a sign that something is amiss or is it fine because the code takes care of it once run?
    Yes, the code sets the combo box3 row source on-the-fly.

    I like the select list box example you sent, very clear, nothing hidden from the user. However, none of your list boxes are multi-select to plagiarize as examples.
    I didn't need a multi-select at the time... but I'm learning....

    For clarification:

    combo box 1: Data set.(which entity collected the data)
    combo box 2: Region. (each contains many sites)
    Multi-Select list box: sampling location. (sites within the region)
    combo box 3: sampling type. (types of samples collected at a given location)
    combo box 4: parameter. (various parameters analyzed from the sample)
    text box: guideline. (user inputs a value to see if that value is exceeded at a given site by the chosen parameter)
    It is harder to write code without the correct form names/control names/field names.... but I am trying. (My GF told me that too; she said I was very trying!!)

    OK, here goes......

    combo box 1: standard SQL, no criteria, with code in the after update event

    combo box 2: standard SQL, with criteria referencing combo box 1, with code in the after update event

    Multi-Select list box: standard SQL, with criteria referencing combo box 2, NO code in the after update event. The code will be in a button next to the multi-select list box. The code will loop through the selections in the list box, then set the row source for combo box 3.

    combo box 3: can't have row source set because there might be multiple criteria. See Multi-Select list box above. Code in the after update event

    combo box 4: standard SQL, with criteria referencing combo box 4, with code in the after update event

    text box: not sure about this yet.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't want to interrupt Steve's assistance of the primary question, just want to clarify something.

    Quote Originally Posted by ssanfu View Post
    The only difference with using a Multi-Select List box is that you need to use a button to continue after selecting options in the list box instead of code in the after update event of the list box.
    I wouldn't say you need to use a button. You can use the after update event of the listbox, as long as you either want or are content with it firing with every selection/unselection. Often this is exactly what you want. For instance, if the SQL was filling a subform, the subform results would dynamically change with each selection.

    In your case, the user wouldn't see anything changing, but I'd probably use the after update event anyway, save performance issues, to avoid the button click. Maybe that's just because I have users that whine about every extra click.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by pbaldy View Post
    I don't want to interrupt Steve's assistance of the primary question, just want to clarify something.



    I wouldn't say you need to use a button. You can use the after update event of the listbox, as long as you either want or are content with it firing with every selection/unselection. Often this is exactly what you want. For instance, if the SQL was filling a subform, the subform results would dynamically change with each selection.

    In your case, the user wouldn't see anything changing, but I'd probably use the after update event anyway, save performance issues, to avoid the button click. Maybe that's just because I have users that whine about every extra click.
    Paul - glad you posted.

    I have two Multi-select list boxes in a project, both of which have buttons to initiate a process (code) after selections have been made. I knew that the after update event would fire after each selection, but I hadn't thought it through far enough as to updating combo box 3.

    You are right about the user not seeing combo box 3 change in any way. That is a much cleaner way than what I suggested.


    PS - nice site. I have it bookmarked.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Thanks Steve! I wouldn't say there's anything wrong with using the button, just pointing out that you don't have to use one. Button & after update event each have their pluses and minuses, so each situation needs to be evaluated.

    I've always loved your sig by the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

    Thanks again for your patience. You've been a great help so far. Unfortunately, I'm still getting a syntax error, do you know the format in which SQL concatenates multiple search items? I'd like it to return all entries containing a selection of nominal values i.e. all presidents born in these states: Virginia, Connecticut, Texas, Pennsylvania.

    Quote Originally Posted by ssanfu View Post
    I forgot to put in: "Change the blue text to your object names"
    I figured you took the time to make them blue, which meant switch generic values for unique.

    Quote Originally Posted by ssanfu View Post
    It is harder to write code without the correct form names/control names/field names.... but I am trying. (My GF told me that too; she said I was very trying!!)
    I apologize for the confusion. That list was just to help you put names to faces so to speak. fail.MyFail.

    The text box is easy, don't worry about that. It's the last line in the final query that returns numerical results >= to the value entered in the box, or all values if no value is entered.


    Thanks again!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In case Steve's still sleeping up there, the way to debug SQL strings in code is to add:

    Debug.Print strSQL

    after the string is built but before it's used and run the code. That line will print the finished SQL out to the VBA Immediate window. If you don't spot the problem there, you can copy/paste it into a blank query and try to run it. You'll often get a more descriptive error there. If you don't spot the problem, post the SQL here.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    jfbuller is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    10
    So I found another way. This way properly concatenates the selected items into an SQL string that can be sent to a query. It works.

    Code:
    Private Sub cmdMS_Click()
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryTestmslb")
    ' Loop through the selected items in the list box and build a text string
    If Me!lbSampleLocation.ItemsSelected.Count > 0 Then
    For Each varItem In Me!lbSampleLocation.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lbSampleLocation.ItemData(varItem) & "'"
    Next varItem
     
    Else
    strCriteria = "tbl_WQ_Master.[Sampling Location] Like '*'"
    End If
    ' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    ' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM tbl_WQ_Master " & _
    "WHERE tbl_WQ_Master.[Sampling Location] IN(" & strCriteria & ")ORDER BY [Sampling Location] ASC;"
    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qryTestmslb"
    'clear the following controls and requery
    Me.cbSampleType = ""
    Me.cbParameter = ""
    Me.txtGuideline = ""
    'set row source and requery susequent boxes
    Me.cbSampleType.RowSource = strSQL
    Me.cbParameter.Requery
    Me.cbSampleType.Requery
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    End Sub
    The tweaks are as such:
    I want to use the selections made in the list box to designate the choices available in the row source of the following combo box, which it does-all 15,000 of them. I've tried using SELECT DISTINCT to give unique values only, not hundreds of repeats. No luck. It even appears in the row source query as " SELECT DISTINCT * " I think the * is messing things up here, and the only thing I can think of is to query the multi select query as the new row source for distinct results.

    The other idea is this: Instead of running the code from a seperate button or on update, what about running "On Click ()" from the combo box after the list when the user goes to choose a value there?

    Suggestions

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Nope, not asleep....programming

    I was going to ask for a copy of your mdb, but I decided to create a new one.
    Attached is an example of cascading combo boxes with a multi-select list box thrown in the middle.

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

    Thanks again for all your help. It was a combination of the code you wrote, plus the code I put in my post, plus some probably ugly work-arounds that gave me a working form that makes for a pretty decent UI for searches. Paul, thanks for your help as well. If anybody's interested in how it got done, let me know. So thanks again for your efforts and allowing me to put that satisfying "solved" stamp on my thread. I've added my approval to your reps, some forums have a way to bump your status via a "thanks", is there a way to do this here?

    Cheers,

    Justin
    Last edited by jfbuller; 09-22-2011 at 01:18 PM. Reason: the times they are a changin'

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

Similar Threads

  1. Multi-Select List Box as Criteria in Query
    By broadwat in forum Queries
    Replies: 6
    Last Post: 09-19-2011, 07:47 AM
  2. Multi select list box
    By foxtet in forum Forms
    Replies: 1
    Last Post: 05-30-2011, 02:13 PM
  3. Replies: 1
    Last Post: 01-28-2011, 07:59 PM
  4. Replies: 1
    Last Post: 10-22-2010, 10:11 AM
  5. Parameter Query: Select From A List?
    By catbob in forum Queries
    Replies: 4
    Last Post: 02-08-2010, 08:24 PM

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