Results 1 to 4 of 4
  1. #1
    whamilton is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    6

    Filter form by multiple combo box criteria

    I have a form with multiple Combo Boxes

    Major Command
    Component Command
    Command

    Directorate

    I'm able to filter the Subform from Major Command then filter to Component Command and then to Command. I need to be able to filter by directorate as well. Ultimately I'd like to be able to filter using:

    Major Command
    Component Command
    Command

    if Directorate has a value selected or not.

    The code I'm using the the Command filters looks like this:



    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Combo23_Change()
      Combo27.Value = Null
      Combo27.Requery
      Combo29.Value = Null
      Dim SQL As String
    
    
    
    
     SQL = "SELECT qry_Customers.[Major Command_ID], qry_Customers.[Major Command], qry_Customers.ComponentCommand_ID, " _
     & "qry_Customers.[Component Command], qry_Customers.Commands_ID, qry_Customers.Command, tbl_TaskNames.[Task Name], " _
     & "tbl_TaskNames.Task_ID, tbl_TaskNames.Stage, tbl_TaskNames.[Contract Type], tbl_TaskNames.Directorate, " _
     & "tbl_TaskNames.[Company Name], tbl_TaskNames.[Existing Contract], tbl_TaskNames.Owner, tbl_TaskNames.Product, " _
     & "tbl_TaskNames.[PoP Start], tbl_TaskNames.[PoP End], tbl_TaskNames.[Term Length], tbl_TaskNames.[Total Value], tbl_TaskNames.[GovWin Link] " _
     & "FROM tbl_TaskNames LEFT JOIN qry_Customers ON tbl_TaskNames.Customer = qry_Customers.Customer " _
     & "WHERE ((tbl_TaskNames.Stage)<6) " _
     & "And qry_Customers.[Major Command_ID]=" & Me.Combo23 & " " _
     & "ORDER BY tbl_TaskNames.[PoP Start] " _
    
    
    Me.frm_TaskNames_and_Customer2.Form.RecordSource = SQL
    Me.frm_TaskNames_and_Customer2.Form.Requery
    
    
    End Sub
    I've tried using the If ElseIf statement but it doesn't seem to be working. My current code for the the Major Command combo box is:

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Combo23_Change()
      Combo27.Value = Null
      Combo27.Requery
      Combo29.Value = Null
      Dim SQL As String
    
    
    
    
     SQL = "SELECT qry_Customers.[Major Command_ID], qry_Customers.[Major Command], qry_Customers.ComponentCommand_ID, " _
     & "qry_Customers.[Component Command], qry_Customers.Commands_ID, qry_Customers.Command, tbl_TaskNames.[Task Name], " _
     & "tbl_TaskNames.Task_ID, tbl_TaskNames.Stage, tbl_TaskNames.[Contract Type], tbl_TaskNames.Directorate, " _
     & "tbl_TaskNames.[Company Name], tbl_TaskNames.[Existing Contract], tbl_TaskNames.Owner, tbl_TaskNames.Product, " _
     & "tbl_TaskNames.[PoP Start], tbl_TaskNames.[PoP End], tbl_TaskNames.[Term Length], tbl_TaskNames.[Total Value], tbl_TaskNames.[GovWin Link] " _
     & "FROM tbl_TaskNames LEFT JOIN qry_Customers ON tbl_TaskNames.Customer = qry_Customers.Customer " _
     & "WHERE ((tbl_TaskNames.Stage)<6) " _
     & "And qry_Customers.[Major Command_ID]=" & Me.Combo23 & " " _
     & "ORDER BY tbl_TaskNames.[PoP Start] " _
    
    
     SQL2 = "SELECT qry_Customers.[Major Command_ID], qry_Customers.[Major Command], qry_Customers.ComponentCommand_ID, " _
     & "qry_Customers.[Component Command], qry_Customers.Commands_ID, qry_Customers.Command, tbl_TaskNames.[Task Name], " _
     & "tbl_TaskNames.Task_ID, tbl_TaskNames.Stage, tbl_TaskNames.[Contract Type], tbl_TaskNames.Directorate, " _
     & "tbl_TaskNames.[Company Name], tbl_TaskNames.[Existing Contract], tbl_TaskNames.Owner, tbl_TaskNames.Product, " _
     & "tbl_TaskNames.[PoP Start], tbl_TaskNames.[PoP End], tbl_TaskNames.[Term Length], tbl_TaskNames.[Total Value], tbl_TaskNames.[GovWin Link] " _
     & "FROM tbl_TaskNames LEFT JOIN qry_Customers ON tbl_TaskNames.Customer = qry_Customers.Customer " _
     & "WHERE ((tbl_TaskNames.Stage)<6) " _
     & "And qry_Customers.[Major Command_ID]=" & Me.Combo23 & " " _
     & "Ande tbl_TaskNames.Directorate=" & Me.Combo21 & " " _
     & ";"
    
    
    If IsNull(Me.Combo21) Then
    Me.frm_TaskNames_and_Customer2.Form.RecordSource = SQL
    Me.frm_TaskNames_and_Customer2.Form.Requery
     
    ElseIf NotNull(Me.Combo21) Then
    Me.frm_TaskNames_and_Customer2.Form.RecordSource = SQL2
    Me.frm_TaskNames_and_Customer2.Form.Requery
    
    
    End If
      
    End Sub
    I'm open to any suggestions.
    Attached Thumbnails Attached Thumbnails Form Header.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a continuous form that shows all records.
    In the header, put unbound controls for the user to enter search criteria.
    When user clicks the Find button, Test all controls for a possible filter then build the where clause:
    Code:
    sub btnFind_click()
    sWhere = "1=1"
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"
    
     'then filer
    if sWhere = "1=1" then
       me.filterOn = false
    else
       me.filter = sWhere
       me.filterOn = true
    endif
    end sub

  3. #3
    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 think there's a flaw in your logic. I'd try:

    Code:
    If IsNull(Me.Combo21) Then
      Me.frm_TaskNames_and_Customer2.Form.RecordSource = SQL
    Else
      Me.frm_TaskNames_and_Customer2.Form.RecordSource = SQL2
    End If
    But most of us would build a string as ranman has demonstrated rather than build multiple SQL strings.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388

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

Similar Threads

  1. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  2. Multiple Form Filter Criteria vba code
    By Moonman in forum Programming
    Replies: 6
    Last Post: 11-16-2013, 12:42 AM
  3. Filter a Form with Multiple Combo Boxes
    By Njliven in forum Forms
    Replies: 6
    Last Post: 01-03-2013, 01:25 PM
  4. Replies: 1
    Last Post: 12-04-2011, 06:33 PM
  5. Replies: 1
    Last Post: 02-03-2010, 08:17 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