Filter Form Code

[Larry Salvucci]

I have a form with 12 subforms on it that I use to filter my data by a
specific year. I have 1 subform for each month. The users will then be able
to choose a specific year from my combo box in the main forms header. Then
the subforms are filtered to that year. This is the code I'm using in the
after update event of my combo box. Is there a way to simplify this code
instead of making it so repetitive?

Dim MyRecSet As Object
Dim MyRecSet1 As Object
Dim MyRecSet2 As Object
Dim MyRecSet3 As Object
Dim MyRecSet4 As Object
Dim MyRecSet5 As Object
Dim MyRecSet6 As Object
Dim MyRecSet7 As Object
Dim MyRecSet8 As Object
Dim MyRecSet9 As Object
Dim MyRecSet10 As Object
Dim MyRecSet11 As Object

Set MyRecSet = Me.frmPerformance01.Form.RecordsetClone
Set MyRecSet1 = Me.frmPerformance02.Form.RecordsetClone
Set MyRecSet2 = Me.frmPerformance03.Form.RecordsetClone
Set MyRecSet3 = Me.frmPerformance04.Form.RecordsetClone
Set MyRecSet4 = Me.frmPerformance05.Form.RecordsetClone
Set MyRecSet5 = Me.frmPerformance06.Form.RecordsetClone
Set MyRecSet6 = Me.frmPerformance07.Form.RecordsetClone
Set MyRecSet7 = Me.frmPerformance08.Form.RecordsetClone
Set MyRecSet8 = Me.frmPerformance09.Form.RecordsetClone
Set MyRecSet9 = Me.frmPerformance10.Form.RecordsetClone
Set MyRecSet10 = Me.frmPerformance11.Form.RecordsetClone
Set MyRecSet11 = Me.frmPerformance12.Form.RecordsetClone

MyRecSet.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet1.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet2.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet3.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet4.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet5.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet6.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet7.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet8.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet9.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet10.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet11.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search

 

[Allen Browne]

I'm not sure exactly where you are going with this: you do a findfirst, but
that doesn't filter it.

Something like this would set the RecordSource of the subform so it limits
it to one year (based on YearCount field), and one month of that year (based
on TheMonth field):

Dim strWhere As String
Dim strSubform As String
Dim i As Integer
Const strcStub = "SELECT * FROM Table1 " & vbCrLf
Const strcTail = " ORDER BY SomeField;"

For i = 0 To 11
strWhere = "WHERE ((YearCount = " & Me.cboYear & _
""") AND (TheMonth = " & i + 1 & ")) " & vbCrLf
strSubform = "frmPerformance" & Format(i, "00")
Me(strSubform).Form.RecordSource = _
strcStub & strWhere & strcTail
Next

From your example, it appears YearCount is a Text field (not a number
field.) If TheMonth is a text field too, you need the extra quotes for it
too. Explanation of the quotes:
http://allenbrowne.com/casu-17.html

The idea is to change the RecordSource of the form, by patching in the WHERE
clause of the query into the right spot. This avoids the bugs where Access
doesn't cope if you filter both the main form and subform:
http://allenbrowne.com/bug-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with 12 subforms on it that I use to filter my data by a
specific year. I have 1 subform for each month. The users will then be
able
to choose a specific year from my combo box in the main forms header. Then
the subforms are filtered to that year. This is the code I'm using in the
after update event of my combo box. Is there a way to simplify this code
instead of making it so repetitive?

Dim MyRecSet As Object
Dim MyRecSet1 As Object
Dim MyRecSet2 As Object
Dim MyRecSet3 As Object
Dim MyRecSet4 As Object
Dim MyRecSet5 As Object
Dim MyRecSet6 As Object
Dim MyRecSet7 As Object
Dim MyRecSet8 As Object
Dim MyRecSet9 As Object
Dim MyRecSet10 As Object
Dim MyRecSet11 As Object

Set MyRecSet = Me.frmPerformance01.Form.RecordsetClone
Set MyRecSet1 = Me.frmPerformance02.Form.RecordsetClone
Set MyRecSet2 = Me.frmPerformance03.Form.RecordsetClone
Set MyRecSet3 = Me.frmPerformance04.Form.RecordsetClone
Set MyRecSet4 = Me.frmPerformance05.Form.RecordsetClone
Set MyRecSet5 = Me.frmPerformance06.Form.RecordsetClone
Set MyRecSet6 = Me.frmPerformance07.Form.RecordsetClone
Set MyRecSet7 = Me.frmPerformance08.Form.RecordsetClone
Set MyRecSet8 = Me.frmPerformance09.Form.RecordsetClone
Set MyRecSet9 = Me.frmPerformance10.Form.RecordsetClone
Set MyRecSet10 = Me.frmPerformance11.Form.RecordsetClone
Set MyRecSet11 = Me.frmPerformance12.Form.RecordsetClone

MyRecSet.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34) 'string
search
MyRecSet1.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet2.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet3.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet4.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet5.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet6.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet7.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet8.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet9.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet10.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search
MyRecSet11.FindFirst "[YearCount]=" & Chr(34) & Me.cboYear & Chr(34)
'string
search