Command button filter on a subform

[timothy.burt]

I have a form which is the main form frm_Projects bound to
tbl_Projects
I have subform on the main form subfrm_Risks which is bound to
tbl_Risks
The tbls are linked by field ProjectID

I would like to put a command button on the main form to filter by
month on the subform using the subform control Updated

I would like to filter by month...so I need to filter between dates in
that field

My programming is virtually non-existent and I have tried finding an
anwer to this query everywhere but can't find one that works for
me.... please help! Many thanks

 

[Allen Browne]

Okay, lets assume you are showing the subform in Continuous View (not
Datasheet view) so you can see the Form Header section.

1. Open the subform in design view.

2. If you don't see a Form Header section, add it (View menu in Access 2003
or earlier; right side of the Arrange tab of the ribbon in Access 2007.)

3. Add 2 unbound text boxes to the Form Header section, for entering the
dates. Set their Name property to txtStartDate and txtEndDate. Set their
Format property to General Date, so they don't accept bad dates.

4. Add a command button beside the text boxes.
Name it (say) cmdFilter.
Set its AfterUpdate property to:
[Event Procedure]
Click the Build button (...) beside the property.
Access opens the code window.
Set up the code like this:

Private Sub cmdFilter_Click()
Dim strWhere As String 'Where condition for OpenReport.
Dim strField As String 'Name of your date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strField = "Updated" 'or whatever your date field is.
If Me.Dirty Then Me.Dirty = False

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & _
Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & _
Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & _
Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
If strWhere = vbNullString Then
Me.FilterOn = False 'Show all records
Else
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Note that there is a bug in Access where it doesn't work correctly if you
filter both the main form and subform. It's fixed in Access 2007, but if you
are using an earlier version, see:
http://allenbrowne.com/bug-02.html

 

[timothy.burt]

Wow - that's amazing! Thank you so much for your time and assistance -
it really is greatly appreciated

Tim