So Lost on how to accomplish this form/subform or could be report 1

[tmcrouse]

I have 11 listboxes and have been reading and researching for almost 2wks now with nothing that is helping. All I attempt does not work. the 11 listboxes the user can select 1 or more items from each. Then I need what they select to be used as the filters for a query. My listboxes are unbound because if bound, they will change the data in my main table. I have multiple tables that have primary keys and a foreign key links them to main table. Each listbox has a row source from their table. For example I have a state table which lists all the states and need the user to have the ability to select any or all states they want. No, I am not using All option for anything. Too complex. The main table might not list a state because the particular state might not have any orders associated yet. So, if the user would select an item in listbox that is not in main table, that item will just not appear. But they still need the option to select whatever they want. When they are done selecting what they want, I have a query to display results, however the query only contains 5 of the listboxes. The listboxes are:

lob, yr, st_cd, mth, bus_unit, prod_nm, cat, measure, sub, comm_lvl, comm_type


The query has the following:

program, lob, plan, st_cd, measure, comm_type, mbr_target, mbr_converted, effectiveness

I have tried so many options for coding this. Here they are:

Private Sub command8_click()
On Error GoTo Err_Handler
    'Purpose:  Open the report filtered to the items selected in the list box.
    'Author:   Allen J Browne, 2004.   [URL unfurl="true"]http://allenbrowne.com[/URL]
    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.
    Dim strDoc As String        'Name of report to open.
    
    'strDelim = """"            'Delimiter appropriate to field type. See note 1.
    strDoc = "Search_Quality_Programs"

    'Loop through the ItemsSelected in the list box.
    With Me.List1
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column. See note 2.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "[LOB] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "QualMain: " & Left$(strDescrip, lngLen)
        End If
    End If
   
    'Omit the last argument for Access 2000 and earlier. See note 4.
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

The above does nothing for me.

Private Sub command8_click()

    ' Update the record source
    If BuildFilter = "" Then
    Me.frmQual_Sub.Form.RecordSource = "select * from qualq1 where " & BuildFilter
    End If
    
    'Requery the subform
    Me.frmQual_Sub.Requery
    End Sub
    
Private Function BuildFilter() As Variant
    Dim varWhere As Variant
     
    varWhere = Null  ' Main filter
        
If Me.List1 > "" Then
        varWhere = varWhere & "[lob] LIKE """ & Me.List1 & "*"" AND "
    End If
        
If Me.List2 > "" Then
        varWhere = varWhere & "[yr] LIKE """ & Me.List2 & "*"" AND "
    End If
    
If Me.List3 > "" Then
        varWhere = varWhere & "[mth] LIKE """ & Me.List3 & "*"" AND "
    End If
        
If Me.List4 > "" Then
        varWhere = varWhere & "[st_cd] LIKE """ & Me.List4 & "*"" AND "
    End If
        
If Me.List5 > "" Then
        varWhere = varWhere & "[bus_unit] LIKE """ & Me.List5 & "*"" AND "
    End If
        
If Me.List6 > "" Then
        varWhere = varWhere & "[prod_nm] LIKE """ & Me.List6 & "*"" AND "
    End If
       
If Me.list7 > "" Then
        varWhere = varWhere & "[category_condition] LIKE """ & Me.list7 & "*"" AND "
    End If
        
If Me.list8 > "" Then
        varWhere = varWhere & "[measure] LIKE """ & Me.list8 & "*"" AND "
    End If
    
If Me.list9 > "" Then
        varWhere = varWhere & "[sub_measure] LIKE """ & Me.list9 & "*"" AND "
    End If
       
If Me.List10 > "" Then
        varWhere = varWhere & "[comm_lvl] LIKE """ & Me.List10 & "*"" AND "
    End If
        
If Me.List11 > "" Then
        varWhere = varWhere & "[comm_type] LIKE """ & Me.List11 & "*"" AND "
    End If
       
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = "''"
    Else
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
                   
    End If
     BuildFilter = varWhere
     End Function

this does nothing
The 2 above just sit and do nothing

Private sub command8_click()
Dim msg As String
  Dim i As Integer
    
  With Me.List1

   'loop through all the entries in the list
    
    For i = 0 To .ListCount
    
      'if this item is selected, add it to the msg variable
      If .Selected(i) Then
      
        'now we'll pull the value of the first column at the specified row and add it to the msg variable
        msg = msg & .Column(0, i) & vbCrLf
        
      End If
      
    Next i  'go to the next item and check that one
    End With
    
    With Me.List2
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
With Me.List3
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
  With Me.List4
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
   With Me.List5

   'loop through all the entries in the list
    
    For i = 0 To .ListCount
    
      'if this item is selected, add it to the msg variable
      If .Selected(i) Then
      
        'now we'll pull the value of the first column at the specified row and add it to the msg variable
        msg = msg & .Column(0, i) & vbCrLf
        
      End If
      
    Next i  'go to the next item and check that one
    End With
    
    With Me.List6
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
With Me.list7
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
  With Me.list8
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
   With Me.list9

   'loop through all the entries in the list
    
    For i = 0 To .ListCount
    
      'if this item is selected, add it to the msg variable
      If .Selected(i) Then
      
        'now we'll pull the value of the first column at the specified row and add it to the msg variable
        msg = msg & .Column(0, i) & vbCrLf
        
      End If
      
    Next i  'go to the next item and check that one
    End With
    
    With Me.List10
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
  
With Me.List11
    
    For i = 0 To .ListCount
    If .Selected(i) Then
    msg = msg & .Column(0, i) & vbCrLf
    End If
    
    Next i
    
  End With
    
  'now the msg variable should contain a list of items that were selected
  MsgBox msg
End sub

The above returns what was selected but I cannot figure out how to maximize on the concept of the message box to turn it into something I could use to either create a query to query my main table or filter on the query I already have. Please help me.

 

[MajP]

http://www.4shared.com/file/-WcpEOJ_ba/MajPDemoListBoxSearch_2.html

As far as I can tell it does it all correctly including unselecting the listboxes.

 

[tmcrouse]

This is incredibly awesome. This works perfect. OMG. Thank you so much. This has been quite an experience attempting to figure this out. You deserve some major raises over there.

 

[MajP]

To make this even more incredibly awesome, you may want to do the following. You can remove the code from the "Get Results" button. Put it in a standalone procedure:

Public Function FilterRecords()
   Dim FormFilter As String
   FormFilter = GetFilterFromListBoxes
   Debug.Print FormFilter
   Me.FilterOn = False
   Me.Filter = FormFilter
   Me.FilterOn = True
End Function

Then on the after update event of each listbox call this function. This is nice because as you choose a selection it narrows your list immediately. This avoids picking a combination of criteria that returns no records when the first criteria already excludes all records. Gives you that immediate feedback. Also in the multiselect you choose "extended", I think "simple" would be easier to use. Here is an update to show some features you may want to consider.

http://www.4shared.com/file/9Ws0TVCZba/MajPDemoListBoxSearch_3.html