Here is the code I finally got to work. The big problem is I left out calling a few of the sub procedures during the "on click" events that triggered the filter.
Code:
Option Compare Database
Option Explicit
Private SQL As String
Private strtglWhere As String
Private strcboWhere As String
Private WhereStatement As String
Private Sub EntrySQL()
SQL = "SELECT tblEntry.EntryID, tblEntry.Entry_ProjectCodeIDfk, tblEntry.Entry_CostCenterIDfk, tblEntry.Entry_Description, " _
& "tblEntry.Entry_ProductSKU, tblEntry.Entry_UnitPrice, tblEntry.Entry_QuantityofUnits, tblEntry.Entry_VendorIDfk, " _
& "tblEntry.Entry_PurchaseOrderIDfk, tblEntry.Entry_EventYear, tblEntry.Entry_ReceiptDate, tblEntry.Entry_ReceiptNumber, " _
& "tblEntry.Entry_BAFIDfk, qryEntry.Entry_TotalCost FROM qryEntry " _
& WhereStatement
Me.frmEntry_sub.Form.RecordSource = SQL
Me.frmEntry_sub.Form.Requery
End Sub
Private Sub ComboBoxFilter()
Dim Part1cbo As String
Dim Part2cbo As String
Dim Part3cbo As String
If Nz(Me.[cboProjectCode], "") <> "" Then
Part1cbo = "tblEntry.Entry_ProjectCodeIDfk=" & Me.[cboProjectCode] & " And "
Else
Part1cbo = ""
End If
If Nz(Me.[cboCostCenter], "") <> "" Then
Part2cbo = "tblEntry.Entry_CostCenterIDfk=" & Me.[cboCostCenter] & " And "
Else
Part2cbo = ""
End If
If Nz(Me.[cboYear], "") <> "" Then
Part3cbo = "tblEntry.Entry_EventYear=" & Me.[cboYear] & " And "
Else
Part3cbo = ""
End If
strcboWhere = Part1cbo & Part2cbo & Part3cbo
If Nz(strcboWhere, "") <> "" Then
strcboWhere = Left(strcboWhere, Len(strcboWhere) - 4)
End If
End Sub
'''''''''''''''''''''
Private Sub ToggleFilter()
Dim Part1tgl As String
Dim Part2tgl As String
Dim Part3tgl As String
Dim Part4tgl As String
If tglBudget = True Then
Part1tgl = "tblEntry.Entry_BAFIDfk=" & 1 & " Or "
Else
Part1tgl = ""
End If
If tglJatasa = True Then
Part2tgl = "tblEntry.Entry_BAFIDfk=" & 2 & " Or "
Else
Part2tgl = ""
End If
If tglTracked = True Then
Part3tgl = "tblEntry.Entry_BAFIDfk=" & 3 & " Or "
Else
Part3tgl = ""
End If
If tglForecast = True Then
Part4tgl = "tblEntry.Entry_BAFIDfk=" & 4 & " Or "
Else
Part4tgl = ""
End If
strtglWhere = Part1tgl & Part2tgl & Part3tgl & Part4tgl
If Nz(strtglWhere, "") <> "" Then
strtglWhere = Left(strtglWhere, Len(strtglWhere) - 4)
End If
End Sub
Private Sub WhereStatementsub()
'If Nz(strcboWhere & strtglWhere, "") <> "" Then
'WhereStatement = " Where " & strcboWhere & strtglWhere
'Else
'WhereStatement = ""
'End If
If Len(strcboWhere) >= 1 And Len(strtglWhere) >= 1 Then
WhereStatement = " Where (" & strcboWhere & ") And (" & strtglWhere & ")"
ElseIf Len(strcboWhere) >= 1 Then
WhereStatement = " Where " & strcboWhere
ElseIf Len(strtglWhere) >= 1 Then
WhereStatement = " Where " & strtglWhere
Else
WhereStatement = ""
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''
Private Sub Form_Load()
EntrySQL
End Sub
Private Sub btnResetForm_Click()
DoCmd.Close acForm, "frmEntry", acSaveYes
DoCmd.OpenForm "frmEntry", acNormal
End Sub
'''''''''''''''''''''''''''''''''''''''''''''
Private Sub cboCostCenter_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
Private Sub cboProjectCode_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
Private Sub cboYear_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
'''''''''''''''''''''''''''''''''''''''''
Private Sub tglBudget_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
Private Sub tglForecast_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
Private Sub tglJatasa_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
Private Sub tglTracked_AfterUpdate()
Call ComboBoxFilter
Call ToggleFilter
Call WhereStatementsub
Call EntrySQL
End Sub
''''''''''''''''''''''''''''''''''''''''''
Private Sub txtMessage_Click()
Dim TextMsg As String
MsgBox WhereStatement
End Sub