Using multiple selections in Combo or List boxes 2

[Alanclyde]

How can I create a query by using the multiple selection option of a Combo or List box.

Say I have three fields:

Name
Address
Phone

I want to select Name and Address and create a query by selecting those options from one of the boxes above and then select Name and Phone and update the query with the new fields...etc...etc.

Obviously I am only using three fields here, but it could be 5, 6, 10 etc

 

[MazeWorX]

I use an approach that involes filtering the report with mulitple selections. The code is quite involved but you could easily adapt it for your needs. I have attached the form and code however because of confidentiality agreements I cannot include the tables with data.

HTH M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MazeWorX]

ohh btw any questions regarding the code ask away

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[Alanclyde]

Thanks MazeWorX, this is gogin to be tough. I have tried creating a dummy table and reports to see if I can hang them together.

Is there no chance of the tables (without data) as I could easily populate them myself with dummy data

 

[MazeWorX]

sure give me a sec

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[Alanclyde]

Thanks MazeWorX but I am floundering, but your still a star for helping me.

 

[MajP]

I did not get a chance to look at MazeWorX approach so this may be similar to his approach.

I put a lot of multi select listboxes on a form. Normally the logic is everything inside a single list box is an OR, and it is an AND between listboxes.
(color = 'red' or color = 'Blue') AND (size = "Big" or Size = "Small") And (count = 7)

Call this code on each listboxes afterupdate event. Although this code is long, it is pretty much the same code for each listbox. Once you see how one is done all the rest are done the same way. There is really only about 5 lines of code per listbox.

Public Sub createFilter()
  Dim strType As String
  Dim strCritical As String
  Dim strScope As String
  Dim strRRB1 As String
  Dim strRRB2 As String
  Dim strArea As String
  Dim strKPP
  Dim strFilter As String
  Dim itm As Variant
  
  'Filter by Type
  For Each itm In Me.lstFilterByType.ItemsSelected
     If strType = "" Then
        strType = "strRequirementType_Threshold_Objective = '" & Me.lstFilterByType.ItemData(itm) & "'"
     Else
       strType = strType & " OR strRequirementType_Threshold_Objective = '" & Me.lstFilterByType.ItemData(itm) & "'"
     End If
  Next itm
  If Not strType = "" Then
    strType = " (" & strType & ") AND "
  End If
  
 'Filter by Critical
 For Each itm In Me.lstFilterByCritical.ItemsSelected
   If strCritical = "" Then
      strCritical = "blnCriticalRequirement = " & Me.lstFilterByCritical.ItemData(itm)
   Else
     strCritical = strCritical & " OR blnCriticalRequirement = " & Me.lstFilterByCritical.ItemData(itm)
   End If
 Next itm
  If Not strCritical = "" Then
    strCritical = "(" & strCritical & ") AND "
  End If
 
 'Filter by scope
 For Each itm In Me.lstFilterByScope.ItemsSelected
   If strScope = "" Then
      strScope = "inScope = " & Me.lstFilterByScope.ItemData(itm)
   Else
     strScope = strScope & " OR inScope = " & Me.lstFilterByScope.ItemData(itm)
   End If
 Next itm
  If Not strScope = "" Then
    strScope = " (" & strScope & ") AND "
  End If
 
 'Filter by RRB1 resolution
  For Each itm In Me.lstRRB1.ItemsSelected
   If strRRB1 = "" Then
      strRRB1 = "strResults = '" & Me.lstRRB1.ItemData(itm) & "'"
   Else
     strRRB1 = strRRB1 & " OR strResults = '" & Me.lstRRB1.ItemData(itm) & "'"
   End If
 Next itm
  If Not strRRB1 = "" Then
    strRRB1 = " (" & strRRB1 & ") AND "
  End If
 
 'Filter by RRB2 Resolution
   For Each itm In Me.lstRRB2.ItemsSelected
   If strRRB2 = "" Then
      strRRB2 = "strRRB2Results = '" & Me.lstRRB2.ItemData(itm) & "'"
   Else
     strRRB2 = strRRB2 & " OR strRRB2Results = '" & Me.lstRRB2.ItemData(itm) & "'"
   End If
 Next itm
  If Not strRRB2 = "" Then
    strRRB2 = " (" & strRRB2 & ") AND "
  End If
 
 'Filter by KPP
 For Each itm In Me.lstFilterByKPP.ItemsSelected
   If strKPP = "" Then
      strKPP = "isKPP = " & Me.lstFilterByKPP.ItemData(itm)
   Else
     strKPP = strKPP & " OR isKPP = " & Me.lstFilterByKPP.ItemData(itm)
   End If
 Next itm
  If Not strKPP = "" Then
    strKPP = "(" & strKPP & ") AND "
  End If
  
 'Filter by Area
 For Each itm In Me.lstFilterByArea.ItemsSelected
   If strArea = "" Then
      strArea = "strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
   Else
     strArea = strArea & " OR strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
   End If
 Next itm
  If Not strArea = "" Then
    strArea = " (" & strArea & ") AND "
  End If
  
 strFilter = strType & strCritical & strScope & strRRB1 & strRRB2 & strKPP & strArea
 If Not strFilter = "" Then
    strFilter = Left(strFilter, Len(strFilter) - 5)
 End If
 'Debug.Print strFilter
   
   
   Me.FilterOn = False
   Me.Filter = ""
   Me.Filter = strFilter
   Me.FilterOn = True
 If Me.Recordset.RecordCount = 0 Then
   Me.FilterOn = False
   MsgBox "No Records"
 End If
End Sub

 

[Alanclyde]

Phew MajP that is some coding..I will give it a go...thanks for your help

 

[MajP]

It looks like a lot, but each listbox is exactly the same. Basically once you figure out how to do one listbox it is a cut and paste job with some name changing. That example is 7 listboxes.