need to pass where criteria from DoCmd to subreports, how?

[diddydustin]

Hey guys,

I have a main report "EMR_All_Report" that has three subreports-- EMR_Time_Report, EMR_UPSH, and EMR_Sheet_Report. I have a form that allows the end-user to select a date range, machine, or shift (for example) and then open the form based on that crieteria. Previously this worked fine because the reports would not subreports but opened directly. Now that I am using one main report with the reports as subreports within it, I cannot figure out how to open this report through DoCmd and have it display the correct results.


Here's the code I use to open the report then close the form:

DoCmd.OpenReport "EMR_All_Report", acViewPreview, , strWhere, , acWindowNormal
DoCmd.Close acForm, "Report Generator"


Any help would be appreciated! Thanks

diddydustin

 

[dhookom]

You will not be able to get this to work. I place the form control references in the criteria of the record sources of the subreports.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Duane,

I am using my form to build the WHERE statement, as the end-user may enter some data, such as date or machine, and not enter shift or time code. Here's what I was doing:

Dim strWhere As String
strWhere = "1 = 1 " 'now I can use AND
If Not IsNull(Me.repMachine) Then
strWhere = strWhere & " AND MACHINE = " & Me!repMachine
End If
If Not IsNull(Me.repShift) Then
strWhere = strWhere & " AND SHIFT = '" & Me!repShift & "'"
End If
If Not (IsNull(Me.begDate) And IsNull(Me.endDate)) Then
strWhere = strWhere & " AND DATE > #" & Me!begDate & "# " & _
" AND DATE < #" & Me!endDate & "# "
End If
If (Not IsNull(Me.begDate)) And IsNull(Me.endDate) Then
strWhere = strWhere & " AND DATE = #" & Me!begDate & "#"
End If
If Not IsNull(Me.repCode) Then
strWhere = strWhere & " AND Time_Code = " & Me!repCode
End If

If repReport = "All" Then
DoCmd.OpenReport "EMR_All_Report", acViewPreview, , strWhere, , acWindowNormal
DoCmd.Close acForm, "Report Generator"
Else
If repReport = "Sheet Report" Then
DoCmd.OpenReport "EMR_Sheets_Report", acViewPreview, , strWhere, , acWindowNormal
DoCmd.Close acForm, "Report Generator"
Else
If repReport = "UPSH Report" Then
DoCmd.OpenReport "EMR_UPSH_Report", acViewPreview, , strWhere, , acWindowNormal
DoCmd.Close acForm, "Report Generator"
Else
If repReport = "Time Report" Then
DoCmd.OpenReport "EMR_Time_Report", acViewPreview, , strWhere, , acWindowNormal
DoCmd.Close acForm, "Report Generator"
End If
End If
End If
End If


I am using three subreports, so how can I use this method to determine what the end-user selected in the record sources of the subreports? Should I have a hidden textbox that holds the WHERE statement and then have the subreports record source point to that?

diddydustin

 

[dhookom]

You can either change the SQL property of the saved querys that are the subreport record sources or you can reference the form controls in the criteria of the record sources.
Where [Shift] & "" = Nz(Forms!frmA!repShift,[Shift]) & "" AND ....=...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

I am trying this in the record source for one of the subreports:

"SELECT Sum(EMR_SheetsUnion.TotalSheets) AS SumOfTotalSheets, Sum(EMR_TimeUnion.[Sum Of Time_Total])/3600 AS [SumOfSum Of Time_Total], EMR_TimeUnion.Machine, EMR_TimeUnion.Shift, EMR_TimeUnion.Date
FROM EMR_SheetsUnion, EMR_TimeUnion
GROUP BY EMR_TimeUnion.Machine, EMR_TimeUnion.Shift, EMR_TimeUnion.Date WHERE EMR_TimeUnion.Shift = '" & Forms!ReportGenerator!repShift & "'"


However, it's telling me it doesn't recognize Forms!ReportGenerator!repShift. How exactly am I supposed to change the record source so that it understands Forms!ReportGenerator!repShift is coming from a form and not a field named 'Forms!ReportGenerator!repShift'? How can I even change the SQL in the underlining queries to do this? I am a bit confused.

Thanks
Dustin

 

[dhookom]

To change the SQL property of a saved query:
strSQL = "SELECT ... WHERE ID=" & Me.txtID
CurrentDb.QueryDefs("qselSubOne").SQL = strSQL
You must do this prior to opening the report.

BTW: does either EMR_SheetsUnion or EMR_TimeUnion return just one record or are you trying to create a cartesian query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

No, the queries return more than one recrod.

What is qselSubOne? The name of the query?

Dustin

 

[diddydustin]

Okay, I got it to work on two reports. However, this code returns an error:

strSQL = "SELECT Sum(EMR_SheetsUnion.TotalSheets) AS SumOfTotalSheets, " & _
"Sum(EMR_TimeUnion.[Sum Of Time_Total])/3600 AS [SumOfSum Of Time_Total], " & _
"EMR_TimeUnion.Machine, EMR_TimeUnion.Shift, EMR_TimeUnion.Date " & _
"FROM EMR_SheetsUnion, EMR_TimeUnion " & _
strWhere + " GROUP BY EMR_TimeUnion.Machine, EMR_TimeUnion.Shift, EMR_TimeUnion.Date;"

CurrentDb.QueryDefs("EMR_UPSH").SQL = strSQL

DoCmd.OpenReport "EMR_UPSH_Report", acViewPreview, , , , acWindowNormal
DoCmd.Close acForm, "ReportGenerator"


The error I'm getting is:

Run-time error '2580'

The record source "SELECT Sum(EMR_SheetsUnion.TotalSheets) AS SumOfTotalSheets, Sum(EMR_TimUnion.[Sum Of Time_Total])/3600 AS [SumofSum of Ti...' specified on this form or report does not exist.

Why do the other two work fine and this does not? I am doing the other subreports in the same manner.

Dustin

 

[dhookom]

Open the query "EMR_UPSH" in design view to find the issue and then report back.

Do you understand why I asked about returning more than one record? Have you check the results of your strSQL?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Okay great-- I got everything to work smoothly except for one thing. My EMR_Time_Report does not return the results back in order by date. Here is the code to set the SQL:

' Set the time report
strSQL = "SELECT EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code, EMR_TimeCodeList.Desc, EMR_Base.Machine, EMR_Base.Date, EMR_Base.Shift" & _
" FROM (EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code " + strWhere & _
" AND (((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].

)) GROUP BY EMR_Base.Date, EMR_Base.Shift, EMR_Base.Machine, EMR_TotalGraphicTime.[Sum Of Time_Total], " & _
    "EMR_TotalGraphicTime.Time_Code, EMR_TimeCodeList.Desc;"
    
    CurrentDb.QueryDefs("EMR_TimeUnion").SQL = strSQL

The query returns the results fine, but when the report is displayed the results are not in order by date.

Here is the actual query after the above code runs:

SELECT EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code, EMR_TimeCodeList.Desc, EMR_Base.Machine, EMR_Base.Date, EMR_Base.Shift
FROM (EMR_Base INNER JOIN EMR_TotalGraphicTime ON EMR_Base.Time_ID = EMR_TotalGraphicTime.Time_ID) INNER JOIN EMR_TimeCodeList ON EMR_TotalGraphicTime.Time_Code = EMR_TimeCodeList.Code
WHERE 1 = 1  AND SHIFT = '2' AND (((EMR_TotalGraphicTime.Time_Code)=[EMR_TimeCodeList].[Code]))
GROUP BY EMR_Base.Date, EMR_Base.Shift, EMR_Base.Machine, EMR_TotalGraphicTime.[Sum Of Time_Total], EMR_TotalGraphicTime.Time_Code, EMR_TimeCodeList.Desc;

What could be the problem? Can I include the other values (EMR_Base.Shift, EMR_Base.Machine...) in the query but not in the GROUP BY?

Thanks
Dustin

 

[dhookom]

Never rely on the sort order in the query to be carried over to the report. Use the sorting and grouping dialog in the report to set its sorting.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.