[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

RTF Output from Fields... Timely!!!

Posted on 2004-10-29
9
Medium Priority
?
378 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
Comment
Question by:will_scarlet7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 85
ID: 12443304
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
ID: 12444405
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 12451952
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Author Comment

by:will_scarlet7
ID: 12452037
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 85
ID: 12452104
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
ID: 12452228
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
ID: 12453436
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
ID: 12453451
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
ID: 12483558
I deleted the pointer...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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