Using a variable to compile to a rich text report

I want to be able to compile a rich text report using VBA.  I am constructing my data to include verbiage and variables  and then I use cases to loop through an entire report to fill a string variable with all the text, variable results, and things like vbCr's to make a report.  I do not want to construct the report in Word, but after edits I want to output it to Word.

My problem:  If I convert the field in the table with the verbiage "blurbs" to Rich text, and I use the immediate window to view the compiled information, I get HTML tags.    I want to be able to output the compiled verbiage to either a text box on a form or a report -- I am ambivalent as to which one.  

How to I get my variable to "dump" the compiled formatted verbiage and variables to a form or report?  Is there a command to do this?
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:

Let me see if I understand.

...Once you "Construct" your HTML formatted string:
ex: "<b><i>Bold Italic Text</b></i>"

You can take this HTML string and insert it into a textbox in a report and have it appear "Formatted".

You can use some fairly basic code to do this.
(Note that the "Text Format" property of the Target Texbox Must be set to "Rich Text" in order for the text to appear formatted)
The Code will be something like this:

Dim strHTML As String
strHTML = "<b><i>Bold Italic Text</b></i>"
Me.txtDisplayHTML = strHTML

How you get this into MS Word as formatted text is a *Totally* different issue.

I would first ask why you need a system this complicated in the first place...?
<I want to be able to compile a rich text report using VBA.  I am constructing my data to include verbiage and variables  and then I use cases to loop through an entire report to fill a string variable with all the text, variable results, and things like vbCr's to make a report. >

You can do it in "On format" event of report (or in any event in form). Field on report can be filled with variable (if it is declared and assigned in report module or it is global).
Me.SomeMyField = MyVariable1 & vBCrLf & MyVariable2
You can also use Format function.
Are you expecting to be able to export the html formatted document ito Word and retain the formatting?
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I use an Unbound text box set to can grow.

I use the report section's On Format to calculate the text and assign to the unbound text box.

Exporting the report to word is problematic. I usually have to do some tweaks to the report's design to get it to export and keep most of the formatting.
jwandmrsquaredAuthor Commented:
BOAG, it is this complicated because I am building a text string/report with 9 different case situations from a simple boldfaced heading to reporting the verbiage that corresponds to the chosen option in an option box.  It works pretty slick in choosing the right verbiage and in the transitional phrases. I've seen this done in a Word template, BUT a programmer has to change the verbiage and order of transitional words AND you can't go back and modify answers whereas with my system a user can do all of this.

Settting the final loaded variable to a text box on my form shows initial promise (in other words, it worked right) except that it inserts a VBcr after each verbiage section.  Can this be turned off?
Jeffrey CoachmanMIS LiasonCommented:
Well since you are not posting the resulting string, this is difficult to tell...

With issue like this.
(Formatted text being displayed formatted instead of as a string)
...It is difficulty to visualize what you are calling a VBcr in terms of; what is happening , what was coded, and what should be happening, and what is actually in the raw text string...

(For example you could use <br> to wrap text in HTML...)

Can you post this raw string, then simply post a graphical example of the *exact* expected output that should result from this string?
jwandmrsquaredAuthor Commented:
Clarification, it is inserting <div> and <\div> before and after the cases that pull other variable results, not just text.  For example, "Jo" is the variable result as is "Frankenstein"...the example:

Jo<div>'s score is </div>45.<br><br><div><u>EYE HEALTH</u></div>
<div>As you know from </div>FrankensteinJo<div>'s eyes,

I need the <div> to not appear. The code is:

Private Sub Create_report_Click()

Dim mycnxn As ADODB.Connection
Dim tstresults As New ADODB.Recordset 'pull patient results
Dim tstblurbs As New ADODB.Recordset 'pull all blurbs
Dim reportID As Integer ' for critera in recordsets
Dim tstresultstxt As String ' for select stmt in results
Dim tstblurbstext As String ' for select stmt in blurbs
Dim tstSEQ As Integer 'for loop from one test to the next
Dim case_number As Integer ' to set case number for typing, will be set through blurb field blurb_type
Dim blurb_seq As Integer ' to set blurb sequence up for loop
'Dim action query recordset statement to write sections to table
Dim section_collector As String ' this will collect the verbiage until time to paste to table
Dim blurb_fieldname As String 'this will be used in the field cases
Dim fvaluetxt As String 'test values with string
Dim fvalueint As Integer 'test values as integer
Dim blurbmatch As Integer 'to contain the blurbmatches optoins for choices
Dim tstendcount As Integer ' this will tell it to stop looping
Dim tstcounter As Integer 'this will be the looper variable
Dim and_counter As Integer 'for statements that might need ands
Dim report_append As String 'to write to reports table
Dim output_dt As Date

Set mycnxn = CurrentProject.Connection
Set tstresults = New ADODB.Recordset
Set tstblurbs = New ADODB.Recordset

'The strategy here is to pull all test results for ONE test series, loop through tests, each test loops through sequence, at the end of each test
'a record will be written to the reports table

tstresults.ActiveConnection = mycnxn
reportID = Me.report_ID

tstresultstxt = "SELECT tblPatientResults.PatientRPDID, tblPatientResults.PatientFirstname, tblPatientResults.ReportDate, tblPatientResults.PatientGender, tblPatientResults.PatientGrde, tblPatientResults.Regarding, tblPatientResults.PatientAge, tblPatientResults.tstevAccomAb, tblPatientResults.tstPerfRL, tblPatientResults.tstBotheyes, tblPatientResults.tstSupdbl, tblPatientResults.tstCond, tblPatientResults.tstDef, tblPatientResults.tstDefcopy, tblPatientResults.tstDefclose, tblPatientResults.tstDeffatig, tblPatientResults.tstDefcomp, tblPatientResults.tstvisfocdiff, tblPatientResults.tst30Beh, tblPatientResults.tstEyehealth, tblPatientResults.tstEyeHealthbox, tblPatientResults.tstEyeHealthdr, tblPatientResults.tstVisAcuitAided, tblPatientResults.tstVisAcuitUnAidDist_RT, tblPatientResults.tstVisAcuitUnAidDist_LT, tblPatientResults.tstVisAcuitAidDist_RT, tblPatientResults.tstVisAcuitAidDist_LT, tblPatientResults.tstVisAcuitUnAidNear_RT," & _
"tblPatientResults.tstVisAcuitUnAidNear_LT, tblPatientResults.tstVisAcuitAidNear_RT, tblPatientResults.tstVisAcuitAidNear_LT , tblPatientResults.tstVisAcuitAdeq, tblPatientResults.tstVisAcuitBlur, tblPatientResults.tstRefractdegree, tblPatientResults.tstRefracthyper, tblPatientResults.tstRefractmyo, tblPatientResults.tstRefractastig, tblPatientResults.tstColor, tblPatientResults.tstOculoeyemove, tblPatientResults.tstOculooversh, tblPatientResults.tstOculoovershdeg, tblPatientResults.tstoculoundersh, tblPatientResults.tstoculoundershdeg, tblPatientResults.tstoculoheadmove, tblPatientResults.tstoculoheadmovedeg, tblPatientResults.tsteyemovesig, tblPatientResults.tsteyemovedescr" & _
" FROM tblPatientResults" & _
" WHERE (((tblPatientResults.PatientRPDID)=" & reportID & "))"

tstresults.Open tstresultstxt
'make sure there isnt' a second somehow

tstblurbs.ActiveConnection = mycnxn

tstblurbstext = "SELECT tbl_patient_tests.TestBox, tblReportBlurbs.TestName, tblReportBlurbs.TestSEQ, tbl_patient_tests.PatientRPDID, tblReportBlurbs.BlurbSequence, tblReportBlurbs.Blurbmatchesoption, tblReportBlurbs.BlurbType, tblReportBlurbs.Result_field_name, tblReportBlurbs.Blurb, tblReportBlurbs.BlurbCAseNumber " & _
"FROM tbl_patient_tests INNER JOIN tblReportBlurbs ON tbl_patient_tests.TESTID = tblReportBlurbs.TestName " & _
"WHERE (((tbl_patient_tests.TestBox)=True) AND ((tbl_patient_tests.PatientRPDID)=" & reportID & ")) " & _
"ORDER BY tblREportBlurbs.TestSEQ, tblReportBlurbs.Blurbsequence"

'sort by test sequence, then blurb_sequence

tstblurbs.Open tstblurbstext

Me.rpt_output = ""

and_counter = 0


case_number = tstblurbs.Fields("BlurbCaseNumber")

blurb_seq = tstblurbs.Fields("Blurbsequence")

If case_number = 7 Then
and_counter = and_counter + 1
and_counter = 0
End If

Select Case case_number

Case 1    'heading
section_collector = section_collector & tstblurbs.Fields("blurb") & vbCr

Case 2      'straight text
section_collector = section_collector & tstblurbs.Fields("blurb")

Case 4 'paragraph insert
section_collector = section_collector & "<br>"

Case 5 'results of field
blurb_fieldname = tstblurbs.Fields("result_field_Name")
fvaluetxt = tstresults.Fields(blurb_fieldname)
section_collector = section_collector & fvaluetxt

Case 6 'choose correct blurb based on blurb-value
blurb_fieldname = tstblurbs.Fields("result_field_Name")
fvalueint = tstresults.Fields(blurb_fieldname) 'will need error trapping for incomplete results
blurbmatch = tstblurbs.Fields("blurbmatchesoption")

If fvalueint = blurbmatch Then
    section_collector = section_collector & tstblurbs.Fields("blurb")
End If

Case 7 'multiple choices that could result in and with commas

blurb_fieldname = tstblurbs.Fields("result_field_Name")
fvaluetxt = tstresults.Fields(blurb_fieldname)

If and_counter > 2 Then
    section_collector = section_collector & ", and"
End If

If fvaluetxt = True Then
    section_collector = section_collector & tstblurbs.Fields("blurb")
End If

End Select

'insert text case with just a period after type 7s in blurbs table

Debug.Print section_collector

Loop Until tstblurbs.EOF

Me.rpt_output = section_collector

output_dt = Now()

'report_append = "INSERT INTO tblreportoutput (uniqu_rpdid, output_date, report_text) " & _
'"SELECT " & reportID &,& output_dt &,& section_collector & "

'DoCmd.RunSQL report_append

'DoCmd.OpenForm "frm_test_output", acNormal

'section_collector = vbNull

End Sub
jwandmrsquaredAuthor Commented:
The results table is one long ugly table with many field names..but each report pulls only one record because the result TYPES are all of the above (string, value, option box, yes/no, date/time, etc) so I use the field name as a connector between the "blurb" table and the results table to pull/choose the right verbiage.  
Jeffrey CoachmanMIS LiasonCommented:
This is why I asked for a sample of:
1. The *exact* string generated
2. The *exact* expected Output to be displayed in the texbox on the report.

Or just post a sample of this DB to avoid typing everything in here...

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, 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.


jwandmrsquaredAuthor Commented:
It's currently in development so no sensitive data.  I will make the fixes and post later this evening.
jwandmrsquaredAuthor Commented:
I removed all objects I thought to be unrelated.  

Simply press the "push me first" button to open the 2 forms that need to be open.

On Create Report, click "preview report".

My issue is that certain blurbs will insert carriage returns where inappropriate and eliminate the ones I included on purpose.

Thanks for any help you can give.
Jeffrey CoachmanMIS LiasonCommented:
Did you compile this DB before posting? (step 9.)
The code fails as soon as I click the "push me first" button.
AFAICT this is because the code does not compile.

Please check your code, fix any errors, compile, and re-post.
I have no errors in your example.
I think your problem is in table tblReportBlurbs.
Blurb field contain html formatting. For example:
<div>'s score is </div>
It will be better to remove this formatting from table and add it in your sub in proper places.
You can see it in immediate window.
You can, of course use replace for removing "<div>", but, I think it not the best way.
Jeffrey CoachmanMIS LiasonCommented:

Odd, as I open this now there are no errors...

I see that you seem to have a good grip on this, so I'll just monitor...

jwandmrsquaredAuthor Commented:

I tried to use code to insert the formatting into the text, but was unsuccessful.  I have no heartburn with removing the rich text and using formatting codes to produce the output for the report.   So if my output text box is rich text, I am assuming that the formatting will be retained.  If that is the case, can you please provide a simple example of say underlining some text as VB code and perhaps a reference website for the other formatting codes (space, etc).  Case 1 in my code is simply to output a header that is underlined.


This is html tags:
Test this sample for your report:
section_collector = "<B> Bold text </B> <br />"
section_collector = section_collector & "<u> Underlined text </u> <br />"
Me.rpt_output = section_collector

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.