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
|
|||
|
|||
Report prints data twice on two different pages
Can we assume you actually are referencing subreports rather than subforms?
Is the main report bound only to the Part Certification table and no table that is detailed in the subreport record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 subreport giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#12
|
|||
|
|||
Report prints data twice on two different pages
Yes the setup below is for the main input form "Part Certification" with the
two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#13
|
|||
|
|||
Report prints data twice on two different pages
I would not print subforms. Take the time to save the subforms as reports and
then use the subreports. Maybe you could provide the actual SQL of the main and subreports. What sections are the subreports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#14
|
|||
|
|||
Report prints data twice on two different pages
The subs are in the detail section of the report.
I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#15
|
|||
|
|||
Report prints data twice on two different pages
Again, I would convert the forms to reports and get rid of the code in the
reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#16
|
|||
|
|||
Report prints data twice on two different pages
Duane,
I hope this is what your talking about. Each time I have tried to change things in the Select statement it makes one or both of the subs not show data or the whole sub does not show up. I will begin to make sub-reports to replace the sub-forms now but what record source do I use? the sub-form or the main form? SQL record source from Part Certification Report: SELECT [PartCertification].[PartCertificationID] AS PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS PartCertSub_PartCertificationID, [PartCertSub].[ProductID], [PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID, [PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts], [CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID], [CertOrderSub].[OrderID] AS CertOrderSub_OrderID, [CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS PartCertification_PartCertSub, [PartCertification].[OrderID] AS PartCertification_OrderID, [PartCertification].[CertmaterialSubID], [PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID, [PartCertification].[PartCertDate], [PartCertification].[Number Certified], [PartCertification].[Part Number], [PartCertification].[Part Cert Entered by], [PartCertification].[Part Cert Notes], [PartCertification].[Material], [PartCertification].[Specification], CertMaterailSub.PourScheduleID, PourSchedule.[Master Heat Number], PourSchedule.HeatNo, PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products], PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER JOIN PartCertSub ON PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER JOIN CertMaterailSub ON PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON Products.ProductID=PartCertSub.ProductID; Record Source from Cert Order Sub: SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID; Record source for Part Cert Sub is PartCertSub appears to be no SQL but within that sub I am selecting the part number from the ProductID field: SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number]; Thank you for your help on this. Mark Pierce "Duane Hookom" wrote: Again, I would convert the forms to reports and get rid of the code in the reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#17
|
|||
|
|||
Report prints data twice on two different pages
I'm not sure why you have the CertOrderSub table in your main report's record
source. Is this table needed in the main report? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Duane, I hope this is what your talking about. Each time I have tried to change things in the Select statement it makes one or both of the subs not show data or the whole sub does not show up. I will begin to make sub-reports to replace the sub-forms now but what record source do I use? the sub-form or the main form? SQL record source from Part Certification Report: SELECT [PartCertification].[PartCertificationID] AS PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS PartCertSub_PartCertificationID, [PartCertSub].[ProductID], [PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID, [PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts], [CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID], [CertOrderSub].[OrderID] AS CertOrderSub_OrderID, [CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS PartCertification_PartCertSub, [PartCertification].[OrderID] AS PartCertification_OrderID, [PartCertification].[CertmaterialSubID], [PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID, [PartCertification].[PartCertDate], [PartCertification].[Number Certified], [PartCertification].[Part Number], [PartCertification].[Part Cert Entered by], [PartCertification].[Part Cert Notes], [PartCertification].[Material], [PartCertification].[Specification], CertMaterailSub.PourScheduleID, PourSchedule.[Master Heat Number], PourSchedule.HeatNo, PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products], PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER JOIN PartCertSub ON PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER JOIN CertMaterailSub ON PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON Products.ProductID=PartCertSub.ProductID; Record Source from Cert Order Sub: SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID; Record source for Part Cert Sub is PartCertSub appears to be no SQL but within that sub I am selecting the part number from the ProductID field: SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number]; Thank you for your help on this. Mark Pierce "Duane Hookom" wrote: Again, I would convert the forms to reports and get rid of the code in the reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#18
|
|||
|
|||
Report prints data twice on two different pages
It is Nessa's unless you know of a different way to do it. The goal of the
final report is to produce a material cert that shows the parts that go with the cert number along with the purchase order issued for those parts. I have the sub reports made and have them in the main report now but the issue still remains. "Duane Hookom" wrote: I'm not sure why you have the CertOrderSub table in your main report's record source. Is this table needed in the main report? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Duane, I hope this is what your talking about. Each time I have tried to change things in the Select statement it makes one or both of the subs not show data or the whole sub does not show up. I will begin to make sub-reports to replace the sub-forms now but what record source do I use? the sub-form or the main form? SQL record source from Part Certification Report: SELECT [PartCertification].[PartCertificationID] AS PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS PartCertSub_PartCertificationID, [PartCertSub].[ProductID], [PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID, [PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts], [CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID], [CertOrderSub].[OrderID] AS CertOrderSub_OrderID, [CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS PartCertification_PartCertSub, [PartCertification].[OrderID] AS PartCertification_OrderID, [PartCertification].[CertmaterialSubID], [PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID, [PartCertification].[PartCertDate], [PartCertification].[Number Certified], [PartCertification].[Part Number], [PartCertification].[Part Cert Entered by], [PartCertification].[Part Cert Notes], [PartCertification].[Material], [PartCertification].[Specification], CertMaterailSub.PourScheduleID, PourSchedule.[Master Heat Number], PourSchedule.HeatNo, PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products], PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER JOIN PartCertSub ON PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER JOIN CertMaterailSub ON PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON Products.ProductID=PartCertSub.ProductID; Record Source from Cert Order Sub: SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID; Record source for Part Cert Sub is PartCertSub appears to be no SQL but within that sub I am selecting the part number from the ProductID field: SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number]; Thank you for your help on this. Mark Pierce "Duane Hookom" wrote: Again, I would convert the forms to reports and get rid of the code in the reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#19
|
|||
|
|||
Report prints data twice on two different pages
Sorry Duane I missed spelled the word Necessary on that last post, didn't
want to confuse you. "Duane Hookom" wrote: I'm not sure why you have the CertOrderSub table in your main report's record source. Is this table needed in the main report? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Duane, I hope this is what your talking about. Each time I have tried to change things in the Select statement it makes one or both of the subs not show data or the whole sub does not show up. I will begin to make sub-reports to replace the sub-forms now but what record source do I use? the sub-form or the main form? SQL record source from Part Certification Report: SELECT [PartCertification].[PartCertificationID] AS PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS PartCertSub_PartCertificationID, [PartCertSub].[ProductID], [PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID, [PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts], [CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID], [CertOrderSub].[OrderID] AS CertOrderSub_OrderID, [CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS PartCertification_PartCertSub, [PartCertification].[OrderID] AS PartCertification_OrderID, [PartCertification].[CertmaterialSubID], [PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID, [PartCertification].[PartCertDate], [PartCertification].[Number Certified], [PartCertification].[Part Number], [PartCertification].[Part Cert Entered by], [PartCertification].[Part Cert Notes], [PartCertification].[Material], [PartCertification].[Specification], CertMaterailSub.PourScheduleID, PourSchedule.[Master Heat Number], PourSchedule.HeatNo, PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products], PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER JOIN PartCertSub ON PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER JOIN CertMaterailSub ON PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON Products.ProductID=PartCertSub.ProductID; Record Source from Cert Order Sub: SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID; Record source for Part Cert Sub is PartCertSub appears to be no SQL but within that sub I am selecting the part number from the ProductID field: SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number]; Thank you for your help on this. Mark Pierce "Duane Hookom" wrote: Again, I would convert the forms to reports and get rid of the code in the reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I have a report that contains three sub reports everything is working correctly as far as the output except the one page report repeats its self on a second page with all the same info except the header. Is this a setting I have wrong or a format issue? Let me know if more info is needed for this issue. Thanks |
#20
|
|||
|
|||
Report prints data twice on two different pages
Your main report should not have a level of detail that is greater than the
level of the Link Master/Child properties unless the subreport is located in a group header or footer. For instance if you have a table of Employees and a table of employee's children, your link master/child would be the employeeID. The main report would NOT have employee children in its Record Source. I assume your CertOrderSub table is similar to employee children table and shouldn't be in the main report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: It is Nessa's unless you know of a different way to do it. The goal of the final report is to produce a material cert that shows the parts that go with the cert number along with the purchase order issued for those parts. I have the sub reports made and have them in the main report now but the issue still remains. "Duane Hookom" wrote: I'm not sure why you have the CertOrderSub table in your main report's record source. Is this table needed in the main report? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Duane, I hope this is what your talking about. Each time I have tried to change things in the Select statement it makes one or both of the subs not show data or the whole sub does not show up. I will begin to make sub-reports to replace the sub-forms now but what record source do I use? the sub-form or the main form? SQL record source from Part Certification Report: SELECT [PartCertification].[PartCertificationID] AS PartCertification_PartCertificationID, [PartCertSub].[PartCertSub] AS PartCertSub_PartCertSub, [PartCertSub].[PartCertificationID] AS PartCertSub_PartCertificationID, [PartCertSub].[ProductID], [PartCertSub].[PourScheduleID] AS PartCertSub_PourScheduleID, [PartCertSub].[Pour Wt], [PartCertSub].[NumberOfParts], [CertMaterailSub].[CertMaterialID], [CertMaterailSub].[PourScheduleID] AS CertMaterailSub_PourScheduleID, [CertMaterailSub].[PartCertificationID] AS CertMaterailSub_PartCertificationID, [CertOrderSub].[CertOrderSubID], [CertOrderSub].[OrderID] AS CertOrderSub_OrderID, [CertOrderSub].[OrderDetailID], [CertOrderSub].[PartCertificationID] AS CertOrderSub_PartCertificationID, [PartCertification].[PartCertSub] AS PartCertification_PartCertSub, [PartCertification].[OrderID] AS PartCertification_OrderID, [PartCertification].[CertmaterialSubID], [PartCertification].[PourScheduleID] AS PartCertification_PourScheduleID, [PartCertification].[PartCertDate], [PartCertification].[Number Certified], [PartCertification].[Part Number], [PartCertification].[Part Cert Entered by], [PartCertification].[Part Cert Notes], [PartCertification].[Material], [PartCertification].[Specification], CertMaterailSub.PourScheduleID, PourSchedule.[Master Heat Number], PourSchedule.HeatNo, PourSchedule.PourDate, Products.[Part Number] AS [Part Number_Products], PartCertification.PartCertificationID FROM Products INNER JOIN (PourSchedule INNER JOIN (((PartCertification INNER JOIN CertOrderSub ON PartCertification.PartCertificationID=CertOrderSub .PartCertificationID) INNER JOIN PartCertSub ON PartCertification.PartCertificationID=PartCertSub. PartCertificationID) INNER JOIN CertMaterailSub ON PartCertification.PartCertificationID=CertMaterail Sub.PartCertificationID) ON PourSchedule.PourScheduleID=CertMaterailSub.PourSc heduleID) ON Products.ProductID=PartCertSub.ProductID; Record Source from Cert Order Sub: SELECT CertOrderSub.*, Orders.CustomerID, Orders.OrderDate FROM Orders INNER JOIN CertOrderSub ON Orders.OrderID=CertOrderSub.OrderID; Record source for Part Cert Sub is PartCertSub appears to be no SQL but within that sub I am selecting the part number from the ProductID field: SELECT Products.[Part Number] FROM Products ORDER BY Products.[Part Number]; Thank you for your help on this. Mark Pierce "Duane Hookom" wrote: Again, I would convert the forms to reports and get rid of the code in the reports. I wasn't asking for code. I had expected a reply back with the SQL view of your reports' Record Sources. This would be something like: SELECT ... FROM... -- Duane Hookom Microsoft Access MVP "Xfree" wrote: The subs are in the detail section of the report. I can only see code for the main form, the subs come up blank when I try to look at code. SQL for Part Cert Form: Private Sub Print_Cert_Click() Dim strWhere As String If Me.Dirty Then 'Save any edits. Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record to print MsgBox "Select a record to print" Else strWhere = "[PartCertificationID] = " & Me.[PartCertificationID] DoCmd.OpenReport "PartCertification", acViewPreview, , strWhere End If End Sub Private Sub Delete_Heat_Click() On Error GoTo Err_Delete_Heat_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_Delete_Heat_Click: Exit Sub Err_Delete_Heat_Click: MsgBox Err.Description Resume Exit_Delete_Heat_Click End Sub Private Sub Command36_Click() On Error GoTo Err_Command36_Click Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind Exit_Command36_Click: Exit Sub Err_Command36_Click: MsgBox Err.Description Resume Exit_Command36_Click End Sub Private Sub Print_Cert1_Click() On Error GoTo Err_Print_Cert1_Click DoCmd.RunCommand acCmdSelectRecord DoCmd.PrintOut acSelection Exit_Print_Cert1_Click: Exit Sub Err_Print_Cert1_Click: MsgBox Err.Description Resume Exit_Print_Cert1_Click End Sub "Duane Hookom" wrote: I would not print subforms. Take the time to save the subforms as reports and then use the sub-reports. Maybe you could provide the actual SQL of the main and sub-reports. What sections are the sub-reports located in? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: Yes the setup below is for the main input form "Part Certification" with the two sub forms. The report is setup just as the main form is but the two Sub forms are pulled into the form so I didn't make sub reports and then pull them into the main report, so they are not sub-reports. The record sources in the report for each Sub is the sub table its self. "Duane Hookom" wrote: Can we assume you actually are referencing sub-reports rather than sub-forms? Is the main report bound only to the Part Certification table and no table that is detailed in the sub-report record source? -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I am still unable to resolve this issue here is what I have: Main form table Part Certification with the key field of PartCertificationID with a one to many join to both subtables Sub form 1 is PartCertSub with the key field of PartCertSub and the foreign key that joins the two tables is PartCertificationID Within this table also it is joined to the Products table by the ProductID field (many to one) The Link Master Fields is PartCerificationID and Link Child is PartCertificationID Sub form 2 is CertMaterialSub with the key field of CertMaterialID and the foreign key to PartCertification table is PartCertificationID This table also joins with the PourSchedule table (many to one) by way of the PourScheduleID foreign key. The Link Master Fields is PartCerificationID and Link Child is PartCertificationID The issue starts when I add more than one record in either sub form one or two. If there is only one record in each of the two sub reports it prints correctly. "Duane Hookom" wrote: Perhaps you should share the record sources of your main report and the 1 sub-report giving the problems as well as the Link Master/Child properties. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: I tried to remove tables but it kills the report. Now this report has 3 sub reports in it 2 out of three of the sub reports prints correctly i.e. only once, just the one sub report is causing me issues of repeating its self on page 2 of what should be a 1 page report. "John Spencer" wrote: Mor Likely you need to remove a table from the query rather than fields. Of course you can use SELECT DISTINCT ... and remove all the fields from the tables that are being used in the sub-report. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Xfree wrote: Daune, I am still battling this issue, I have removed fields but I am still getting the sub-report section printing twice. Any other ideas? "Duane Hookom" wrote: You are somewhat correct. It's more like you have a detail table in your main report's record source that should not be included. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: When you say level of detail are you saying I may have fields in the main report that should only show up in the sub reports? "Duane Hookom" wrote: Usually this suggests you have a level of detail in your main report's Record Source that should only be in a sub-report. -- Duane Hookom Microsoft Access MVP "Xfree" wrote: |
Thread Tools | |
Display Modes | |
|
|