Results 1 to 7 of 7
  1. #1
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214

    Multiple Listboxes in Search Form


    I have a search form that contains a listbox that allows multiple selections to be selected and searched. My problem is that I want to add another listbox to the current code but cannot get it work along side the current listbox. See below code.
    Code:
    Private Sub Search_Click()
    'Update the record source
    Me.SearchSubform.Form.RecordSource = "Select * From Search " & BuildFilter
    'Requery the subform
    Me.Form!SearchSubform.Form.Requery
    End Sub
    Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    varWhere = Null 'Main Filter
    'Check for LIKE Status
    If Me.Status > "" Then
        varWhere = varWhere & "[Status] LIKE '*" & Me.Status & "*' And "
    End If
    'Check for LIKE Last Name
    If Me.LastName > "" Then
        varWhere = varWhere & "[Last Name] Like '*" & Me.LastName & "*' And "
    End If
    'Check for LIKE First Name
    If Me.FirstName > "" Then
        varWhere = varWhere & "[First Name] LIKE '*" & Me.FirstName & "*' And "
    End If
    'Check for LIKE Account Number
    If Me.AccountNumber > "" Then
        varWhere = varWhere & "[Account Number] LIKE '*" & Me.AccountNumber & "*' And "
    End If
    'Check for LIKE Social Security Number
    If Me.SocialSecurityNumber > "" Then
        varWhere = varWhere & "[Social Security Number] LIKE '*" & Me.SocialSecurityNumber & "*' And "
    End If
    'Check for LIKE Entity Name
    If Me.EntityName > "" Then
        varWhere = varWhere & "[EntityName] LIKE '*" & Me.EntityName & "*' And "
    End If
    'Check for LIKE EIN
    If Me.EIN > "" Then
        varWhere = varWhere & "[EIN] LIKE '*" & Me.EIN & "*' And "
    End If
    'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE" & varWhere
        
    ' 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
    Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim varItem As Variant
       Dim strCriteria As String
       Dim strSQL As String
       Set db = CurrentDb()
       Set qdf = db.QueryDefs("Search")
       If Me!Company.ItemsSelected.Count > 0 Then
          For Each varItem In Me!Company.ItemsSelected
             strCriteria = strCriteria & "[Account List].[Company Name] = " & Chr(34) & Me!Company.ItemData(varItem) & Chr(34) & "OR "
          Next varItem
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
        Else
          strCriteria = "[Account List].[Company Name] Like '*'"
       End If
       strSQL = "SELECT * FROM [Account List] " & "WHERE " & strCriteria & ";"
       qdf.SQL = strSQL
       Set db = Nothing
       Set qdf = Nothing
    End Function

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I see loop structure for only one listbox.
    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
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214
    I know. I posted the code that is working right now with one listbox. I have tried adding the other one but I cannot get it to work. So I figured post the original code and see what suggestions came back for adding a second listbox to it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why wouldn't it work - error message, wrong results, nothing?

    Put another loop for the second box right after the first box loop. Keep concatenating. Mixing AND and OR operators is always tricky. Show attempted code for analysis.
    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.

  5. #5
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214
    Code:
    BuildFilter = varWhere
    Dim db As DAO.Database
       Dim qdf As DAO.QueryDef
       Dim varItem As Variant
       Dim strCriteria As String
       Dim strSQL As String
       Set db = CurrentDb()
       Set qdf = db.QueryDefs("Search")
       If Me!Company.ItemsSelected.Count > 0 Then
          For Each varItem In Me!Company.ItemsSelected
             strCriteria = strCriteria & "[Account List].[Company Name] = " & Chr(34) & Me!Company.ItemData(varItem) & Chr(34) & "OR "
          Next varItem
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
        Else
          strCriteria = "[Account List].[Company Name] Like '*' And "
       End If
       strSQL = "SELECT * FROM [Account List] " & "WHERE " & strCriteria & ";"
       If Me!Representative.ItemsSelected.Count > 0 Then
          For Each varItem In Me!Representative.ItemsSelected
             strCriteria = strCriteria & "[Account List].[Rep] = " & Chr(34) & Me!Representative.ItemData(varItem) & Chr(34) & "Or "
          Next varItem
          strCriteria = Left(strCriteria, Len(strCriteria) - 3)
       Else
          strCriteria = "[Account List].[Rep] Like '*'"
       End If
       strSQL = "SELECT * FROM [Account List] " & "WHERE " & strCriteria & ";"
       Debug.Print strSQL
       qdf.SQL = strSQL
       Set db = Nothing
       Set qdf = Nothing
    End Function
    This is the code that I am trying to use for both listboxes. The listbox for Representative works perfectly now, but the Company listbox doesn't work at all anymore. I'm not quite sure where to go from here.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As stated, mixing AND and OR operators is tricky. I presume the final string needs to be in the form of:

    (value1a OR value1b OR value1c OR ...) AND (value2a OR value2b OR value2c OR ...)

    Note the pair of parens enclosing each OR set.

    Suggest you use two additional criteria variables, one for each listbox: strCriteria1, strCriteria2

    Then concatenate the two into one criteria string, maybe with condition code:

    If strCriteria1 <> "" Then
    strCriteria = "(" & strCriteria1 & ")"
    End If
    If strCriteria2 <>"" Then
    strCriteria = IIf(strCriteria <> "", " AND ", "") & "(" & strCriteria2 & ")"
    End If
    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.

  7. #7
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214
    Thanks for your reply. I used the following and it works perfectly right now.
    Code:
       strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ")" & ";"

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

Similar Threads

  1. Combining data from multiple listboxes
    By dshillington in forum Access
    Replies: 1
    Last Post: 10-04-2012, 11:53 AM
  2. Replies: 12
    Last Post: 03-22-2012, 02:48 AM
  3. Replies: 1
    Last Post: 12-04-2011, 09:11 PM
  4. Replies: 13
    Last Post: 11-20-2010, 06:45 AM
  5. Multiple Listboxes
    By Butterflies88 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:16 PM

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