Send a formatted and complete query to PDF

[Chitoparadais]

Hello!
This is the first thread that I open.
I have a database (Access) from which I am doing, very often, a PDF report of the activity of each of the staff members (there are constant changes).
I publish that report in PDF in the cloud (Google Drive).
The uncomfortable thing is that I have to do several steps to generate the report:
1. First I export the reports to .RTF
2. Later, I open them in Word, to give it a better format than Access offers, through a macro.

I would like to find a way to do it directly from Access.
Searching a lot, I have found a way:
1. Create a temporary file (HTML), which I format;
2. Add the HTML text to a Word document;
3. Save it, with a dialog box, in pdf.

The problem I have is that:
1. It only includes the first recordset of the query.
2. There is an error in the way to save it directly to PDF.

I would really appreciate if you can help me.

I attach the code that I am working on.

 

[theDBguy]

Hi. I haven't looked at your file, but are you saying you can't create the Report in Access to look exactly how you like it, so you have to use Word to do it?

 

[Gasman]

Please post your code between code tags, not as a pdf.
That just locked my computer up big time for some strange reason.

 

[Pat Hartman]

Please post a copy of the word document before and after your modifications. Exporting to pdf is trivial if the report is created by Access.

 

[Cronk]

@Chitoparadais
Your problem with only having the first record is that you are not looping through the recordset.

Your code should include a loop such as

  Set rst = CurrentDb.OpenRecordset("Query")
    If rst.RecordCount > 0 Then
    ....
     rst.movefirst
    do while not rst.eof
       myHtml = myHtml & "<p class=MsoNormal style='margin-top:4.05p.....
       rst.movenext
    loop
    rst.close
    '--save file etc
   Endif

 

[Chitoparadais]

Hi. I haven't looked at your file, but are you saying you can't create the Report in Access to look exactly how you like it, so you have to use Word to do it?

This is how you say. The report in Access contains the information on the activities to be carried out by the staff (start time, end time, place, activity, and other characteristics). To give more clarity and order to the presentation (for example: I want to remove the line breaks that sometimes appear), I export it to rtf, and then, with a macro in word, I give it the format I want and save it in pdf .
I want to "skip" the step of using word, and do it directly from Access.

 

[theDBguy]

This is how you say. The report in Access contains the information on the activities to be carried out by the staff (start time, end time, place, activity, and other characteristics). To give more clarity and order to the presentation (for example: I want to remove the line breaks that sometimes appear), I export it to rtf, and then, with a macro in word, I give it the format I want and save it in pdf .
I want to "skip" the step of using word, and do it directly from Access.

Hi. Thanks for trying to explain, but it doesn't really answer my question. For example, why does the report sometimes get line breaks? If you can take them out using Word, why can't you take them out using Access?

I know you want to skip using Word, and that's exactly what I don't understand. Why do you even need to use Word. As Pat requested, maybe consider posting what the report looks like and what the Word result looks like to help better understand your situation. For example, is the page layout the same between the two? Are you adjusting the margins? What else?

 

[Chitoparadais]

Please post your code between code tags, not as a pdf.
That just locked my computer up big time for some strange reason.

I apologize, because when I want to upload the code between code tags, it gives me a problem.
If you have no problem, I have uploaded it in txt.

 

[Chitoparadais]

@Chitoparadais
Your problem with only having the first record is that you are not looping through the recordset.

Your code should include a loop such as

  Set rst = CurrentDb.OpenRecordset("Query")
    If rst.RecordCount > 0 Then
    ....
     rst.movefirst
    do while not rst.eof
       myHtml = myHtml & "<p class=MsoNormal style='margin-top:4.05p.....
       rst.movenext
    loop
    rst.close
    '--save file etc
   Endif

Thanks a lot!
I am going to try.
Also, what is not working for me is saving it in PDF.
Do you know what my error is in the code?

 

[Chitoparadais]

Please post a copy of the word document before and after your modifications. Exporting to pdf is trivial if the report is created by Access.

Thanks for answering. Give me time, and I'll post it.

 

[Chitoparadais]

Hi. Thanks for trying to explain, but it doesn't really answer my question. For example, why does the report sometimes get line breaks? If you can take them out using Word, why can't you take them out using Access?

I know you want to skip using Word, and that's exactly what I don't understand. Why do you even need to use Word. As Pat requested, maybe consider posting what the report looks like and what the Word result looks like to help better understand your situation. For example, is the page layout the same between the two? Are you adjusting the margins? What else?

Thanks for answering.
I think the answer to your questions is that I don't know how to give it the format I want from Access (sorry, but I have been learning to use Access with a bang).
For example, I don't know why in the reports it breaks lines, when they exceed a number of characters.
Anyway, I'll check the reports in Access, to see if I can fix them (margins)
Give me time, and I'll post it, to show.

 

[theDBguy]

Thanks for answering.
I think the answer to your questions is that I don't know how to give it the format I want from Access (sorry, but I have been learning to use Access with a bang).
For example, I don't know why in the reports it breaks lines, when they exceed a number of characters.
Anyway, I'll check the reports in Access, to see if I can fix them (margins)
Give me time, and I'll post it, to show.

Okay, if you can post the files @Pat Hartman requested, we might be able to help you find out why your Access reports are not looking/coming out as you would prefer. Cheers!

 

[Chitoparadais]

Please post a copy of the word document before and after your modifications. Exporting to pdf is trivial if the report is created by Access.

Hello!
Thank you all very much for your follow-up.
Here I put the copy of the word (rtf) document before and after your modifications.
I have added an image of the report design view in Access.
I have put some explanations (hopefully they help!) Of the changes that I make in word and cannot make in Access.
I just add one more thing: as seen in the Access report design view, the line where the information goes (the one that is not the date) is a field in which I have joined several fields:

(ActSacdObs: (Format([HINI],"Short time")) & " - " & (Format([HFIN],"Short time")) & " | " & [Namectr] & ", " & [ACTIV] & " " & [WHO] & " (" & [ObsSacd] & ")" & [Camb])

 

[Pat Hartman]

If what you want is an empty box with a yellow background to the left of each clause, you can do that with the report layout. Just add a a label control to the left of the text box. Since the label needs text, make the text color the same as the background color so you don't actually see the text. I don't understand the transition of the numbers such as 21:00 - 21:00, 00:00 - 00:00, 07:14 - 15:15, to blanks but maybe those are what your macro is using.

 

[Chitoparadais]

If what you want is an empty box with a yellow background to the left of each clause, you can do that with the report layout. Just add a a label control to the left of the text box. Since the label needs text, make the text color the same as the background color so you don't actually see the text. I don't understand the transition of the numbers such as 21:00 - 21:00, 00:00 - 00:00, 07:14 - 15:15, to blanks but maybe those are what your macro is using.

Thank you very much Pat. What you tell me would be useful for some cases (in which I would like it to be blank), but not for all.
The problem with the hours is because of the data table: I have a column with the "start time", and another with the "duration of the event".
The times that I present in the report are the "start time" - "end time", where the end time is the sum of "start time" and "event duration".
Sometimes, I have to put false hours, which I end up fixing with the word macro.
I am very thankfull for your help.

Meanwhile I have continued struggling to find a way to run a macro in Word from Access, and I have succeeded !!!
I put it in the following reply.

 

[arnelgp]

one comment on the Code you post.
you Declared lngRecordID as Long, but you Never assign value to it.
therefore, the default value of lngRecordID is 0.
then you use it here


strSQL = "SELECT * FROM tblTestData WHERE LineID=" & lngRecordID

 

[Chitoparadais]

I want to thank everyone for their interest in helping me.
As I was telling Pat, I have continued struggling and I have managed to make the code in Access to export a report to RTF, and have a macro in word format and save it in PDF.
With this, I consider this thread closed.
Below I show the code I made (I know I have discovered "drinking water", but I admit that these "victories" excite me.
I just add that I ask you to accept prayers in exchange for your help and interest (praying is easier for me than programming).

Function ExportToPDF()

    Dim LWordDoc As String  'viejo
    Dim oApp As Object
    Dim MacroName As String

    DoCmd.OutputTo acOutputReport, "REPORTname", "RichTextFormat(*.rtf)", "PATH\REPORTname.rtf", False, "", , acExportQualityScreen

'Path to the word document
    LWordDoc = "PATH\REPORTname.rtf"

    If Dir(LWordDoc) = "" Then
        MsgBox "Document not found."
    Else
'Create an instance of MS Word
        Set oApp = CreateObject("Word.Application")
        oApp.Visible = True
'Open the Document
        oApp.Documents.Open FileName:=LWordDoc
    End If

    oApp.Application.Run MacroName:="WORDMACROname"  ‘the macro is in the Normal template
    
    ActiveDocument.ExportAsFixedFormat OutputFileName:= _
            "PATH\PDFFILEname.pdf", ExportFormat:= _
            wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
            wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
            Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
            CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
            BitmapMissingFonts:=True, UseISO19005_1:=False

    oApp.ActiveDocument.Close
    oApp.Quit wdDoNotSaveChanges
    Set oApp = Nothing
    Kill " PATH\REPORTname.rtf"

    MsgBox "I have already exported the report to PDF."

End Function

 

[Chitoparadais]

one comment on the Code you post.
you Declared lngRecordID as Long, but you Never assign value to it.
therefore, the default value of lngRecordID is 0.
then you use it here


strSQL = "SELECT * FROM tblTestData WHERE LineID=" & lngRecordID

Thanks a lot!
You're right.
I copied it from other code.
But it strikes me that it doesn't get in the way, because it runs well.

 

[Chitoparadais]

@Chitoparadais
Your problem with only having the first record is that you are not looping through the recordset.

Your code should include a loop such as

  Set rst = CurrentDb.OpenRecordset("Query")
    If rst.RecordCount > 0 Then
    ....
     rst.movefirst
    do while not rst.eof
       myHtml = myHtml & "<p class=MsoNormal style='margin-top:4.05p.....
       rst.movenext
    loop
    rst.close
    '--save file etc
   Endif

Thank you very much for this loop that you sent me. It worked perfectly!