Report Generates Duplicate Lines

[Perceptus]

I have a report that is powered by a query. When I view the query, everything looks as I would hope. When I try to make a report that consumes this query, I get duplicate lines. I am using Microsoft Office Access 2003 (11.8321.8341) SP3.

How do fix this?

[pbaldy]

Shouldn't happen if the query is the source. You don't have fields in both a group header and the detail section do you?

[Perceptus]

I used the wizard to make the report for the grouping. It does not appear to have the flaw you mentioned. I am currently trying to step through stop statements on the formatting to figure out when it happens.

The duplicates start at the first group and continue into the rest. I have tried removing the group headers, this didn't help as the amount of data returned didnt seem to change.

[pbaldy]

`Can you post the db here, or at least the code that's running?

[Perceptus]

what parts?

Private Sub cmdWRProdCustomer_Click()
On Error GoTo Err_cmdWRProdCustomer_Click


    Dim stDocName As String


    stDocName = "qryMetricsWRProduction_Company"
    DoCmd.OpenReport stDocName, acPreview


Exit_cmdWRProdCustomer_Click:
    Exit Sub


Err_cmdWRProdCustomer_Click:
    MsgBox Err.Description
    Resume Exit_cmdWRProdCustomer_Click
    
End Sub

[pbaldy]

It sounded like there was code running within the report?

[Perceptus]

Nah, I was only using debug.print in the events for detail format and header format. the problem seems to occur before those events.

[pbaldy]

Can you post the db here, or a representative sample?

[Perceptus]

The query that is used is

SELECT tblCustomer.CompanyName, tblFoundryReceiptLI.LongDescription, 
tblFoundryReceiptLI.Qty, tblSalesOrders.SalesOrderID, tblSalesOrders.OrderDate, tblSalesOrders.DatePromised, tblFoundryReceiptLI.DatePartPoured, tblOrderOfMaterials.Material, tblSalesOrders.ShipDate, tblFoundryReceiptLI.autom_mold_date, tblFoundryReceiptLI.autom_pour_date, tblFoundryReceiptLI.autom_clean_date, tblFoundryReceiptLI.autom_wr_date, tblFoundryReceiptLI.autom_ship_date, tblFoundryReceiptLI.autom_hs_date, CInt(CDate([tblSalesOrders].[DatePromised])-Now()) AS days_until_due, tblSalesOrders.DatePromised, tblFoundryReceiptLI.isNotes, tblFoundryReceiptLI.frIDFROM (((tblFoundryReceiptLI INNER JOIN (tblSalesOrders INNER JOIN tblSalesOrdersInventoryLIneItems ON tblSalesOrders.SalesOrderID = tblSalesOrdersInventoryLIneItems.SalesOrderID) ON tblFoundryReceiptLI.SalesOrderID = tblSalesOrders.SalesOrderID) INNER JOIN tblCustomer ON tblSalesOrders.CustomerID = tblCustomer.CustomerID) INNER JOIN tblPartMaster ON tblFoundryReceiptLI.PartNumber = tblPartMaster.PartNumber) INNER JOIN tblOrderOfMaterials ON tblPartMaster.MaterialCode = tblOrderOfMaterials.MaterialCode
WHERE (((tblFoundryReceiptLI.frID)="1" Or (tblFoundryReceiptLI.frID)="2"));

not really sure if you want test data for each table?

[pbaldy]

But you said the query returned records correctly, right? I was hoping for a db that recreated the problem, so we could figure out where the duplication is coming from.

[Perceptus]

Its a workgroup database, I can't give out the login info.

Interesting, I changed the query to a Make table query using Access and let it do its thing. The resulting table had all the duplicates that I am seeing on the report. Change the query back to a select and I get all the rows without the duplicates.

[Perceptus]

I found my answer here.

http://www.ehow.com/how_8229282_supp...ss-report.html

I am still mystified as to why I would need to do this. Changing this option doesn't affect the data when i view the query or bind the query to a form. So odd.

I would mark this solved. But i dont know how.

[pbaldy]

That would imply the query was returning duplicates, but I thought it wasn't. In any case, you can mark the thread solved in Thread Tools at the top of the thread.