Unbound Field to filter a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Is it possible to use a an unbound field(s) to filter a sub-form. For
example, I would like to enter say a starting date, ending date, and last
name of a student. Then in the sub form display the results based on the
fields. I know how to all this stuff in a query with parameters, but I would
like to be able to achieve this in a form.


Any help would be great.

Thank you
David
 
Yes, you can filter the fields of the subform, building the filter string
from what the user enters into unbound controls.

This example shows how to build up the filter string from the non-blank
boxes, and then apply it to the subform:

Dim strWhere As String 'Filter string
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = "[EnrolDate] < " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = "[EnrolDate] > " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = "[EnrolDate] Between " & _
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

If Not IsNull(Me.txtFilterName) Then
If Len(strWhere) > 0 Then
strWhere = "(" & strWhere & ") AND "
End If
strWhere = strWhere & "([LastName] = """ & Me.txtFilterName & """)"
End If

With Me.[NameOfYourSubformControlHere].Form
.Filter = strWhere
.FilterOn = True
End With
 
Back
Top