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.