SubReport Not Using Criteria

[pbrown77]

I have a report and a subreport tied to the same query. To run the report the user selects criteria off a form and presses a button.
The problem is that the main report runs with the criteria, however, the subreport does not.
How do I fix this?

Here is the current 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 "D - ECode", acViewPreview, , strWhere
End If

End Sub

 

[dhookom]

You might need to change the SQL property of the saved query the subreport is based on. Either that or add criteria to the subreport record source likeL

Between....

The filter of the main report will not apply to the subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

Both reports have the same query as their control source.

So, how can I change the SQL (I don't know how anyways), and not change the main form?

 

[dhookom]

You can change the SQL property of a saved query using code like:

Dim strSQL as String
strSQL = "SELECT ... FROM ... WHERE ...."
CurrentDb.QueryDefs("qselYourQuery").SQL = strSQL

Your specific code would "roll-up" your where clause to use in the saved query.

This would need to be done prior to opening the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

I have added the Dim strSQL as String at the top of the code

The problem is that it says that there is a missing syntax and the debugger goes directly to:
CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL

As you said, I placed the code before the report opens. Below shows the bottom portion of the code.




strSQL = "SELECT ... FROM ... WHERE ...."
CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL

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

 

[dhookom]

You need to review the strSQL to debug it. Try placing a debug.print strSQL and a break point. Copy the sql into your clipboard and paste it into a sql view to see if it works. Maybe even paste the sql back in a reply so we can see it.

You may need to set a reference to the Microsoft DAO 3.x library.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

Now I am at a loss. But will go ahead and do the following to see if I can do it.

In the code right after
strSQL = "SELECT ... FROM ... WHERE ...."
CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL

I will put

debug.print strSQL
end sub

..... No joy. all I get is the same error msg.

I am going to go to the help and see what I can find on the debug.print

but if you have a suggestion I am willing..

 

[dhookom]

Place the debug.Pring strSQL before the line that gives the error.

Did you check to see if you have a reference set to the DAO library?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

Okay with the code before the line:
As you can see the original code is there just not being used (') and I added a end sub (that is the only way I can think of to add a break). As to the DAO library, it is currently set to DAO 3.6 Object Library.



Dim strSQL As String
Dim strWhere As String
Dim strwhere1 As String
Dim strwhere2 As String
Me.CCS1 = 0

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 & ")"

strSQL = "SELECT ... FROM ... WHERE ...."
Debug.Print strSQL
CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL



End Sub


'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

 

[dhookom]

I expected you to replace the "SELECT ... FROM ... WHERE ...." with the appropriate sql from your query. You should have your tables and fields in there etc.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

I guess that is where I am getting lost... Since both the main and sub reports run from the same query, which gets its criteria from the form, Where do I get the query sql?

 

[dhookom]

Open the query in sql view. Copy and paste the sql into your code so that you can create the full sql with criteria in the strSQL variable. Once you have created a new sql string for the query, set the sql property of the querydef to the new strSQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

Here is the entire SQL from the query:
SELECT [Weekly Reporting Table - Current].[Cost Center], [Shift Table].[Shift Name], [Weekly Reporting Table - Current].[Work Cycle], [Weekly Reporting Table - Current].[Record Type], [Weekly Reporting Table - Current].[CC Summary 1st level], [Weekly Reporting Table - Current].[CC Summary 2nd level], [Weekly Reporting Table - Current].[CC Description], [Weekly Reporting Table - Current].Date, [Weekly Reporting Table - Current].[Labor Code], [Weekly Reporting Table - Current].[Shift (D/N)], [Weekly Reporting Table - Current].[Attendance Code], [Weekly Reporting Table - Current].Description, [Weekly Reporting Table - Current].Hours, [Weekly Reporting Table - Current].Heads, [Weekly Reporting Table - Current].Roll, [Weekly Reporting Table - Current].Vac, [Weekly Reporting Table - Current].Sick, [Weekly Reporting Table - Current].[Q Day], [Weekly Reporting Table - Current].Pers, [Weekly Reporting Table - Current].[W/Comp], [Weekly Reporting Table - Current].[A & S], [Weekly Reporting Table - Current].[Pers Leave], [Weekly Reporting Table - Current].LOA, [Weekly Reporting Table - Current].Jury, [Weekly Reporting Table - Current].Brvmt, [Weekly Reporting Table - Current].Military, [Weekly Reporting Table - Current].Tardy, [Weekly Reporting Table - Current].Injury, [Weekly Reporting Table - Current].[Fam Med], [Weekly Reporting Table - Current].[Outside Total], [Weekly Reporting Table - Current].Med, [Weekly Reporting Table - Current].HR, [Weekly Reporting Table - Current].Other, [Weekly Reporting Table - Current].Train, [Weekly Reporting Table - Current].[Offline Total], [Weekly Reporting Table - Current].[A/M], [Weekly Reporting Table - Current].LDR, [Weekly Reporting Table - Current].Days, [Weekly Reporting Table - Current].[WE Days], [Weekly Reporting Table - Current].[Maint Days], [Weekly Reporting Table - Current].[Actual Roll], [Weekly Reporting Table - Current].[Total Outside], [Weekly Reporting Table - Current].[Total Offline], [Weekly Reporting Table - Current].Working, [Weekly Reporting Table - Current].[Total Vac], [Weekly Reporting Table - Current].[Total Sick], [Weekly Reporting Table - Current].[Total Q Day], [Weekly Reporting Table - Current].[Total Pers], [Weekly Reporting Table - Current].[Total Work Comp], [Weekly Reporting Table - Current].[Total A&S], [Weekly Reporting Table - Current].[Total Per Leave], [Weekly Reporting Table - Current].[Total Fam Med], [Weekly Reporting Table - Current].[Total Brvmt], [Weekly Reporting Table - Current].[Total Jury], [Weekly Reporting Table - Current].[Total Injury], [Weekly Reporting Table - Current].[Total Tardy], [Weekly Reporting Table - Current].[Total Military], [Weekly Reporting Table - Current].[Total Med], [Weekly Reporting Table - Current].[Total HR], [Weekly Reporting Table - Current].[Total Other], [Weekly Reporting Table - Current].[Total Training], [Weekly Reporting Table - Current].[Total LOA], [Weekly Reporting Table - Current].[Total Online], [Weekly Reporting Table - Current].[Other Abs], [Cost Center Roll-up].Department, [Cost Center Roll-up].Order, [Weekly Reporting Table - Current].ActHeads, [Weekly Reporting Table - Current].StartDate, [Weekly Reporting Table - Current].EndDate
FROM ([Weekly Reporting Table - Current] INNER JOIN [Shift Table] ON [Weekly Reporting Table - Current].[Shift (D/N)] = [Shift Table].[Shift D/N]) INNER JOIN [Cost Center Roll-up] ON [Weekly Reporting Table - Current].[Cost Center] = [Cost Center Roll-up].[Cost Center];


Therefore Here is what the code now looks like:
Dim strSQL As String
Dim strWhere As String
Dim strwhere1 As String
Dim strwhere2 As String
Me.CCS1 = 0

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 & ")"
'Debug.Print strSQL
strSQL = ""
SELECT [Weekly Reporting Table - Current].[Cost Center], [Shift Table].[Shift Name], [Weekly Reporting Table - Current].[Work Cycle], [Weekly Reporting Table - Current].[Record Type], [Weekly Reporting Table - Current].[CC Summary 1st level], [Weekly Reporting Table - Current].[CC Summary 2nd level], [Weekly Reporting Table - Current].[CC Description], [Weekly Reporting Table - Current].Date, [Weekly Reporting Table - Current].[Labor Code], [Weekly Reporting Table - Current].[Shift (D/N)], [Weekly Reporting Table - Current].[Attendance Code], [Weekly Reporting Table - Current].Description, [Weekly Reporting Table - Current].Hours, [Weekly Reporting Table - Current].Heads, [Weekly Reporting Table - Current].Roll, [Weekly Reporting Table - Current].Vac, [Weekly Reporting Table - Current].Sick, [Weekly Reporting Table - Current].[Q Day], [Weekly Reporting Table - Current].Pers, [Weekly Reporting Table - Current].[W/Comp], [Weekly Reporting Table - Current].[A & S], [Weekly Reporting Table - Current].[Pers Leave
], [Weekly Reporting Table - Current].LOA, [Weekly Reporting Table - Current].Jury, [Weekly Reporting Table - Current].Brvmt, [Weekly Reporting Table - Current].Military, [Weekly Reporting Table - Current].Tardy, [Weekly Reporting Table - Current].Injury, [Weekly Reporting Table - Current].[Fam Med], [Weekly Reporting Table - Current].[Outside Total], [Weekly Reporting Table - Current].Med, [Weekly Reporting Table - Current].HR, [Weekly Reporting Table - Current].Other, [Weekly Reporting Table - Current].Train, [Weekly Reporting Table - Current].[Offline Total], [Weekly Reporting Table - Current].[A/M], [Weekly Reporting Table - Current].LDR, [Weekly Reporting Table - Current].Days, [Weekly Reporting Table - Current].[WE Days], [Weekly Reporting Table - Current].[Maint Days], [Weekly Reporting Table - Current].[Actual Roll], [Weekly Reporting Table - Current].[Total Outside], [Weekly Reporting Table - Current].[Total Offline], [Weekly Reporting Table - Current].Working, [Weekly Reporting Table - Current].[To
tal Vac], [Weekly Reporting Table - Current].[Total Sick], [Weekly Reporting Table - Current].[Total Q Day], [Weekly Reporting Table - Current].[Total Pers], [Weekly Reporting Table - Current].[Total Work Comp], [Weekly Reporting Table - Current].[Total A&S], [Weekly Reporting Table - Current].[Total Per Leave], [Weekly Reporting Table - Current].[Total Fam Med], [Weekly Reporting Table - Current].[Total Brvmt], [Weekly Reporting Table - Current].[Total Jury], [Weekly Reporting Table - Current].[Total Injury], [Weekly Reporting Table - Current].[Total Tardy], [Weekly Reporting Table - Current].[Total Military], [Weekly Reporting Table - Current].[Total Med], [Weekly Reporting Table - Current].[Total HR], [Weekly Reporting Table - Current].[Total Other], [Weekly Reporting Table - Current].[Total Training], [Weekly Reporting Table - Current].[Total LOA], [Weekly Reporting Table - Current].[Total Online], [Weekly Reporting Table - Current].[Other Abs], [Cost Center Roll-up].Department, [Cost Center Roll-up].Ord
er , [Weekly Reporting Table - Current].ActHeads, [Weekly Reporting Table - Current].StartDate, [Weekly Reporting Table - Current].EndDate
FROM ([Weekly Reporting Table - Current] INNER JOIN [Shift Table] ON [Weekly Reporting Table - Current].[Shift (D/N)] = [Shift Table].[Shift D/N]) INNER JOIN [Cost Center Roll-up] ON [Weekly Reporting Table - Current].[Cost Center] = [Cost Center Roll-up].[Cost Center];
"

CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL

'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

The error now highligts: (I ctrl C the what was higlighted:

SELECT [Weekly Reporting Table - Current].[Cost Center], [Shift Table].[Shift Name], [Weekly Reporting Table - Current].[Work Cycle], [Weekly Reporting Table - Current].[Record Type], [Weekly Reporting Table - Current].[CC Summary 1st level], [Weekly Reporting Table - Current].[CC Summary 2nd level], [Weekly Reporting Table - Current].[CC Description], [Weekly Reporting Table - Current].Date, [Weekly Reporting Table - Current].[Labor Code], [Weekly Reporting Table - Current].[Shift (D/N)], [Weekly Reporting Table - Current].[Attendance Code], [Weekly Reporting Table - Current].Description, [Weekly Reporting Table - Current].Hours, [Weekly Reporting Table - Current].Heads, [Weekly Reporting Table - Current].Roll, [Weekly Reporting Table - Current].Vac, [Weekly Reporting Table - Current].Sick, [Weekly Reporting Table - Current].[Q Day], [Weekly Reporting Table - Current].Pers, [Weekly Reporting Table - Current].[W/Comp], [Weekly Reporting Table - Current].[A & S], [Weekly Reporting Table - Current].[Pers Leave

 

[dhookom]

You would need code like

[red]'build the first part of the sql string[/red]
strSQL = "SELECT WR.[Cost Center], ST.[Shift Name], WR.[Work Cycle], WR.[Record Type], " & _
	"WR.[CC Summary 1st level], WR.[CC Summary 2nd level], WR.[CC Description], " 
strSQL = strSQL & "WR.Date, WR.[Labor Code], WR.[Shift (D/N)], WR.[Attendance Code], " & _
	"WR.Description, WR.Hours, WR.Heads, WR.Roll, WR.Vac, WR.Sick, WR.[Q Day], " 
strSQL = strSQL & "WR.Pers, WR.[W/Comp], WR.[A & S], WR.[Pers Leave], WR.LOA, WR.Jury, " & _
	"WR.Brvmt, WR.Military, WR.Tardy, WR.Injury, WR.[Fam Med], WR.[Outside Total], "
strSQL = strSQL & "WR.Med, WR.HR, WR.Other, WR.Train, WR.[Offline Total], WR.[A/M], " & _
	"WR.LDR, WR.Days, WR.[WE Days], WR.[Maint Days], WR.[Actual Roll], " 
strSQL = strSQL & "WR.[Total Outside], WR.[Total Offline], WR.Working, WR.[Total Vac], " & _
	"WR.[Total Sick], WR.[Total Q Day], WR.[Total Pers], WR.[Total Work Comp], "
strSQL = strSQL & "WR.[Total A&S], WR.[Total Per Leave], WR.[Total Fam Med], " & _
	"WR.[Total Brvmt], WR.[Total Jury], WR.[Total Injury], WR.[Total Tardy], "
strSQL = strSQL & "WR.[Total Military], WR.[Total Med], WR.[Total HR], WR.[Total Other], " & _
	"WR.[Total Training], WR.[Total LOA], WR.[Total Online], WR.[Other Abs], "
strSQL = strSQL & "CC.Department, CC.Order, WR.ActHeads, WR.StartDate, WR.EndDate " & _
	"FROM ([Weekly Reporting Table - Current] WR INNER JOIN [Shift Table] ST "
strSQL = strSQL & "ON WR.[Shift (D/N)] = ST.[Shift D/N]) INNER JOIN " & _
	"[Cost Center Roll-up] CC ON WR.[Cost Center] = CC.[Cost Center]"

[red]'add your where clause[/red]
strSQL = strSQL & " WHERE " & strWhere

[red]'print the sql to the debug window[/red]
Debug.print strSQL

[red]'update the querydef[/red]
CurrentDb.QueryDefs("F - Query for Reports").SQL = strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pbrown77]

Not sure how, but after pasteing and using your advice and code above the criteria is working. I will dig later.

Thanks!