Results 1 to 9 of 9

Thread: Report Problem

  1. #1
    Join Date
    Aug 2012
    Posts
    49

    Unanswered: Report Problem

    Hi again all,
    I have an odd little problem with a report. I have a command button on a form which i want to use to print the contents of that form once in the shape of a report. It prints the report ok but it does 142 copies of the same record ??. The code i have on the OnClick event on the command button is below.

    Code:
    Private Sub cmdPrint_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 = "[AssessmentID] = " & Me.[AssessmentID]
            DoCmd.OpenReport "Rpt_EditPrint", acViewPreview, , strWhere
        End If
    End Sub
    Thanks guys,
    Jen

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the actual value of strWhere as opposed to the VBA code
    is AssessmentID a numeric column?

    is your printer set to print 145 copies?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    49
    This is code i used successfully in another database to do the same thing but isn't working this time. How do i get the value of strWhere ?. To be honest i just copied and pasted the code and changed the names of the Field and Report.

    No the printer wasn't set to print that many copies and yes AssessmentID is a numeric field (PK)

    Actually when i view the report, the record displays perfectly, then there's a blank page and then another copy of the record, another blank page and another copy of the record etc. All the way up to 142

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    • Some Questions
    • Are you absolutely positive that the Datatype for the AssessmentID, as defined in the Table, is Number or Autonumber, not Text? Data containing all digits can be defined as Text, and often is.
    • Are you positive that the Record Source for the Report only has one Record for the given AssessmentID?
    • How is this PK populated, i.e. by Autionumber, an auto-incrementing number hack, manually entered by the users from an extra-database source?
    • Is the RecordSource a Table or a Multi-Table Query?
    • What happens if you independently run your Report in Preview Mode?
    You also have a major logic problem with your current code! The test

    If Me.NewRecord Then

    is always going to return a False! You've just saved the Record with your code

    If Me.Dirty Then Me.Dirty = False

    so it will never be a New Record when that line of code is reached! And if you're going to save the Record, anyway, simply do that! What you probably need to check for is whether or not [AssessmentID] has been populated:
    Code:
    Private Sub cmdPrint_Click()
        
     Dim strWhere As String
    
     If Nz(Me.AssessmentID, "") <> "" Then
      
      If Me.Dirty Then Me.Dirty = False
        
        strWhere = "[AssessmentID] = " & Me.AssessmentID
       
        DoCmd.OpenReport "Rpt_EditPrint", acViewPreview, , strWhere
        
      Else
        
        MsgBox "Your Record Must Have an AssessmentID Before You Can Print It!"
        
        AssessmentID.SetFocus
        
      End If
    
    End Sub

    Also not that you can write an If...Then construct on a single line, as I did, and when you do, you do not need an End If statement, as you normally would.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2012
    Posts
    49
    * The datatype is definitely defined as a Number in the table.

    * Yes, the database is brand new and theres only one test record in there.

    * The number is manually entered by the user.

    * The record source is a multi-table query

    * If i run the report independently, the record will appear perfectly in the report but using the navigation buttons at the bottom, i can cycle through the same report 192 times.

    * I substituted my old code for your code and the same thing is happening.

    * I checked and yes the AssessmentID field is being populated. I also checked all the other tables associated with the query on which the report is based and they've only got that one record in them also.

    Thanks

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If i run the report independently, the record will appear perfectly in the report but using the navigation buttons at the bottom, i can cycle through the same report 192 times.
    If this is happening, there is a problem in the query that populates the report. It is outputting 192 lines for the one record that you want! You need to correct this problem, and then the report will print as you expect.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The fact that running the Report independently, without using the Form, generates the same number of copies pretty much brings the problem home to the Report, itself.

    I have never heard of a Report being corrupt, but I see no reason why it couldn't happen; Forms, after all, become corrupt all the time. You might try creating a new, blank Database and importing everything into it and see if that resolves the problem; it often does where corruption is involved. Second fix would be re-creating the Report from scratch.

    Barring this, I'd have to agree with weejas, that the culprit is the Multi-Table Query your Report is based on.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Aug 2012
    Posts
    49
    Hi Guys,
    You were right, it was the query. I had 3 tables in there which were unnecessary for the query to run. I removed them and voila !......everything is perfect !

    Thanks very much for helping me...i really appreciate your time and patience !

    Cheers,
    Jenny

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help. Jenny!

    Good luck with your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •