Filter records in a split view form based on a combobox on the form's header

better upload a sample db.

One of my forms has three selection options.  The afterUpdate event for each contains only one line of code:
Me.Requery

The form's RecordSource query references the criteria combos in the form's header:

SELECT tblEmployees.*
FROM tblEmployees
WHERE (tblEmployees.StatusID =[forms]![frmEmployees]![txtFindStatusID] OR [forms]![frmEmployees]![txtFindStatusID] Is null)
AND (tblEmployees.EmpID = [forms]![frmEmployees]![cboFindEmployee] OR  [forms]![frmEmployees]![cboFindEmployee] Is Null)
AND (tblEmployees.EmpID = [forms]![frmEmployees]![cboFindLogin] OR  [forms]![frmEmployees]![cboFindLogin] Is Null)
AND (tblEmployees.EmpID = [forms]![frmEmployees]![cboFindOldClientID] OR  [forms]![frmEmployees]![cboFindOldClientID] Is Null)
ORDER BY tblEmployees.FirstName, tblEmployees.LastName;

The criteria might look odd but each of the three combos is actually bound to the EmpID.  It is just that they show a different value.  So the first shows the Employee's name, the second his login ID, and the third his ID from the old FoxPro system but each is a unique identifier for Employee.

Use the combobox wizard to put a combobox on the form.
(You can type in the years manual when you create the combobox)
Select: "I will type in the values I want"

Then use code similar to this to filter the start ates for the selected year.:
Private Sub YourYearComboBox_AfterUpdate()
    Me.Filter = "Year(StartDate)=" & Me.YourYearComboBox
    Me.FilterOn = True
End Sub

simple sample attached

JeffCoachman
Database66.mdb

I should have mentioned why I always use selection criteria rather than filters.  The reason is that most of my applications either use SQL Server (or some other RDBMS) or are destined to be upsized in the future.  Although filtering works fine when the BE is Jet/ACE, it is NOT fine when the BE is a real RDBMS with server-side processing capabilities.  To use the old Access-style filtering requires retrieving the ENTIRE set of data from the server and then filtering it locally.  That completely eliminates any benefit you might derive from having the server do the heavy lifting.  Of course if you are working with tables of only a few thousand rows, you can do pretty much whatever you want.  Some of my tables run to millions of rows.  I certainly don't want to download millions of rows to memory on my local PC in order to use Access-style filtering so that is why I use queries with criteria.  The server selects the records and only returns what I selected which is a significantly more efficient operation.

I have gotten to the point where I use the criteria technique regardless of whether or not the BE will ever be anything other than Jet/ACE.  Both methods work with Jet/ACE/SQL Server but only the criteria method is appropriate for SQL Server et al.  Consequently, my apps can be upsized at the drop of a hat with little to no change required and will work efficiently in both environments.

This is what I have so far.  Everything works until the code gets to the line where I'm trying to filter between dates:

I'm pretty sure this is the line of code that is breaking it:

strWhere = strWhere & "[CNSTRCTN_START_DT_EST] Between me.txtCurrentDate and me.txtThroughDate AND "


My code:

Private Sub FilterSubform()
    Dim strWhere As String

    If Nz(Me.cbo_PROGRAM_WEEK_VIEW, "") <> "" Then
        strWhere = strWhere & "[PROGRAM_INIT] =  '" & Me.cbo_PROGRAM_WEEK_VIEW & "' AND "
    End If

    If Nz(Me.cboNUM_WKS_TO_VIEW, "") <> "" Then
        strWhere = strWhere & "[CNSTRCTN_START_DT_EST] = '" & Me.cboNUM_WKS_TO_VIEW & "' AND "
    End If

    If Nz(Me.cboPROGRAM_YEAR_WEEK_VIEW, "") <> "" Then
       
        strWhere = strWhere & "[CNSTRCTN_START_DT_EST] Between me.txtCurrentDate and me.txtThroughDate AND "
        
    End If

    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.Form.Filter = strWhere
        Me.Form.FilterOn = True
    Else
        Me.Form.Filter = ""
        Me.Form.FilterOn = False
    End If
End Sub

Select allOpen in new window