none
Access 2013: DoCmd.OpenReport using WHERECONDITION parameter shows duplicate record RRS feed

  • Question

  • Hello,

    I have a form the user can view records 1 at a time in a table. Displayed on the form is a Print Record button. The user can press the Print button and the record will be displayed in a report I have created. I use the following statements to print the record in the report.

    strReportName = "Print Comparable Report"
    Me.[Comparable ID].SetFocus
    strCriteria = "[Comparable ID] = " & Me![Comparable ID].Value
    DoCmd.OpenReport strReportName, acViewNormal, , strCriteria

    DoCmd.Close acReport, "Print Comparable Report"

    The Comparable ID is the primary key, Auto Number, in the table and is set to No Duplicates.

    Problem I having is the same record is printed twice. Identical record in all fields.

    When I add a breakpoint, I see the result of OpenReport is 2 identical records. Interesting thing is this does not happen to all records in the table. For a majority of records in the table, when OpenReport is executed, only the 1 record appears in the report and only 1 record is printed. I performed a Find Duplicates query on the table. No duplicate records are found in the table. I ran a Test Query and specified the criteria to the exact Comparable ID (Comparable ID = 2465) that is printing twice or appears twice in the OpenREport and the query comes back with 2 records when it should be only 1 record. Both records have identical values in all fields. Any help is much appreciated. Thanks.

    Monday, February 22, 2016 6:02 PM

Answers

  • I don't think you will find any dups since you said that your PK with no dups. I'd create a new report from scratch. Don't copy anything from the original one as it appears to be corrupt.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Tuesday, February 23, 2016 3:56 PM

All replies

  • What is the Record Source of the report?

    Build a little, test a little

    Monday, February 22, 2016 6:13 PM
  • The Record Source of the Report is the same table as the Comparable ID is coming from.
    Monday, February 22, 2016 6:56 PM
  •  I performed a Find Duplicates query on the table. No duplicate records are found in the table. I ran a Test Query and specified the criteria to the exact Comparable ID (Comparable ID = 2465) that is printing twice or appears twice in the OpenREport and the query comes back with 2 records when it should be only 1 record.

    Run this query --

    SELECT [YourTable].[Comparable ID], Count([YourTable].[Comparable ID]) AS CountOfComparableID

    FROM  [YourTable]

    GROUP ON  [YourTable]

    HAVING Count([YourTable].[Comparable ID]) > 1;


    Build a little, test a little

    Monday, February 22, 2016 9:48 PM
  • I tried to run the query, but I get a syntax error: Syntax Error is GROUP BY Clause. I changed the the GROUP ON statement to GROUP BY, but get another error 'Your query does not include the specified expression 'Comparable ID' as part of an aggregate function'. I added to the GROUP BY [YourTable].[Comparable ID]. The query runs and I get no records in the query.
    • Proposed as answer by Rivington2 Wednesday, March 2, 2016 6:44 PM
    • Unproposed as answer by Rivington2 Wednesday, March 2, 2016 6:44 PM
    Tuesday, February 23, 2016 3:30 PM
  • I don't think you will find any dups since you said that your PK with no dups. I'd create a new report from scratch. Don't copy anything from the original one as it appears to be corrupt.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Tuesday, February 23, 2016 3:56 PM
  • Creating a new report has resolved the problem. Now I get 1 record in the query, hence only 1 record gets printed.

    Thank you.

    • Proposed as answer by Rivington2 Wednesday, March 2, 2016 6:46 PM
    • Unproposed as answer by Rivington2 Wednesday, March 2, 2016 6:46 PM
    • Proposed as answer by Rivington2 Wednesday, March 2, 2016 6:47 PM
    Wednesday, March 2, 2016 6:46 PM