Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    2 multi select list boxes on one search form


    Hi Guys and thanks for your time.

    I have a search form for my database that allows users to select multiple criteria from multi select list boxes.

    I successfully integrated a multi select listbox for users to select and search for counties.

    On the same page however I would like to integrate a similar multiselect box for nationality. I would like the user to be able to search for nationality with county or individually.

    After inserting my nationality list box and adding the code. i can now only search for either
    nationality or county and cant perform a combined search.


    Code:
    Private Sub btnSearch_Click()
        
        ' Update the record source
        'Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
       
        ' Update the record source
        If BuildFilter = "" Then
            Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew " & BuildFilter
        Else
            Me.sbfrmSearchResults1.Form.RecordSource = "SELECT * FROM qryNew WHERE " & BuildFilter
        End If
        
        'Requery the subform
        Me.sbfrmSearchResults1.Requery
        End Sub
    Private Function BuildFilter() As Variant
        Dim varWhere As Variant
        Dim CountyCode As Variant
        Dim varItem As Variant
        Dim intIndex As Integer
        Dim NationalityCode As Variant
        Dim strSQLOrderBy As String
        
        varWhere = Null  ' Main filter
        CountyCode = Null  ' Subfilter used for colors
        NationalityCode = Null
        ' Check for LIKE First Name
        
    If Me.txtFirstName > "" Then
            varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
        End If
        
        ' Check for LIKE Last Name
        If Me.txtSurname > "" Then
            varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
        End If
         
        If Me.txtRegNumber > "" Then
            varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
       End If
        
     ' Check for Colors in multiselect list
        For Each varItem In Me.lstCountyCode.ItemsSelected
            CountyCode = CountyCode & " [tblMemberDetails_CountyCode] = """ & _
                        Me.lstCountyCode.ItemData(varItem) & """ OR "
            
        Next
        
        'Test to see if we have subfilter for colors...
        If IsNull(CountyCode) Then
            ' do nothing
        Else
            ' strip off last "OR" in the filter
            If Right(CountyCode, 4) = " OR " Then
                CountyCode = Left(CountyCode, Len(CountyCode) - 4)
            End If
        
            'Add some parentheses around the subfilter
            varWhere = varWhere & "( " & CountyCode & " )"
        End If
        
         'NationalityCode
            
            ' Check for Nationality in multiselect list
        For Each varItem In Me.lstNationality.ItemsSelected
            NationalityCode = NationalityCode & " [tblmemberdetails.NationalityCode] = """ & _
                        Me.lstNationality.ItemData(varItem) & """ OR "
            
        Next
        
        'Test to see if we have subfilter for colors...
        If IsNull(NationalityCode) Then
            ' do nothing
        Else
            ' strip off last "OR" in the filter
            If Right(NationalityCode, 4) = " OR " Then
                NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
            End If
        
            'Add some parentheses around the subfilter
            varWhere = varWhere & "( " & NationalityCode & " )"
        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

  2. #2
    Join Date
    Feb 2009
    Posts
    6

    problem solved

    Hi guys,

    My problem is now resolved thanks to some advice

    Changed

    varWhere = varWhere & "( " & CountyCode & " ) AND "

    to

    varWhere = varWhere & "( " & CountyCode & " ) "


    Many thanks for all the time.

    James

  3. #3
    tarhim47 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    57
    Hey,

    In your search form, is the user able to select multiple nationalities and have the form return data of all records which include the selected nationalities?

    For example:

    Table
    - Person a - American
    - Person b - French
    - Person c - British
    - Person d - Chinses
    - Person e - Russian
    - Person f - Chinese

    Then the form will have a multiselect list box which allows the user to select multiple nationalities.

    So if the user selects American and Chinese, is your form able to retun three records - Person a, Person d and Person f?

    If yes, can you tell how you are able to do this?

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

Similar Threads

  1. Replies: 2
    Last Post: 05-29-2014, 09:58 PM
  2. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 AM
  3. Refresh form search text box
    By oxicottin in forum Forms
    Replies: 2
    Last Post: 11-19-2007, 02:28 PM
  4. Search for record from a form
    By hcoakley in forum Forms
    Replies: 0
    Last Post: 11-26-2006, 11:23 AM
  5. Replies: 1
    Last Post: 03-09-2006, 12:12 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