Adam,
I do a lot of changing of the SQL property of saved queries. For instance, assuming your main report has a record source of qselMyProjects. This assumes the subreports are based on totals queries with a source of qselMyProjects.
I would add some vba code to frmNonITReports like:
' add this code to the sub that opens the report
Dim strSQL as String
Dim strWhere as String
Dim strQueryName as String
Dim strReportName as String
strQueryName = "qselMyProjects"
strReportName = "rptMyProjects"
strWhere = " WHERE 1=1 " 'starting point with no other purpose
If Not IsNull(me.DateBeg) Then
strWhere = strWhere & " AND [StartDate] >=#" & Me.DataBeg & "# "
End If
If Not IsNull(me.DateEnd) Then
strWhere = strWhere & " AND [StartDate] <=#" & Me.DateEnd & "# "
End If
strSQL = "SELECT ... FROM [tbl_Project Tracking] JOIN ... " & strWhere
debug.Print strSQL
Currentdb.QueryDefs(strQueryName).SQL = strSQL
DoCmd.OpenReport strReportName, acViewPreview
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Jun 2015 09:40:58 -0700
Subject: [MS_AccessPros] What's the best way to filter a sub form?
I have a project tracking database. A report lists all reports for a specified time frame. That time frame criteries is in the source query: WHERE ((([tbl_Project Tracking].[START DATE]) Between [Forms]![frmNonITReports].[DateBeg] And [Forms]![frmNonITReports].[DateEnd]))
There are two summary subreports at the end - one showing count by project type, the other by project person. They use the same source query as the main report, just grouped differently and showing only the summaries.
I need to report by selected project types. That's easy on the main report - I add "[PojectType] = 8" to the Where condition of DoCmd.OpenReport if a checkbox is checked on the form requesting the report. I'm not sure how to get the summary sub reports to use the same filter. Do I use an OnLoad event for the main form, the subform, do I add the filter to the main or sub via the VBA code that opens the report?
Adam
I do a lot of changing of the SQL property of saved queries. For instance, assuming your main report has a record source of qselMyProjects. This assumes the subreports are based on totals queries with a source of qselMyProjects.
I would add some vba code to frmNonITReports like:
' add this code to the sub that opens the report
Dim strSQL as String
Dim strWhere as String
Dim strQueryName as String
Dim strReportName as String
strQueryName = "qselMyProjects"
strReportName = "rptMyProjects"
strWhere = " WHERE 1=1 " 'starting point with no other purpose
If Not IsNull(me.DateBeg) Then
strWhere = strWhere & " AND [StartDate] >=#" & Me.DataBeg & "# "
End If
If Not IsNull(me.DateEnd) Then
strWhere = strWhere & " AND [StartDate] <=#" & Me.DateEnd & "# "
End If
strSQL = "SELECT ... FROM [tbl_Project Tracking] JOIN ... " & strWhere
debug.Print strSQL
Currentdb.QueryDefs(strQueryName).SQL = strSQL
DoCmd.OpenReport strReportName, acViewPreview
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 1 Jun 2015 09:40:58 -0700
Subject: [MS_AccessPros] What's the best way to filter a sub form?
I have a project tracking database. A report lists all reports for a specified time frame. That time frame criteries is in the source query: WHERE ((([tbl_Project Tracking].[START DATE]) Between [Forms]![frmNonITReports].[DateBeg] And [Forms]![frmNonITReports].[DateEnd]))
There are two summary subreports at the end - one showing count by project type, the other by project person. They use the same source query as the main report, just grouped differently and showing only the summaries.
I need to report by selected project types. That's easy on the main report - I add "[PojectType] = 8" to the Where condition of DoCmd.OpenReport if a checkbox is checked on the form requesting the report. I'm not sure how to get the summary sub reports to use the same filter. Do I use an OnLoad event for the main form, the subform, do I add the filter to the main or sub via the VBA code that opens the report?
Adam
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar