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

I have a form in split view.  The header of the form has a combobox where the user can select a year.  For example 2015.

The detail of the split form as a field named "StartDate".  (txtStartDate).  This field can contain values suach as 1/1/2013, 2/13/2013, 5/15/2015, etc.

When the user has selected a year in the combobox I want the detail records to display only those records where txtStartDate is in 2015.

Who is Participating?
You can't embed form field references in the filter.  You need to convert the values to strings.

strWhere = strWhere & "[CNSTRCTN_START_DT_EST] Between #" & me.txtCurrentDate & "# and #" & me.txtThroughDate & "# AND "
Rey Obrero (Capricorn1)Commented:
better upload a sample db.
One of my forms has three selection options.  The afterUpdate event for each contains only one line of code:

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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Jeffrey CoachmanMIS LiasonCommented:
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

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.
SteveL13Author Commented:
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
        Me.Form.Filter = ""
        Me.Form.FilterOn = False
    End If
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.