Solved

Access2003: How do I remove white space Access adds when a text report is emailed

Posted on 2010-11-24
11
591 Views
Last Modified: 2012-05-10
I apologise for the length of the question but I want to present as much info as possible. I support an old Access database (originally 97, now 2003) that is used to build letters sent to test candidates. The letters are sent to people all over the world and must be emailed in text format. The letters are built from data passed to the Access database from a Java application, data retrieved from an SQL Server database and templates stored in the Access database.

Access occaisionally, with no discernable rhyme or reason, adds varying amounts of white space (extra blank lines) between sections of a report. This doesn't happen for every client and every letter type but does happen consistently for certain clients for a specific letter type.

The report templates consist of a number of sections. There are page header and footer, section headers and footers and a detail section. Which sections are to be printed are determined by the location in the world to which the report is to be sent, the client for whom the letter is being sent, and the specific contents (cost, discount, fees, and the like) the client selected. One section that is included in the template contains a text field that holds custom text for the specific client. This text box is immediately before the report footer.

Part of the formatting of the text box and the following section includes imbedded blank lines (CR+LF characters). There are 2 sets at the end of the custom text and 3 sets at the start of the text in the next section.

The report displays as it should in print preview mode (as plain text) but when it is sent via email (also as plain text) there are an additional 25 blank lines between the two sections. I've checked the can grow and can shrink settings for both the text box and the report section that contains it and all are set to yes to allow growth or shrinking.

Any ideas that can identify a cause and or a solution will be greatly appreciated.
0
Comment
Question by:Ironranger
  • 7
  • 4
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Can you post a sample of this database that exhibits this phenomenon?

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. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.

JeffCoachman
0
 

Author Comment

by:Ironranger
Comment Utility
Hi Jeff,

Thanks for the note. Unfortunately, the Access database is covered by a copyright and both the Access database and SQL Server database are covered by HIPPA and other data privacy requirements. So, sending a copy of them isn't possible. I will see if I can send sample data and, at least, the part of the letter template in which the problem occurs. I won't be able to do that until Monday.

Are there other questions I might answer that could help to resolve the problem?

Tom
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
1. <sending a copy of them isn't possible.>
I never asked for a "copy", ...only a "Sample"
In other words, you can even create a simple Fake database, as long as the same issue can be replicated...

2. <Are there other questions I might answer that could help to resolve the problem?>
Not really, having 2+3 (5) line breaks and then having 25 show up in the email is strange...
There must be something else at play here, that perhaps only a sample could reveal...

JeffCoachman
0
 

Author Comment

by:Ironranger
Comment Utility
I don't have access to the test database and sample data right now. I'll put something together and send it Monday.

One of the challenges of this problem is that I'm the current support person in a list of maybe 10 people over the course of 15 years. There are pieces of the database that everyone has been afraid to delete because it's not known if there are other programs that access the Access database that use them. It's being replaced by a new tool and should only be around for another 12 - 15 months so the company doesn't want to spend a lot of money upgrading it. So, there have been a ton of bandaids slapped on a whole bunch of problems and most of the people who did patching aren't around.

Again, thanks for taking the time. I'll try to get more info to you Monday.

Tom
0
 

Author Comment

by:Ironranger
Comment Utility
Here is some additional information on the problem I'm having with the appearance of extra space in the reports (letters) being generated by Access.

The letters are based on templates stored in the Access database.
VBA code in the program performs the following actions as part of the letter generation process:
  1. The database is entered via a call from a custom DLL.
       The DLL passes a parameter package that contains the call ID, client ID, order ID, order item ID,
       candidate ID, recipient information and letter type.
      Note: not all parameters are required but at least the call ID, order ID and/or order item ID
                              must be included.
  2. The type of letter is decoded and a function that manages the processing of that letter type is
       called.
  3. The called function verifies the parameters, retrieves any additional information (client, candidate,
      recipient, destination address, custom letter content) that is needed for the letter.
  4. The appropriate letter template is identified and opened.
  5. The template data source is a query that calls a stored procedure in an SQL database. The
      parameters passed to the stored procedure are those that have been described above.
  6. The letter template with which I'm having the problem is composed of the following sections, each
       of which have VBA code modules:
      * ReportHeader
      * OrderInfoHeader
      * Detail
      * OrderItemID Footer 1 - EligibleFooter
      * OrderItemID Footer 2 - VTS1_6OrMore      
      * OrderItemID Footer 3 - GroupFooter0
      * OrderID Footer 1            - Custom Client confirmation text
      * OrderID Footer 2            - Survey Section
      * Report Footer
      The problem is occurring between the Custom Client confirmation text and the Survey Section
7. The Custom Client confirmation text contains a single unbound textbox control. This text box is populated by the following VBA code -

Private Sub OrderIDFooter_Format(Cancel As Integer, FormatCount As Integer)
' Defualt for IT letters is always show the survey
   
  Dim retValue As String
  Dim retStr As String
   
    retValue = GetLetterOption(txtClientID, optSURVEY)
    If isEmpty(retValue) Or retValue = "True" Then
    ' defualt behavor
        Me.txtSurvey.Visible = True
    Else
        Me.txtNoSurvey.Visible = True
        Me.txtSurvey.Visible = False
    End If
     
    retStr = GetLetterOption(txtClientID, optEXAMCUSTOMSCRIPT)
    If Not isEmpty(retStr) And retStr = "True" Then
        Me.txtClientCancelPolicyText = Get_ClientCancelPolicy(txtClientID) & " " & _
            Get_ClientExamCancelPolicy(txtClientID, txtExamSeriesID) & " " & _
            Get_ClientCancelBoilerPlate() & vbCrLf & " "
    Else
        Me.txtClientCancelPolicyText = Get_ClientCancelPolicy(txtClientID) & " " & _
            Get_ClientCancelBoilerPlate() & vbCrLf & " "
    End If
           
End Sub

Definitions:
    txtClientID      - Report field contains the client ID
    optSurvey      - Constant used to check to see if client wants survey request included in letter      
    GetLetterOption      - Checks to see if specified option is true for client
    txtSurvey      - Survey Section textbox that contains the survey invitation text, visible by default
    txtNoSurvey  - Survey Section textbox that contains closing text that does not include the survey
                            invitation, hidden by default
    optEXAMCUSTOMSCRIPT  - Constant used to check to see if client has provided custom
                            confirmation text
    txtClientCancelPolicyText   - Text box control in the Custom Client confirmation text OrderID Footer 1
                            section
    Get_ClientCancelPolicy      - Retrieves custom confirmation text
    Get_ClientExamCancelPolicy    - Retrieves custom text associated with a particular exam
    Get_ClientCancelBoilerPlate     - Retrieves cancelation text boiler plate that is included in all letters.
   
   8. The SurveySection (OrderID Footer 2) contains the following text box controls:

txtSurvey  - Contents:

=" " & Chr(13)+Chr(10) & Chr(13)+Chr(10) & [ClientName] & " services provided by: $$$$$$$$$$" & Chr(13)+Chr(10) & Chr(13)+Chr(10) & "Sincerely," & Chr(13)+Chr(10) & "$$$$$$$$$$" & Chr(13)+Chr(10) & Chr(13)+Chr(10) & "
For a complete list of phone numbers, please refer to this site: http://www. $$$$$$$$$$.com/contact

Please ensure that the exam details listed above are correct. If any details of this appointment are not correct, contact the $$$$$$$$$$ immediately.

It is extremely important to us that you receive the best quality testing experience.  After your exam, please visit " & get_LineFeed() & get_LineFeed() & [txtFeedBackURL] & "/index.cfm?SurveyID=" & EncodeOrderID([Order_ID],[txtScreenID],[LangCode]) & "
 which will appear on your Thank You email, and answer a few short questions concerning your testing experience.  " & " " & Chr(13)+Chr(10) & "=================================================" & Chr(13) & Chr(10) & "If you would like to register for an exam, or view your exam history with $$$$$$$$$$, please visit our web site at http://www. $$$$$$$$$$.com  Please use your username and password to gain access to this site. " & Chr(13)+Chr(10) & Chr(13)+Chr(10) & Chr(13)+Chr(10)
Please ensure that the exam details listed above are correct. If any details of this appointment are not correct, contact the $$$$$$$$$$ immediately.

txtNoSurvey  - Contents:

=" " & Chr(13)+Chr(10) & Chr(13)+Chr(10) & [ClientName] & " services provided by: $$$$$$$$$$" & " " & Chr(13)+Chr(10) & Chr(13)+Chr(10) & "Sincerely,  " & Chr(13)+Chr(10) & "$$$$$$$$$$" & Chr(13)+Chr(10) & Chr(13)+Chr(10) & "=================================================" & Chr(13) & Chr(10) & "If you would like to register for an exam, or view your exam history with $$$$$$$$$$, please visit our web site at http://www. $$$$$$$$$$.com  Please use your username and password to gain access to this site.  

Please ensure that the exam details listed above are correct. If any details of this appointment are not correct, contact the $$$$$$$$$$immediately.

For a complete list of phone numbers, please refer to this site: http://www. $$$$$$$$$$.com/contact
"

9. For most clients the affected section of the letter is generated as  -

*
*
*
You will not be allowed to take any personal items with you into the testing room. This includes all
bags, books or other materials not authorized for this exam, notes, cell phones, pagers, watches
and wallets.




$$$$$$$$$$ services provided by: $$$$$$$$$$

Sincerely,
$$$$$$$$$$


For a complete list of phone numbers, please refer to this site: http://www. $$$$$$$$$$.com/contact

Please ensure that the exam details listed above are correct. If any details of this appointment are not
 correct, contact the $$$$$$$$$$ immediately.

It is extremely important to us that you receive the best quality testing experience.  After your exam,
please visit

https:// $$$$$$$$$$.$$$$$$$$$$.com/index.cfm?SurveyID=$$$$$$$$$$$$$$$$$$$$
 which will appear on your Thank You email, and answer a few short questions concerning your
testing experience.
=================================================
If you would like to register for an exam, or view your exam history with $$$$$$$$$$, please visit our web
site at http://www. $$$$$$$$$$.com  Please use your username and password to gain access to this
site.

10. For letters with the problem the affected section is generated as  -

*
*
*
You will not be allowed to take any personal items with you into the testing room. This includes all
bags, books or other materials not authorized for this exam, notes, cell phones, pagers, watches
and wallets.































$$$$$$$$$$ services provided by: $$$$$$$$$$

Sincerely,
$$$$$$$$$$


For a complete list of phone numbers, please refer to this site: http://www. $$$$$$$$$$.com/contact

Please ensure that the exam details listed above are correct. If any details of this appointment are not
 correct, contact the $$$$$$$$$$.

It is extremely important to us that you receive the best quality testing experience.  After your exam,
please visit

https:// $$$$$$$$$$. $$$$$$$$$$.com/index.cfm?SurveyID=$$$$$$$$$$$$$$$$$$$$
 which will appear on your Thank You email, and answer a few short questions concerning your
testing experience.
=================================================
If you would like to register for an exam, or view your exam history with $$$$$$$$$$, please visit our web
site at http://www. $$$$$$$$$$.com  Please use your username and password to gain access to this
site.




0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Ironranger
Comment Utility
I hope that my last post helps to explain the problem a bit more clearly. The database contains around 60 templates that are used to generate letters and has several hundred pages of VBA code contained in modules, reports and forms. That makes it a bit difficult to narrow down what information may be helpful.

I spent some time over the last few days looking at the other templates and have found that the problem exists in many of them. There are additional blank lines added between sections and at the end of the letters. Using a hex editor, I've looked at the actual characters in the letter and was able to verify that carriage return/line feed pairs are being inserted.  I haven't been able to find a reference that would explain why it's happening.

I can create a function that will remove the unwanted lines but would rather identify and solve the problem than slap another band-aid on the program.

Any ideas as to the cause of the problem or places to look for examples or discussion will be greatly apreciated.

Tom
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
I will really need a sample db to troubleshoot this...
0
 

Author Comment

by:Ironranger
Comment Utility
I'll have to see if I can come up with a way to create a sample database that has the same issue. In the long run it may be simpler/quicker to just create the function to remove extra blank lines.

Thanks for taking the time to look at this.

Tom
0
 

Accepted Solution

by:
Ironranger earned 0 total points
Comment Utility
After much frustration, I did write a function to remove the extra blank lines. I'm not 100% sure what the cause of the problem is but it does seem to be tied to the printer driver used to create the text file for the letter. I've copied the function below in case it may be of use to someone else.

Code listing -

Public Function gfRemoveBlankLines(ByVal strLetterPathandName As String) As Boolean
On Error GoTo Err_gfRemoveBlankLines
'
'  ************************************************************************************************************************
'
'   Global Function:    gfRemoveBlankLines
'
'   The purpose of this function is to remove any extraneous blank lines that are inserted into
'   email letters when they are sent out.
'
'   Author:     Tom Hope
'   Version:    1
'   Date:       2010/12/09
'
'   Inputs:
'           gblnDevMode                         Boolean   used to indicate if software is in development mode
'           gintNumberBlankLinesAllowed         Integer   number of consecutive blank lines allowed
'           strLetterPathandName                String    Location and name of text file to check
'  ************************************************************************************************************************
'
'
Const forRead = 1, forWrite = 2, forAppend = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim intCrLfCount As Integer, intMaxBlankLines As Integer

Dim errNum As Long
Dim errMsg As String

Dim fs As Object
Dim inFile As Object, outFile As Object, inTS As Object, outTS As Object

Dim rs As ADODB.Recordset

Dim strSQL As String, strInFilePath As String, strOutputFile As String
Dim strInLine As String, strBlankLine As String

    strInFilePath = strLetterPathandName

    intMaxBlankLines = gcintNumBlankLinesAllowed
    strBlankLine = Chr(13) & Chr(10)
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set inFile = fs.GetFile(strInFilePath)
    Set inTS = inFile.OpenAsTextStream(forRead, TristateUseDefault)
    
    Do While Not inTS.AtEndOfStream
        strInLine = inTS.ReadLine
        If Len(strInLine) > 2 Then
            strOutputFile = strOutputFile & strInLine & vbCrLf
            intCrLfCount = 0
        Else
            intCrLfCount = intCrLfCount + 1
            If intCrLfCount <= intMaxBlankLines Then
                strOutputFile = strOutputFile & strBlankLine
            End If
        End If
    Loop
    
    inTS.Close
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next                                                 ' Handle errors here
    '
    '   Try to open output file, catch File Not Found error
    '
    Set outFile = fs.GetFile(strLetterPathandName)
    errNum = Err.Number
    If errNum <> 0 Then                                                  ' File found if no error
        On Error GoTo Err_gfRemoveBlankLines                             ' Reset error handler
        If errNum = 53 Then                                              ' If caught File Not Found error
            fs.CreateTextFile strOutputFile                              '   then create the file
            Set outFile = fs.GetFile(strOutputFile)
        Else
            Err.Raise errNum                                             '   otherwise send error to error exit
        End If
    End If
    '
    '   Open letter output file
    '
    Set outTS = outFile.OpenAsTextStream(forWrite, TristateUseDefault)

    outTS.Write strOutputFile
    outTS.Close

    gfRemoveBlankLines = True
    
    If gblnDevMode Then
        MsgBox "Updated letter saved successfully.", vbOKOnly, "Save Success"
    End If

Exit_gfRemoveBlankLines:

    Set fs = Nothing
    Set inFile = Nothing
    Set inTS = Nothing
    Set outFile = Nothing
    Set outTS = Nothing
    Set rs = Nothing
    
    Exit Function

Err_gfRemoveBlankLines:

    If gblnDevMode Then
        MsgBox "ERROR encountered in gfRemoveBlankLines: " & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description
    End If

    gfRemoveBlankLines = True

    Resume Exit_gfRemoveBlankLines
    
End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Hey, if it works...

...Roll with it...

;-)

JeffCoachman
0
 

Author Closing Comment

by:Ironranger
Comment Utility
The fix works but doesn't really answer the question of how or why the problem is occurring.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

772 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

14 Experts available now in Live!

Get 1:1 Help Now