Results 1 to 2 of 2
  1. #1
    Bunta is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    1

    Email search function using combo box and drop down controls

    Hi all,



    I'm struggling to write some code which uses the result of up 6 checkboxes. Each checkbox stands for a different dataset and the plan is to extract email address based on the datasets selected.

    For example at one point in time I may only want address for people in one dataset, but the next time I use it it maybe that I want to select all the people in say 2 or 3 of the datasets.

    Once the datasets have been selected, I then need to add another criteria such as whether they are a supplier or a customer from a drop down box. Essentially I'm trying to create a search form to select as many datasets as needed and once done then to select the type.

    I'm struggling with the logic behind it. Currently once I select the datasets, it then doesn't apply the additional restriction of the type and brings back all rows based on the dataset selection.

    My code is as below, and a screen shot of the skeleton view of the form can be found at: http://imageshack.us/photo/my-images/51/84341811.png/

    I'm not an expert at VB and very much cobbling some of this together from googled results. I very much appreciate any help that any one is able to provide as I'm at a loss of what to do to solve this.

    Many thanks

    Code:
    Private Function BuildFilter() As Variant
        Dim varWhere As Variant
        Dim varItem As Variant
        Dim intIndex As Integer
        varWhere = Null  ' Main filter
            
        ' Check for set1
        If Me.cmbset1 = -1 Then
            varWhere = varWhere & "([DS_set1] = True) OR "
        ElseIf Me.cmbset2 = -1 Then
            varWhere = varWhere & "([DS_set2] = True) OR "
        ElseIf Me.cmbset3 = -1 Then
            varWhere = varWhere & "([DS_set3] = True) OR "
        ElseIf Me.cmbset4 = -1 Then
            varWhere = varWhere & "([DS_set4] = True) OR "
        ElseIf Me.cmbset5 = -1 Then
            varWhere = varWhere & "([DS_set5] = True) OR "
        ElseIf Me.cmbset6 = -1 Then
            varWhere = varWhere & "([DS_set6] = True) AND "
        End If
        
            ' Check for Category
        If Not IsNull(Me.cmbCategory) Then
          varWhere = varWhere & "([Category] = """ & Me.cmbCategory & """) AND "
        End If
     
            ' Check if there is a filter to return...
        If IsNull(varWhere) Then
            varWhere = ""
            
        Else
            ' strip off last logic operator in the filter
            If Right(varWhere, 5) = " AND " Then
                varWhere = Left(varWhere, Len(varWhere) - 5)
            Else
                varWhere = Left(varWhere, Len(varWhere) - 4)
            End If
                       
        End If
        
        BuildFilter = varWhere
    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on exactly what the problem is. This may help debug the SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    If you want to apply more than one of the check boxes, you wouldn't want to use ElseIf. As is, if cmbset1 is true, none of the others will be checked.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 03-10-2012, 06:15 PM
  2. Replies: 1
    Last Post: 02-20-2012, 10:56 PM
  3. Formating a Drop Down Combo Box
    By Douglas Post in forum Forms
    Replies: 3
    Last Post: 02-01-2012, 03:18 PM
  4. Drop down combo box search has glitch??
    By robsworld78 in forum Forms
    Replies: 1
    Last Post: 06-02-2011, 08:06 PM
  5. Collecting data via email with drop downs
    By tlyons in forum Access
    Replies: 2
    Last Post: 01-19-2011, 10:34 AM

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