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

    Search form multiple multi select list box's Error

    Hi Everyone. Thanks for your time.

    I am trying to create a search form that will allow users to select criteria from multiple multi select boxes. So far i have managed to achieve a search option for 2 list boxes:- county and nationality, while trying to add a third multi select list box for qualifications search is where i encounter my problem.

    I've copied the working code from my working list boxes, however it cant seem to pick up the search value from the qualification list box.

    I think this error may stem from the relatioships but i am not sure.

    This is the record source property of one of my working list boxes:

    lstNationality :SELECT [tblNationality].[NationalityCode], [tblNationality].[Nationality] FROM tblNationality;

    This is the record source property of my new troublesom qualification list box:

    SELECT [tblQualifications].[QualCode], [tblQualifications].[ShortQual] FROM tblQualifications ORDER BY [ShortQual];

    Am i correct in asuming that it's a relationship issue?

    I have attached a screnshot of my relationships, and included a copy of my code in this post, if any body has any ideas they would be greatly appericated.


    Many thanks once again for your time Kind regards, JAMES


    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 varItem As Variant
        Dim intIndex As Integer
        Dim CountyCode As Variant
        Dim NationalityCode As Variant
        Dim qualCode As Variant
        
        varWhere = Null  ' Main filter
        CountyCode = Null  ' Subfilter used for CountyCode
        NationalityCode = Null ' Subfilter used for NationalityCode
        qualCode = Null ' Subfilter used for qualCode
        
         ' 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
         ' Check for LIKE regNumber
        If Me.txtRegNumber > "" Then
            varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
       End If
        
          ' Check for counties 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 & " ) AND "
        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 & " ) AND "
        End If
         
          'qualCode
            
            ' Check for qualCode in multiselect list
        For Each varItem In Me.lstqual1.ItemsSelected
            qualCode = qualCode & " [tblqualifications_qualCode] = """ & _
                        Me.lstqual1.ItemData(varItem) & """ OR "
            
        Next
        
        'Test to see if we have subfilter for colors...
        If IsNull(qualCode) Then
            ' do nothing
        Else
            ' strip off last "OR" in the filter
            If Right(qualCode, 4) = " OR " Then
                qualCode = Left(qualCode, Len(qualCode) - 4)
            End If
        
            'Add some parentheses around the subfilter
            varWhere = varWhere & "( " & qualCode & " )  "
        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
    Hi
    thanks for the responce.

    I figured is out it was finally with some advice from my friend.

    qualCode a numeric value in the table (the data type) so, i don't want quotes around the value.

    Thanks so much for all the assistance.

    All the best and thanks again.

    Regards

    JAMES.

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

Similar Threads

  1. Search for multiple records
    By Blake in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:17 AM
  2. 2 multi select list boxes on one search form
    By woodey2002 in forum Forms
    Replies: 2
    Last Post: 11-05-2010, 12:44 PM
  3. Multi-select listbox and update flag in table
    By Suresh in forum Programming
    Replies: 0
    Last Post: 12-19-2007, 01:04 AM
  4. Replies: 1
    Last Post: 11-11-2006, 08:23 PM
  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