Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have inherited a database with a form that is supposed to give users the ability to select a variety of options and then create a report. The form has 17 combo boxes where the user is supposed to be able to make 1 to 17 selections to run a query. The combo boxes look up their values from a table that contains a list of tasks. The list is then used to query another table that references these tasks. The goal is to get a list of individuals who are able to perform these tasks. If there were 3 combo boxes, I'd be fine. I am creating the query using VBA because it was not working using an Access query referencing the form. The code I'm using will work for 3 boxes, but I shudder to think how much code would be required to handle 17. There must be an easier way to set this up. What am I missing?

    Here's my code for the first 3 boxes. I apologize for the non-creative names for the combo boxes that were used by the previous owner of the database! I check elsewhere if combo6 is null, and demand at least one selection be made. I build the query by using the variable strIn as criteria for an In (" ") statement within the query.

    If IsNull(Me.Combo8) Then
    If IsNull(Me.Combo10) Then
    strIn = "'" & Me.Combo6 & "'"
    Else
    strIn = "('" & Me.Combo6 & "','" & Me.Combo10 & "')"
    End If
    Else
    If IsNull(Me.Combo10) Then
    strIn = "('" & Me.Combo6 & "','" & Me.Combo8 & "')"
    Else
    strIn = "('" & Me.Combo6 & "','" & Me.Combo8 & "','" & Me.Combo10 & "')"
    End If
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of 17 combo boxes, I'd use a single list box with its MultiSelect property set to Simple or Extended. The user can select any number of items in the list box.

    You'd use code like this (lstSelect is the name of the list box):

    Code:
    Dim v As Variant
    Dim strIn As String
    
    For Each v In Me.lstSelect.ItemsSelected
      strIn = strIn & ", " & Me.lstSelect.ItemData(v)
    Next v
    
    If Not strIn = "" Then
      strIn = "(" & Mid(strIn, 3) & ")"
    End If
    The advantage of this approach is that it doesn't matter how many items the user selects - 1, or 5, or 37.

  3. #3
    Star Lounger
    Join Date
    Jan 2008
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your solution works perfectly Hans, as always! The user will be pleased since they needed to run this query several times to accommodate all the selections they needed. The list box is much more flexible and easier to work with.

    Thanks again!

Posting Permissions

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