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=CertMaterailSub.PartCertificationID) ON
PourSchedule.PourScheduleID=CertMaterailSub.PourScheduleID) 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 said:
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
:
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
:
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
:
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.
:
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
:
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.
:
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
:
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.
:
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?
:
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
:
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?
:
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
:
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
