Solved

Text is repeating in Access report

Posted on 2012-03-12
9
238 Views
Last Modified: 2012-03-13
Hello,

I am running a report, an image of which is attached below.  The comment field is populated via the following code.  When the report appears in preview, it looks fine.  However when it is printed on a printer, the first line in the comments field is repeated and is appended to the end of the comment.  For exampe

Preview
"Mary had a little lamb "
"whose fleece was white as snow"
"and every where where mary went"
"the lamb was sure to go"

Printer Output

"Mary had a little lamb "
"whose fleece was white as snow"
"and every where where mary went"
"the lamb was sure to go"
"Mary had a little lamb "

Option Compare Database

Option Explicit

Dim lngLabelCount As Long

Dim strComments As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_Detail_Format

    strComments = strComments & " " & Me.ECOComments

Exit_Sub:

    Exit Sub

Err_Detail_Format:

    MsgBox ("Error Number :" & Err.Number & " Error Description:  " & Err.Description)

    Resume Exit_Sub

End Sub

 

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_GroupFooter0_Format

    Me.txtComments = LTrim(strComments)  

Exit_Sub:

    Exit Sub

Err_GroupFooter0_Format:

    MsgBox ("Error Number :" & Err.Number & " Error Description:  " & Err.Description)

    Resume Exit_Sub

End Sub

 Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_GroupHeader0_Format

 

    'strComments = ""

 Exit_Sub:

    Exit Sub

Err_GroupHeader0_Format:

    MsgBox ("Error Number :" & Err.Number & " Error Description:  " & Err.Description)

    Resume Exit_Sub

End Sub
Report.JPG
0
Comment
Question by:chtullu135
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 37711104
The Format event can get called more than once for each object in a report. This is the case in the sections that have repeated text in your report. To prevent the repeats, use the FormatCount parameter to determine if the event is being called the first time and only run your code then:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Err_Detail_Format
If FormatCount = 1 Then
    strComments = strComments & " " & Me.ECOComments
End If
Exit_Sub:
    Exit Sub
Err_Detail_Format:
    MsgBox ("Error Number :" & Err.Number & " Error Description:  " & Err.Description)
    Resume Exit_Sub
End Sub
You'll need to repeat this  IF in each section that has code on the Format event.

HTH,

pT72
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37711298
Hi,

I would suggest you create the Comments and trim the value  as part of the report's recordsource and then bind to the comments to the txtComments.  That way you can be sure of the results.

Regards,

Bill
0
 

Author Comment

by:chtullu135
ID: 37711521
Each line in the report's comment control is from a different record in the underlying database.  The data was imported from a legacy main frame application where each line was stored in a different record.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37711832
Hi,

In my experience,  you can prepare the data in a a query join more easily than in the report and the bonus is that the query is reusable.

Regards,

Bill
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37712261
Screenshots and code are useful, but a sample DB that exhibits the issue is always best.
For example, there is a myterious subreport there that may effect this, and there are literally half a dozen properties that may also be involved here.

In any event...

1. Your Output does not match your code.
AFAICT Your code will produce:
Mary had a little lamb whose fleece was white as snow and every where where mary went the lamb was sure to go.

Not:
Mary had a little lamb
whose fleece was white as snow
and every where where mary went
the lamb was sure to go

...Unless, again, there is something else at play here.


2. I usually clear the "accumulation" variable in the last line of the group footer, not in the group header.

This works fine for me with no dupes...

Dim strprvtData As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    strprvtData = strprvtData & vbCrLf & Me.Data
    'Or
    'strprvtData = strprvtData & " " & Me.Data

End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtFullData = strprvtData
    strprvtData = ""
End Sub
Database67.accdb
0
 

Author Comment

by:chtullu135
ID: 37713083
Hello boag2000,

My mistake.  Output number one is the correct output.  I am getting the following when the report is only outputted to a printer
"Mary had a little lamb whose fleece was white as snow and every where where mary went the lamb was sure to go. Mary had a little lamb"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37714527
Did you test my sample?
Again, it works fine for me in Print Preview and the actual Printout...
Let me know.


As I stated, there may be many factors affecting this.
So try my sample first and report back.
If my sample works properly then compare my DB to yours.

If you can't get it going, then post a sample DB.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 37714830
Hi,

Did you try my suggestion of preparing the data before printing the report?  I still believe that is an easier way to solve your issue.  

Suggest you create a function that is similar to your OnFormat event and combine the data in a query.  Once you have the data prepared the report will not fail.

Regards,

Bill
0
 

Author Closing Comment

by:chtullu135
ID: 37717763
Thanks that was the problem.  I had cleared the accumulator strComments in the GroupHeader instead of the footer.  Thanks again.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now