Solved

Using a variable to compile to a rich text report

Posted on 2011-09-06
16
392 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:jwandmrsquared
  • 6
  • 5
  • 3
  • +2
16 Comments
 
LVL 39

Expert Comment

by:als315
ID: 36493467
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36494238
Are you expecting to be able to export the html formatted document ito Word and retain the formatting?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 36495946

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

JeffCoachman
0
 
LVL 21
ID: 36497784
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.
0
 

Author Comment

by:jwandmrsquared
ID: 36497900
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36497988
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?
0
 

Author Comment

by:jwandmrsquared
ID: 36498044
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
tstresults.MoveFirst
'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 = ""

tstblurbs.MoveFirst
and_counter = 0

Do



case_number = tstblurbs.Fields("BlurbCaseNumber")

blurb_seq = tstblurbs.Fields("Blurbsequence")

If case_number = 7 Then
and_counter = and_counter + 1
Else
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
tstblurbs.MoveNext

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
0
 

Author Comment

by:jwandmrsquared
ID: 36498060
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.  
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36498287
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, ...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

0
 

Author Comment

by:jwandmrsquared
ID: 36499102
It's currently in development so no sensitive data.  I will make the fixes and post later this evening.
0
 

Author Comment

by:jwandmrsquared
ID: 36526029
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.
EE-question.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36527003
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.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 333 total points
ID: 36527443
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36529670
als315,

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

Jeff
0
 

Author Comment

by:jwandmrsquared
ID: 36529753
als315,

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.

Thanks!!

0
 
LVL 39

Assisted Solution

by:als315
als315 earned 333 total points
ID: 36530952
This is html tags:
http://www.w3schools.com/tags/default.asp
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

0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

18 Experts available now in Live!

Get 1:1 Help Now