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.

???
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
better upload a sample db.
0
PatHartmanCommented:
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.
0
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

JeffCoachman
Database66.mdb
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Open in new window

0
PatHartmanCommented:
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 "
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.