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 |
#11
|
|||
|
|||
table/Query for the subreport
Duane, I still can not grab all the idea, so what would be the query for the subreport. I think it is no longer like in your email before.
I appreciate your help. Frank Duane Hookom wrote: I expect you want to have 5 text boxes in your main report to provide a 20-Nov-09 I expect you want to have 5 text boxes in your main report to provide a value to link to the year in the subreport. These 5 text boxes could Name: txt0 Control Source: =Forms!frmTahundipilih!cboEndYear Name: txt1 Control Source: =Forms!frmTahundipilih!cboEndYear -1 Name: txt2 Control Source: =Forms!frmTahundipilih!cboEndYear - 2 Name: txt3 Control Source: =Forms!frmTahundipilih!cboEndYear - 3 Name: txt4 Control Source: =Forms!frmTahundipilih!cboEndYear - 4 -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Free Icons / Graphics For Business Applications http://www.eggheadcafe.com/tutorials...phics-for.aspx |
#12
|
|||
|
|||
table/Query for the subreport
Create a subreport based on a query like:
SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat You would have the link master/child properties of each subreport copy reference the year and position fields. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane, I still can not grab all the idea, so what would be the query for the subreport. I think it is no longer like in your email before. I appreciate your help. Frank Duane Hookom wrote: I expect you want to have 5 text boxes in your main report to provide a 20-Nov-09 I expect you want to have 5 text boxes in your main report to provide a value to link to the year in the subreport. These 5 text boxes could Name: txt0 Control Source: =Forms!frmTahundipilih!cboEndYear Name: txt1 Control Source: =Forms!frmTahundipilih!cboEndYear -1 Name: txt2 Control Source: =Forms!frmTahundipilih!cboEndYear - 2 Name: txt3 Control Source: =Forms!frmTahundipilih!cboEndYear - 3 Name: txt4 Control Source: =Forms!frmTahundipilih!cboEndYear - 4 -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Free Icons / Graphics For Business Applications http://www.eggheadcafe.com/tutorials...phics-for.aspx . |
#13
|
|||
|
|||
It is taft
Duane, I have tried it but the result is not like waht we expected.
This is my Query in the MainRepot: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, Forms!frmTahundipilih!cboEndYear-0 AS Year0, Forms!frmTahundipilih!cboEndYear-1 AS Year1, Forms!frmTahundipilih!cboEndYear-2 AS Year2, Forms!frmTahundipilih!cboEndYear-3 AS Year3, Forms!frmTahundipilih!cboEndYear-4 AS Year4 FROM PelayanJemaat GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan; And this is my Query in the Subreport1: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-0 AS [Year] FROM PelayanJemaat; Subreport2: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-1 AS [Year] FROM PelayanJemaat; and so forth to subreport5 Link childfields: BidangPelayanan;Year Link Master Fields: BidangPelayanan;Year0 and so forth.... What have I done wrong...should we have the NamaPel( name of officers) also in the Main Query/Report? When we say link should we linked with the field of the query or the name of the field in the report. Could you help my by looking to your linked file, like you helped me before? Thanks in advence Frank Duane Hookom wrote: Create a subreport based on a query like:SELECT PelayanJemaat. 23-Nov-09 Create a subreport based on a query like: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat You would have the link master/child properties of each subreport copy reference the year and position fields. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Encryption On Mobile Devices in C# / .NET http://www.eggheadcafe.com/tutorials...bile-devi.aspx |
#14
|
|||
|
|||
It is taft
As I stated, there is only one subreport. I'm not sure where you got the
Year[X] columns in your main report record source. What's wrong with the main report record source as I suggested in my reply on 11/18 and the subreport record source from 11/23? -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane, I have tried it but the result is not like waht we expected. This is my Query in the MainRepot: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, Forms!frmTahundipilih!cboEndYear-0 AS Year0, Forms!frmTahundipilih!cboEndYear-1 AS Year1, Forms!frmTahundipilih!cboEndYear-2 AS Year2, Forms!frmTahundipilih!cboEndYear-3 AS Year3, Forms!frmTahundipilih!cboEndYear-4 AS Year4 FROM PelayanJemaat GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan; And this is my Query in the Subreport1: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-0 AS [Year] FROM PelayanJemaat; Subreport2: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel, Forms!frmTahundipilih!cboEndYear-1 AS [Year] FROM PelayanJemaat; and so forth to subreport5 Link childfields: BidangPelayanan;Year Link Master Fields: BidangPelayanan;Year0 and so forth.... What have I done wrong...should we have the NamaPel( name of officers) also in the Main Query/Report? When we say link should we linked with the field of the query or the name of the field in the report. Could you help my by looking to your linked file, like you helped me before? Thanks in advence Frank Duane Hookom wrote: Create a subreport based on a query like:SELECT PelayanJemaat. 23-Nov-09 Create a subreport based on a query like: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat You would have the link master/child properties of each subreport copy reference the year and position fields. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Encryption On Mobile Devices in C# / .NET http://www.eggheadcafe.com/tutorials...bile-devi.aspx . |
#15
|
|||
|
|||
How can it go sideway year by Year ( TahunPel/Year Servie).
Duane in my understanding report will take value from query and query will take value from table. So if we do not have a calculated field in query how can we link it.
I also refer to your previous email Nov 13 saying 5 set of subreport. OK I will try again to make it only one subreport. But if you still have a rather easy one I appreciate. The problem because we have to choose in combo box the year of service, then it will show, for the last 5 years in the report. Thanks very much, Frank Duane Hookom wrote: As I stated, there is only one subreport. 24-Nov-09 As I stated, there is only one subreport. I am not sure where you got the Year[X] columns in your main report record source. What's wrong with the main report record source as I suggested in my reply on 11/18 and the subreport record source from 11/23? -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Look Ma, No DataBase! Bamboo Prevalence http://www.eggheadcafe.com/tutorials...abase-bam.aspx |
#16
|
|||
|
|||
How can it go sideway year by Year ( TahunPel/Year Servie).
My Nov 13 message was prior to you providing some information. My 11/13
message stated "I would try use 5 copies of the same subreport side-by-side in the detail section of a main report". That clearly states the same subreport copied 5 times in main report. The Year field must be in the subreport's record source. This year is used as part of the Link Child. To get the year part for the Link Master property, you put 5 text boxes in the main report. If you can't figure out how to do this based on the control on your form, just set the control sources of the 5 text boxes to: =2009 =2008 =2007 =2006 =2005 Then once it works, read back through my posts to find out how to change these to dynamically calculate based on the value from the control on the form. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane in my understanding report will take value from query and query will take value from table. So if we do not have a calculated field in query how can we link it. I also refer to your previous email Nov 13 saying 5 set of subreport. OK I will try again to make it only one subreport. But if you still have a rather easy one I appreciate. The problem because we have to choose in combo box the year of service, then it will show, for the last 5 years in the report. Thanks very much, Frank Duane Hookom wrote: As I stated, there is only one subreport. 24-Nov-09 As I stated, there is only one subreport. I am not sure where you got the Year[X] columns in your main report record source. What's wrong with the main report record source as I suggested in my reply on 11/18 and the subreport record source from 11/23? -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Look Ma, No DataBase! Bamboo Prevalence http://www.eggheadcafe.com/tutorials...abase-bam.aspx . |
#17
|
|||
|
|||
Can not link with the unbound form
Duane,
I have followed your suggestion using: =Forms!frmRptSlct!cboYear =Forms!frmRptSlct!cboYear-1 =Forms!frmRptSlct!cboYear-2 =Forms!frmRptSlct!cboYear-3 =Forms!frmRptSlct!cboYear-4 both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report. This is my mainreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); This is my subreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); Is it because both queries come from one table? In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem? Thanks for your help. Frank Duane Hookom wrote: My Nov 13 message was prior to you providing some information. 25-Nov-09 My Nov 13 message was prior to you providing some information. My 11/13 message stated "I would try use 5 copies of the same subreport side-by-side in the detail section of a main report". That clearly states the same subreport copied 5 times in main report. The Year field must be in the subreport's record source. This year is used as part of the Link Child. To get the year part for the Link Master property, you put 5 text boxes in the main report. If you cannot figure out how to do this based on the control on your form, just set the control sources of the 5 text boxes to: =2009 =2008 =2007 =2006 =2005 Then once it works, read back through my posts to find out how to change these to dynamically calculate based on the value from the control on the form. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice XAML "Windows Send Error Report" http://www.eggheadcafe.com/tutorials...end-error.aspx |
#18
|
|||
|
|||
Can not link with the unbound form
The subreport should not have any text boxes with Control Sources like
=Forms!frm... You were suppose to substitute your actual form and control name which would be [Forms]![frmTahundipilih]![cboEndYear] Both of your report querys have a criteria that limits them to a single year. Try a main report query of my suggestion earlier. There should be no year or name fields in this query. SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan FROM PelayanJemaat GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan The subreport query should be: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND Forms!frmTahundipilih!cboEndYear; You should have the 5 text boxes that would display the 5 years across the top of the detail section in the main report. Below each of these should be a copy of the subreport. You use the link master/child properties to filter each subreport to the appropriate fields. These fields will include the text boxes displaying the years as part of the Link Master. The link child field that links with the text boxes is the TahunPel which I assume is a field storing the year like 2009, 2008, 2007,... -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane, I have followed your suggestion using: =Forms!frmRptSlct!cboYear =Forms!frmRptSlct!cboYear-1 =Forms!frmRptSlct!cboYear-2 =Forms!frmRptSlct!cboYear-3 =Forms!frmRptSlct!cboYear-4 both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report. This is my mainreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); This is my subreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); Is it because both queries come from one table? In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem? Thanks for your help. Frank Duane Hookom wrote: My Nov 13 message was prior to you providing some information. 25-Nov-09 My Nov 13 message was prior to you providing some information. My 11/13 message stated "I would try use 5 copies of the same subreport side-by-side in the detail section of a main report". That clearly states the same subreport copied 5 times in main report. The Year field must be in the subreport's record source. This year is used as part of the Link Child. To get the year part for the Link Master property, you put 5 text boxes in the main report. If you cannot figure out how to do this based on the control on your form, just set the control sources of the 5 text boxes to: =2009 =2008 =2007 =2006 =2005 Then once it works, read back through my posts to find out how to change these to dynamically calculate based on the value from the control on the form. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice XAML "Windows Send Error Report" http://www.eggheadcafe.com/tutorials...end-error.aspx . |
#19
|
|||
|
|||
Can not link with the unbound form
The subreport should not have any text boxes with Control Sources like
=Forms!frm... You were suppose to substitute your actual form and control name which would be [Forms]![frmTahundipilih]![cboEndYear] Both of your report querys have a criteria that limits them to a single year. Try a main report query of my suggestion earlier. There should be no year or name fields in this query. SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan FROM PelayanJemaat GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan The subreport query should be: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND Forms!frmTahundipilih!cboEndYear; You should have the 5 text boxes that would display the 5 years across the top of the detail section in the main report. Below each of these should be a copy of the subreport. You use the link master/child properties to filter each subreport to the appropriate fields. These fields will include the text boxes displaying the years as part of the Link Master. The link child field that links with the text boxes is the TahunPel which I assume is a field storing the year like 2009, 2008, 2007,... -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane, I have followed your suggestion using: =Forms!frmRptSlct!cboYear =Forms!frmRptSlct!cboYear-1 =Forms!frmRptSlct!cboYear-2 =Forms!frmRptSlct!cboYear-3 =Forms!frmRptSlct!cboYear-4 both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report. This is my mainreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); This is my subreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); Is it because both queries come from one table? In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem? Thanks for your help. Frank Duane Hookom wrote: My Nov 13 message was prior to you providing some information. 25-Nov-09 My Nov 13 message was prior to you providing some information. My 11/13 message stated "I would try use 5 copies of the same subreport side-by-side in the detail section of a main report". That clearly states the same subreport copied 5 times in main report. The Year field must be in the subreport's record source. This year is used as part of the Link Child. To get the year part for the Link Master property, you put 5 text boxes in the main report. If you cannot figure out how to do this based on the control on your form, just set the control sources of the 5 text boxes to: =2009 =2008 =2007 =2006 =2005 Then once it works, read back through my posts to find out how to change these to dynamically calculate based on the value from the control on the form. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice XAML "Windows Send Error Report" http://www.eggheadcafe.com/tutorials...end-error.aspx . |
#20
|
|||
|
|||
Can not link with the unbound form
The subreport should not have any text boxes with Control Sources like
=Forms!frm... You were suppose to substitute your actual form and control name which would be [Forms]![frmTahundipilih]![cboEndYear] Both of your report querys have a criteria that limits them to a single year. Try a main report query of my suggestion earlier. There should be no year or name fields in this query. SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan FROM PelayanJemaat GROUP BY PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan The subreport query should be: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, NamaPel, TahunPel FROM PelayanJemaat WHERE [TahunPel] Between Forms!frmTahundipilih!cboEndYear- 4 AND Forms!frmTahundipilih!cboEndYear; You should have the 5 text boxes that would display the 5 years across the top of the detail section in the main report. Below each of these should be a copy of the subreport. You use the link master/child properties to filter each subreport to the appropriate fields. These fields will include the text boxes displaying the years as part of the Link Master. The link child field that links with the text boxes is the TahunPel which I assume is a field storing the year like 2009, 2008, 2007,... -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Duane, I have followed your suggestion using: =Forms!frmRptSlct!cboYear =Forms!frmRptSlct!cboYear-1 =Forms!frmRptSlct!cboYear-2 =Forms!frmRptSlct!cboYear-3 =Forms!frmRptSlct!cboYear-4 both in the subreport and main report, but when I created the link master & child field, should be the field of the query, the error message says " Can not create the link with the unbound form/report. This is my mainreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); This is my subreport query: SELECT PelayanJemaat.Nurut, PelayanJemaat.BidangPelayanan, PelayanJemaat.ID_pel, PelayanJemaat.NamaPel, PelayanJemaat.TahunPel FROM PelayanJemaat WHERE (((PelayanJemaat.TahunPel)=[Forms]![frmTahundipilih]![cboEndYear])); Is it because both queries come from one table? In other reports of my churchdatabase, the link can work when the field that I link is from difference table. Could it the onetable source be the cause of the problem? Thanks for your help. Frank Duane Hookom wrote: My Nov 13 message was prior to you providing some information. 25-Nov-09 My Nov 13 message was prior to you providing some information. My 11/13 message stated "I would try use 5 copies of the same subreport side-by-side in the detail section of a main report". That clearly states the same subreport copied 5 times in main report. The Year field must be in the subreport's record source. This year is used as part of the Link Child. To get the year part for the Link Master property, you put 5 text boxes in the main report. If you cannot figure out how to do this based on the control on your form, just set the control sources of the 5 text boxes to: =2009 =2008 =2007 =2006 =2005 Then once it works, read back through my posts to find out how to change these to dynamically calculate based on the value from the control on the form. -- Duane Hookom Microsoft Access MVP "Frank Situmorang" wrote: Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice XAML "Windows Send Error Report" http://www.eggheadcafe.com/tutorials...end-error.aspx . |
Thread Tools | |
Display Modes | |
|
|