Dynamic Filter of a Subreport Problem

[RAman]

Dear Gurus:

I am trying to dynamically filter a report and two subreports (the
second sub report is a subreport of the 1st) with three different combo
boxes on a form. The intent is illustrated below:

Combobox1 -> Filter Main Report
Combobox2 -> Filter SubReport(Level 1)
Combobox3 -> FilterSubReport(Level 2)

I am not a VBA expert, so I have attempted to exprapolate some code
from a Sample MS database called RptSampl.

The sample code looks like this:

---------BEGIN---------------
Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptCustomers].Filter = strSQL
Reports![rptCustomers].FilterOn = True
Else
Reports![rptCustomers].FilterOn = False
End If

End Sub

----END CODE------

Since I only have three comboboxes, i thought that the following
modification might work. I was attempting to change each filter, one at
a time. However, The subreports aren't open when the main report is
open, so I am stuck. Please advise!

------MY CODE BEGIN--------------

Private Sub Command28_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 1
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptCalls].Filter = strSQL
Reports![rptCalls].FilterOn = True
Else
Reports![rptCalls].FilterOn = False
End If

For intCounter = 2 To 2
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![Relationships].Filter = strSQL
Reports![Relationships].FilterOn = True
Else
Reports![Relationships].FilterOn = False
End If

For intCounter = 3 To 3
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " &
" = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![Calls].Filter = strSQL
Reports![Calls].FilterOn = True
Else
Reports![Calls Query].FilterOn = False
End If

End Sub

-----MY CODE END--------