If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Passing Filter to SubReport
I have a report with two subreports, nothing to link parent to child, but
they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#2
|
|||
|
|||
Passing Filter to SubReport
Changing the filter or row source of a subreport at run-time is difficult.
Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#3
|
|||
|
|||
Passing Filter to SubReport
Duane, I have tried both Marsh's code and have tried to include the code in
the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#4
|
|||
|
|||
Passing Filter to SubReport
I believe the subreports are rendered first. I don't understand what you mean
by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#5
|
|||
|
|||
Passing Filter to SubReport
All three data sources for my reoprt comes from queries with the same fields
accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#6
|
|||
|
|||
Passing Filter to SubReport
"form comes up and ask for up to five different fields to sort on" by this do
you mean "filter on" rather than "sort on"? "Sort" generally refers to the order of the records in the report. Also, I never use a solution that opens a form from code in the report. I always start with the form open and criteria entered prior to opening the report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: All three data sources for my reoprt comes from queries with the same fields accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#7
|
|||
|
|||
Passing Filter to SubReport
You are correct on both accounts. Should of been filter rather than sort and
yes the form opens first, collects the filter information and then passes it to the report via an openreport code. It is this filter info passed to the main report that is not being passed along to the subreports? That is what I am trying to solve. "Duane Hookom" wrote: "form comes up and ask for up to five different fields to sort on" by this do you mean "filter on" rather than "sort on"? "Sort" generally refers to the order of the records in the report. Also, I never use a solution that opens a form from code in the report. I always start with the form open and criteria entered prior to opening the report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: All three data sources for my reoprt comes from queries with the same fields accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#8
|
|||
|
|||
Passing Filter to SubReport
I gave you the two option that I use in my first reply in this thread. Which
ones did you try implement and what were your results? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: You are correct on both accounts. Should of been filter rather than sort and yes the form opens first, collects the filter information and then passes it to the report via an openreport code. It is this filter info passed to the main report that is not being passed along to the subreports? That is what I am trying to solve. "Duane Hookom" wrote: "form comes up and ask for up to five different fields to sort on" by this do you mean "filter on" rather than "sort on"? "Sort" generally refers to the order of the records in the report. Also, I never use a solution that opens a form from code in the report. I always start with the form open and criteria entered prior to opening the report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: All three data sources for my reoprt comes from queries with the same fields accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#9
|
|||
|
|||
Passing Filter to SubReport
The SQL does not work because it did not reset the query to the original
state after running and I used the Forms!TempFilter!TFilter as the filter line works great when the report is open independently, it fails when the report is opened as a subreport "Duane Hookom" wrote: I gave you the two option that I use in my first reply in this thread. Which ones did you try implement and what were your results? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: You are correct on both accounts. Should of been filter rather than sort and yes the form opens first, collects the filter information and then passes it to the report via an openreport code. It is this filter info passed to the main report that is not being passed along to the subreports? That is what I am trying to solve. "Duane Hookom" wrote: "form comes up and ask for up to five different fields to sort on" by this do you mean "filter on" rather than "sort on"? "Sort" generally refers to the order of the records in the report. Also, I never use a solution that opens a form from code in the report. I always start with the form open and criteria entered prior to opening the report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: All three data sources for my reoprt comes from queries with the same fields accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
#10
|
|||
|
|||
Passing Filter to SubReport
I don't think either of my suggestions pertained at all to the filter
property of any report. Also, why are you concerned about maintaining the SQL of your saved query when you will dynamically change it every time the report with the subreport is run? If you need to persist the original query SQL, create a copy of the original query and use code like: CurrentDb.QueryDefs("qselRptA").SQL = CurrentDb.QueryDefs("qselRptAOriginal").SQL -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: The SQL does not work because it did not reset the query to the original state after running and I used the Forms!TempFilter!TFilter as the filter line works great when the report is open independently, it fails when the report is opened as a subreport "Duane Hookom" wrote: I gave you the two option that I use in my first reply in this thread. Which ones did you try implement and what were your results? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: You are correct on both accounts. Should of been filter rather than sort and yes the form opens first, collects the filter information and then passes it to the report via an openreport code. It is this filter info passed to the main report that is not being passed along to the subreports? That is what I am trying to solve. "Duane Hookom" wrote: "form comes up and ask for up to five different fields to sort on" by this do you mean "filter on" rather than "sort on"? "Sort" generally refers to the order of the records in the report. Also, I never use a solution that opens a form from code in the report. I always start with the form open and criteria entered prior to opening the report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: All three data sources for my reoprt comes from queries with the same fields accept one. That field is working, done, or pending jobs. The main report shows alot of data over several rows. The two subreports are for pending and completed projects. They list just the basics. When the report is run a form comes up and ask for up to five different fields to sort on (supervisor, worker, team, customer, company). All three of the reports have this data, but the combination of the filter requested will change everytime the report is run. "Duane Hookom" wrote: I believe the subreports are rendered first. I don't understand what you mean by "bombs". What is the record source of your subreport? -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: Duane, I have tried both Marsh's code and have tried to include the code in the subreport record source as you suggest. However, it appears that when a report is included as a subreport the timing of when the filter is set becomes confusing. Putting the filter on form then referencing it works great when the subreport is opened by itself, yet bombs when it is used as a subreport. What is the sequence of opening a report and subreport? DDBeards "Duane Hookom" wrote: Changing the filter or row source of a subreport at run-time is difficult. Marsh Barton has offered some suggestions in the past. My normal suggestions are to either set a criteria in the subreport record source like: Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd or change the SQL property of a saved query used in the Row Source property of the subreport prior to opening the main report. -- Duane Hookom Microsoft Access MVP "DDBeards" wrote: I have a report with two subreports, nothing to link parent to child, but they do have the same fields to be filtered on. The user selects the values from a form that are passed to the main form on open. However, I can not set the filter on the subreports at open of the main form or of the subreport. please help! DDBeards |
Thread Tools | |
Display Modes | |
|
|