SubReport Criteria

[pbrown77]

Perhaps since the solution is most likely within the form, I should ask here......

The form I am using allows the user to select a facility and run a report via a button (code listed below). The problem is with the subreport (both main & sub share same query as control source). The main report runs with the criteria chosen from the form, however the sub report does not use the criteria and pulls all data. How can I prevent this?

As you can notice towards the bottom of the code, I tried opening the subreport by itself then minimizing it before opening the main report... Does not work The sub report opened by itself has the criteria but the one in the main report does not.

CODE:

Private Sub Command54_Click()

Dim strWhere As String
Dim strwhere1 As String
Dim strwhere2 As String

If [Location] = 0 Then
MsgBox "No facility selected"
Exit Sub
End If

Select Case Me.Location
Case 1
strwhere1 = "([CC Summary 2nd Level] = 1340) OR " _
& "([CC Summary 2nd Level] > 3999 AND " _
& "[CC Summary 2nd Level] <> 9500 AND " _
& "[CC Summary 2nd Level] Not Between 7700 And 7799)"
Case 2
strwhere1 = "([CC Summary 2nd Level] In (1350, 1800, 1801, 1805, 1905, 9500)) OR " _
& "([CC Summary 2nd Level] Between 3000 and 3999) OR " _
& "([CC Summary 2nd Level] Between 7700 and 7799)"
End Select

strwhere2 = "([Attendance Code] In ('E', 'OA'))"
strWhere = "(" & strwhere1 & ")" & " AND " & "(" & strwhere2 & ")"

If DCount("*", "F - Query for Reports", strWhere) = 0 Then
MsgBox "There is no current E Code data for the choosen facility", vbInformation
Else
DoCmd.CLOSE acForm, "ReportChoices"
'DoCmd.OpenReport "D1 - ECode Sub", acViewPreview, , strWhere
'DoCmd.Minimize
DoCmd.OpenReport "D - ECode", acViewPreview, , strWhere
End If

End Sub