Text is repeating in Access report
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
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
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
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
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
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
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
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
ASKER
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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"
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
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
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
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
ASKER
Thanks that was the problem. I had cleared the accumulator strComments in the GroupHeader instead of the footer. Thanks again.
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