Solved

RTF Output from Fields... Timely!!!

Posted on 2004-10-29
355 Views
Last Modified: 2010-08-05
I have a SQL Server backend database that has a table that contains comments from users in rtf format. I've been given the task of compiling/outputting the comments to .rtf files so they can be sent to the departments that need them. Due to privacy concerns I cannot post actual copies of what I am working with, but below are some dummy samples to give an idea of my project.

Tables:
    Departments:
        Field Name     Data Type
        -------------------------------
        DeptID       -   AutoNumber
        DeptName  -   Text

    Comments Categories
        Field Name     Data Type
        -------------------------------
        CatID         -   AutoNumber
        CatName    -   Text
        DeptID       -    Number

    Comments
        Field Name     Data Type
        -------------------------------
        CatID         -   Number
        Author       -   Text
        Comment   -   Text

So as you can see, it is pretty straightforward to get the comments compiled by category and the department that they need to go to, and the code for that I have alread written. My problem comes from the fact that the "Comment" field in the "Comments" table contains rtf data similar to this:

Comments Record 1:
'**********************
CatID:
    1

Author:
    John Smith

Comment:
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\f0\fs20 This is a test of some basic rtf text...\par
}
'**********************
Comments Record 2:
'**********************
CatID:
    1

Author:
    James Morgan

Comment:
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
{\*\generator Msftedit 5.41.15.1507;}\viewkind4\uc1\pard\f0\fs20 And this is test 2...\par
}
'**********************

My code basically walks throught the tables and outputs each Comment to file using the "Print #" method, however this has posed some problems when for example 2 Authors use bullets in their comments that get outputted to the same file, the second author's entire comment will be garble in the output file even though the underlying text is fine. I have not yet run across any imbedded pictures, but since they have an RTF box to enter their comments into it would be theoretically possibly, and I don't know what it would do to my output.

Does anyone have a suggestion on what the proper way to do this would be?

ThanX in advance!

Sam
0
Question by:will_scarlet7
    9 Comments
     
    LVL 83
    Are you outputting all the comments to the same file? RTF is pretty picky in regards to formatting, tags, syntax, etc etc ... but you should be able to add one users comments, then add another "section" of comments, etc etc ... is this what you're trying to do?
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    Hi LSM,
    ThanX for offering help! Sorry for the delay. I've been away from my dest for a few hours.

    What I am trying to acomplish is to output all the comments in a specifice category to a single RTF file. So using the samples above the RTF file would read:
    '****************************
    This is a test of some basic rtf text...
    And this is test 2
    '****************************

    Of course these sample comments do not contain any formatting, but it would need to be able to handle any formatting and still combine the comments into
    output files by category.
    0
     
    LVL 83

    Accepted Solution

    by:
    You cannot combine 2 rtf files into one (far as I know) directly (in other words, if you just copy all of them to a text file, your RTF program won't parse them correctly). You _could_ add a richtextbox control to a form, iterate the recordset and place each rtf in the richtextbox and select the .text from the richtextbox and append that to a second textbox ...

    The code below is an example of what to do ... you'd have to have a reference to DAO, and you'll have to change the names of the tables/fields/controls to match those in your project.

    Private Sub Command4_Click()

      '/Purpose:
      '/Created: 10/30/2004 09:22 AM
      '/Created By: Scott
      '/ctlRTF and ctlCombine are RichTextBox ActiveX controls

      Dim rst  As DAO.Recordset
      Dim rtb  As RichTextBox

      On Error GoTo Err_Command4_Click
      Set rst = Me.RecordsetClone
      Set rtb = Me.ctlCombine.Object

        Do Until rst.EOF
            If Nz(rst("strPartNumbers"), "") <> "" Then
              Me.ctlRTF = rst("strPartNumbers")
              rtb.Text = rtb.Text & Nz(Me.ctlRTF.Object.Text, "")
            End If 'If Nz(rst("strPartNumbers"), "") <> ""
          rst.MoveNext
        Loop

    Exit_Command4_Click:
      On Error Resume Next
      Set rst = Nothing
      Set rtb = Nothing
      Exit Sub
    Err_Command4_Click:
        Select Case Err
          'case
          Case Else
            MsgBox Err & ":" & Error$, vbCritical, "Form_Form1" & ": " & "Command4_Click"
        End Select
     
      Resume Exit_Command4_Click

    End Sub
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    Hi Scott,
    ThanX for the advice. I had a similar thought that I started trying to work through today. It seems that you can pass multiple blocks of RTF to a RichTextbox object and it will combine them the same as if you were pasting the formatted text directly into wordpad. However it seems to have limitations. Below is the code I've been working with just to test the theory. It seems to freeze at @ 1000 records:

    Sub RTFOutputTest()
        Dim myRTF As New RichTextLib.RichTextBox
        Dim rs As Object
        Dim strSQL As String
        Dim ctr As Long
       
        myRTF.MaxLength = 200000000#
        strSQL = "SELECT * FROM qry_CSData;"
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open strSQL, CurrentProject.Connection, 2, 3
       
        While Not rs.EOF
            ctr = ctr + 1
            If IsNull(myRTF.Text) Or myRTF.Text = "" Then
                myRTF.SelStart = 0
            Else
                myRTF.SelStart = Len(myRTF.TextRTF)
            End If
            myRTF.SelLength = 0
            myRTF.SelText = vbCrLf
            myRTF.SelStart = Len(myRTF.TextRTF)
            myRTF.SelLength = 0
            myRTF.SelRTF = rs!comment
            Debug.Print ctr
            rs.MoveNext
        Wend
       
        Open "C:\Temp\TestRTF.rtf" For Output As #1
        Print #1, myRTF.TextRTF
        Close #1
       
        rs.Close
        Set rs = Nothing
        Set myRTF = Nothing
    End Sub

    Sam
    0
     
    LVL 83
    Your code is basically the same as what I've given you ... how large are your comment files?

    FWIW, I'd try setting the .MaxLength=0 (which basically sets NO limit to the amount of text the control can handle, except for those limits placed by the system's memory).

    The only other difference I see is where I've dimmed the RichTextBox ... you've dimmed a member of the library, whereas I've dimmed the control ... I don't really expect this would make any difference, but you can always try it.
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    Well, I have a few that are near a hundred K at the moment, but most of them are pretty small. At the moment I am testing to see if there is a ceiling for the control and what it would be.

    I'm wondering if my problem could be something to do with a timeout from the SQL Server. I copied the largest comment to a local table in my database and ran it through the test prog above (with a few modifications) and while it was slow it seems to have run.

    If it is not the SQL database timing out... Do you think writing the rtf control to file, then clearing it from memory and reloading it on a interval of say every 100 comments, would improve the performance? It seems to greatly slow down with each comment added after 100 or so.
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    Scott,

    ThanX so much for the advice of using the RichTextBox control. It turned out that the freezing problem was due to passing multiple rtf comments containing bullets directly from the memo field to the rtf object. In the end I settled for using 3 RichTextBox controls that worked in sequence to get the most of speed and acurracy. The code below is my working copy of the test version (Outputted 1755 comments to a single rtf document in under a minute):

    Sub RTFOutputTest()
        Dim myRTF As New RichTextLib.RichTextBox
        Dim myRTF1 As New RichTextLib.RichTextBox
        Dim cmpRTF As New RichTextLib.RichTextBox
        Dim rs As Object
        Dim strSQL As String
        Dim ctr As Long
       
        myRTF.MaxLength = 200000000#
        strSQL = "SELECT * FROM qry_CSData;"
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open strSQL, CurrentProject.Connection, 2, 3
       
        While Not rs.EOF
            ctr = ctr + 1
            If ctr Mod 100 = 0 Then
                If IsNull(cmpRTF.Text) Or cmpRTF.Text = "" Then
                    cmpRTF.SelStart = 0
                Else
                    cmpRTF.SelStart = Len(cmpRTF.TextRTF)
                End If
                cmpRTF.SelLength = 0
                cmpRTF.SelText = vbCrLf
                cmpRTF.SelStart = Len(cmpRTF.TextRTF)
                cmpRTF.SelLength = 0
                cmpRTF.SelRTF = myRTF.TextRTF
                Set myRTF = New RichTextLib.RichTextBox
            End If
            Set myRTF1 = New RichTextLib.RichTextBox
            myRTF1.SelStart = 0
            myRTF1.SelLength = 0
            myRTF1.SelRTF = rs!comment
            If IsNull(myRTF.Text) Or myRTF.Text = "" Then
                myRTF.SelStart = 0
            Else
                myRTF.SelStart = Len(myRTF.TextRTF)
            End If
            Debug.Print ctr
            myRTF.SelLength = 0
            myRTF.SelText = vbCrLf
            myRTF.SelStart = Len(myRTF.TextRTF)
            myRTF.SelLength = 0
            myRTF.SelRTF = myRTF1.TextRTF
            rs.MoveNext
        Wend
       
            If Not IsNull(myRTF.Text) Or myRTF.Text <> "" Then
                If IsNull(cmpRTF.Text) Or cmpRTF.Text = "" Then
                    cmpRTF.SelStart = 0
                Else
                    cmpRTF.SelStart = Len(cmpRTF.TextRTF)
                End If
                cmpRTF.SelLength = 0
                cmpRTF.SelText = vbCrLf
                cmpRTF.SelStart = Len(cmpRTF.TextRTF)
                cmpRTF.SelLength = 0
                cmpRTF.SelRTF = myRTF.TextRTF
            End If
       
        Open "C:\Temp\TestRTF.rtf" For Output As #1
        Print #1, cmpRTF.TextRTF
        Close #1
       
        rs.Close
        Set rs = Nothing
        Set myRTF = Nothing
        Set myRTF1 = Nothing
        Set cmpRTF = Nothing
    End Sub
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    Scott,
    If you'd like an extra 20 points post a comment here:
    http://www.experts-exchange.com/Applications/MS_Office/Word/Q_21187457.html
    (It's a pointer from the Word TA to this question)

    God bless!

    Sam
    0
     
    LVL 15

    Author Comment

    by:will_scarlet7
    I deleted the pointer...
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    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…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    913 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

    15 Experts available now in Live!

    Get 1:1 Help Now