Filter subform from main form input

[Guest]

I'm still a bit new to programming... I have a main form with employee data
[Employees] and a subform [Reviews] with data entered by year and quarter.
Somehow, I'd like the user to open the main form and select what year and
quarter's data should be displayed in the subform. The user could 'scan' thru
[Employees] viewing the related data [Reviews] without re-entering the
filter. I need all employees viewable, not just those with data. I would also
like to supply an option for selecting a new year/quarter without closing the
main form.

Thanks in advance,
JT

 

[Allen Browne]

You can set the Filter property of the subform so that it shows only records
from the specified quarter.

This example assumes an unbound text box named txtYear where the user enters
the 4-digit year, and another named txtQuarter where the user enters the
quarter (1 - 4.)

Dim strWhere As String
Dim dtQuarterStart As Date
Dim dtQuarterEnd As Date
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not (IsNull(Me.txtYear) Or IsNull(Me.txtQuarter)) Then
dtQuarterStart = DateSerial(Me.txtYear, 3 * (Me.txtQuarter - 1) + 1, 1)
dtQuarterEnd = DateAdd("m", 3, dtQuarterStart) - 1
strWhere = "[ReviewDate] Between " & Format(dtQuarterStart, strcJetDate)
& _
" And " & Format(dtQuarterEnd, strcJetDate)
With Me.[NameOfYourSubformControlHere].Form
.Filter = strWhere
.FilterOn = True
End With
End If

Note that if you are trying to filter the main form as well as the subform,
you are likely to run into this flaw in Access:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html