Duplicate Record Information Printed in Report

[bell]

Hi all,



I am currently making a database has two tables, "Route Sheet" and "Rework Sheet"

Route Sheet has two keys: Part Number and Serial Number
Rework Sheet has three keys: Part Number, Serial Number and Sequence Number

So the relationship is:
Route Sheet Part Number -> Rework Sheet Part Number (one to many)
Route Sheet Serial Number -> Rework Sheet Serial Number (one to many)

I have two Forms:
Route Sheet Form and Rework Sheet Form

I have three Reports:
Route Sheet Report
Rework Report
Route and Rework Report

On Route Sheet Form I have two buttons, Print Route Sheet Report (for the current record) and Open Rework Form (for the current record, so open the rework record with the same part number and the same serial number)....These buttons all work correctly

On Rework Sheet Form I have one button, Print Rework Report (for the current record)...which does not work correctly

My main problem is printing the Rework Report for the current record, I get a double print of the same record


Here is my code for the button click event:

Private Sub Print_Rework_Report_Click()
On Error GoTo Err_Print_Rework_Report_Click


    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim strCriteria As String


    stDocName = "Rework Report"
    stLinkCriteria = "[Serial Number]=" & Me![Serial Number]
    strCriteria = "[Part Number]=" & Me![Part Number]
    stLinkCriteria = stLinkCriteria & " AND " & strCriteria
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria


Exit_Print_Rework_Report_Click:
    Exit Sub


Err_Print_Rework_Report_Click:
    MsgBox Err.Description
    Resume Exit_Print_Rework_Report_Click
    
End Sub

I think my where condition for the OpenReport is wrong for the way my Rework Table is outlined...here is an example of the stuff in Rework Table


I think because my Rework info is saved in two records, it double displays the information in the Report...
If I add another Sequence Number for the same Part Number and Serial Number, the report will print the record three times (because there is another record now)

Can anyone help me get my Report to only display the info once?

[June7]

Options:

Report Grouping & Sorting features.

Report/Subreport arrangement.

[bell]

Options:

Report Grouping & Sorting features.

Report/Subreport arrangement.

Could you expand on this?

[June7]

Example:
Tables for Orders and OrderDetails. If report is based on query that joins these tables, the Order info will display with each OrderDetails record. Use report Grouping & Sorting features to set up a group section on the Order number and put data from Orders table into the group header. Data from OrderDetails into detail section. The Order info will not repeat.

Another approach is to set textbox HideDuplicates property to Yes.

Another approach is a main report bound to Orders table and a subreport bound to OrderDetails.

If none of these methods solve your issue, need to review db structure and report design. If you want to provide, follow instructions at bottom of my post.

[bell]

Example:
Tables for Orders and OrderDetails. If report is based on query that joins these tables, the Order info will display with each OrderDetails record. Use report Grouping & Sorting features to set up a group section on the Order number and put data from Orders table into the group header. Data from OrderDetails into detail section. The Order info will not repeat.

Another approach is to set textbox HideDuplicates property to Yes.

Another approach is a main report bound to Orders table and a subreport bound to OrderDetails.

If none of these methods solve your issue, need to review db structure and report design. If you want to provide, follow instructions at bottom of my post.

Thanks, I think I had a problem with how I set up a subreport that was in the Rework Report, I set up the field links between the two wrong...after rewritting it I fixed the problem