Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2016
    Posts
    13

    How to search for multiple criterias using comboBox?

    Hello everyone!



    This is my first post to this forum.
    I have a problem for so long that I can't figure it out how to solve.

    The problem is
    Suppose I have to search for multiple criterias. I have four comboBoxes
    named : cboColor, cboType, cboComposition, and cboTexture
    I would like to know how to use them to find some particular records.
    I also have textboxes named txtColor, txtType, txtComposition, txtTexture and txtID for example.

    In addition, I would like to know how to search for less than four criterias such as cboColor, cboType, and cboComposition
    but not cboTexture. And also I would like to know how to use command buttons such as GoToNextRecord , GotoPreviousRecord
    etc. for moving from one matched record to another.

    Please understand I have learned the program by myself. So many questions to ask
    Thank you anyone who kindly answers in advance.

    AccessPractice

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You cant use form boxes in a query if there's nothing in them..so..
    Test all controls for a possible filter then build the where clause.

    Code:
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
        'remove 1st And
    sWhere= mid(sWhere,4)
      'just use the filter
    me.filter = sWhere
    me.filterOn = true

  3. #3
    Join Date
    Apr 2016
    Posts
    13
    Hello ranman256,

    Thank you for your solution.
    I adapted your code to
    Code:
    Private Sub Command37_Click()
        
        Dim sWhere As String
        
        If Not IsNull(cboColor) Then sWhere = sWhere & "[Color]='" & cboColor & "'"
        If Not IsNull(cboTapeType) Then sWhere = sWhere & " and [TapeType]='" & cboTapeType & "'"
        If Not IsNull(cboYarnComposition) Then sWhere = sWhere & " and [YarnComposition]='" & cboYarnComposition & "'"
        If Not IsNull(cboFormerSize) Then sWhere = sWhere & " and [FormerSize]= '" & cboFormerSize & "'"
    
    
    'remove 1st And
    'sWhere = Mid(sWhere, 4)
    'just use the filter
    Me.Filter = sWhere
    Me.FilterOn = True
    End Sub
    It works well. However, if I don't select cboColor, sWhere is started with " and [TapeType]='" & cboTapeType & "' ..... "
    The "and" is an error.
    I have tried to move the "and" to other positions or remove some of them but I still can't solve the problem.
    Could you please tell me how to solve?

    BTW, I don't understand your comments in the code that are
    'remove 1st And
    'sWhere = Mid(sWhere, 4)
    I just first encounter the Mid function and read it in help but I don't understand how to use it in my scenario.

    Thank you
    AccessPractice

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

Similar Threads

  1. Replies: 3
    Last Post: 05-07-2015, 10:24 AM
  2. Replies: 1
    Last Post: 09-12-2013, 11:56 AM
  3. Replies: 19
    Last Post: 08-01-2013, 10:47 AM
  4. search a value with 2 criterias
    By Patougaffou in forum Queries
    Replies: 5
    Last Post: 07-26-2011, 10:50 AM
  5. Update Multiple fields according multiple criterias
    By denis.m in forum Programming
    Replies: 1
    Last Post: 02-21-2011, 11:03 AM

Tags for this Thread

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