I have been successful in using a report when running data through a simple report and having the code apply a filter...
The challenge I am now having is that I have a much more complex report, with sub-reports and all, where the filter approach does not work.
So, what I am trying to do now is apply the filter to the make table query - from there the report then uses the temp table and the problem of filtering via the report goes away...
What I cannot figure out how to do is apply the filter to the make table query...from there it will be easy to loop through the report create/distribution steps...
Any help you can provide would be greatly appreciated.
Public strInvoiceWhere As String
Function AJSendStaffingReport()
Dim headcounts As DAO.Database
'set table containing data source?
Dim Staffing_Report_ALL_NEW_BREAKS As DAO.Recordset
Set headcounts = Currentdb()
Set Staffing_Report_ALL_NEW_BREAKS = headcounts.OpenRecordset("Staffing_Report_ALL_NEW_BREAKS", _
dbOpenDynaset)
'If MsgBox("Do you want to create report" & Chr(13) & _ <put prompt back in later
' "to all receiptents using Microsoft Outlook?", 4) = 6 Then
With Staffing_Report_ALL_NEW_BREAKS
Do Until .EOF
'Create the report Filter <-how to replace in query
'used by the Report_Open event. <-how to replace report_open to define variable
strInvoiceWhere = "[hierarchy_code] = '" & ![hierarchy_code] & "'"
DoCmd.SetWarnings False
DoCmd.OpenQuery "Staffing_Report_ALL_NEW"
DoCmd.SetWarnings True
' create report as pdf with unique filename
'SendKeys "c:\z\Staffing_Report_" & ![hierarchy_code] & "_" & ![rvp_avp_last_name] & "_" & Format(Now(), "YYYYMMDD") & "~^sy", False
'DoCmd.OpenReport "_Staffing Report - COMBINED - Consolidated", acViewNormal, "", "", acHidden
' then attach both files and send
' DoCmd.SendObject acReport, "_Staffing Report - COMBINED - Consolidated", acFormatRTF, _
' ![test], , , "Weekly Open Requisition Report - " & Format(Now(), "mm/dd/yy"), ![hierarchy_name] & " THIS IS A TEST open requisition report attached - ARE YOU HAPPY???", False
.MoveNext
Loop
End With
' End If < remove comment later when prompt is put back in
Staffing_Report_ALL_NEW_BREAKS.Close ' close break list
'DONE with hierarchy pass
The challenge I am now having is that I have a much more complex report, with sub-reports and all, where the filter approach does not work.
So, what I am trying to do now is apply the filter to the make table query - from there the report then uses the temp table and the problem of filtering via the report goes away...
What I cannot figure out how to do is apply the filter to the make table query...from there it will be easy to loop through the report create/distribution steps...
Any help you can provide would be greatly appreciated.
Public strInvoiceWhere As String
Function AJSendStaffingReport()
Dim headcounts As DAO.Database
'set table containing data source?
Dim Staffing_Report_ALL_NEW_BREAKS As DAO.Recordset
Set headcounts = Currentdb()
Set Staffing_Report_ALL_NEW_BREAKS = headcounts.OpenRecordset("Staffing_Report_ALL_NEW_BREAKS", _
dbOpenDynaset)
'If MsgBox("Do you want to create report" & Chr(13) & _ <put prompt back in later
' "to all receiptents using Microsoft Outlook?", 4) = 6 Then
With Staffing_Report_ALL_NEW_BREAKS
Do Until .EOF
'Create the report Filter <-how to replace in query
'used by the Report_Open event. <-how to replace report_open to define variable
strInvoiceWhere = "[hierarchy_code] = '" & ![hierarchy_code] & "'"
DoCmd.SetWarnings False
DoCmd.OpenQuery "Staffing_Report_ALL_NEW"
DoCmd.SetWarnings True
' create report as pdf with unique filename
'SendKeys "c:\z\Staffing_Report_" & ![hierarchy_code] & "_" & ![rvp_avp_last_name] & "_" & Format(Now(), "YYYYMMDD") & "~^sy", False
'DoCmd.OpenReport "_Staffing Report - COMBINED - Consolidated", acViewNormal, "", "", acHidden
' then attach both files and send
' DoCmd.SendObject acReport, "_Staffing Report - COMBINED - Consolidated", acFormatRTF, _
' ![test], , , "Weekly Open Requisition Report - " & Format(Now(), "mm/dd/yy"), ![hierarchy_name] & " THIS IS A TEST open requisition report attached - ARE YOU HAPPY???", False
.MoveNext
Loop
End With
' End If < remove comment later when prompt is put back in
Staffing_Report_ALL_NEW_BREAKS.Close ' close break list
'DONE with hierarchy pass
