Solved when i exported to pdf it adds additional blank pages (1 Viewer)

[camiramzi]

i have a subform that has scanned images (jpg) and also converted pdfs like shown here

when i click to convert again the second/third time i think it includes the pdfs in subform list and adds them as blank pages because when i have like 10 pdfs and one jpg in the list it exports a pdf containing jpg(image) and 10 additional blank pages(previously converted pdfs).
i'm a beginner at access vba please help me with this issue.

this is code :

Private Sub CommandConvertToPdf_Click()

Dim Numberofdocuments As Integer

Numberofdocuments = DCount("IDD", "QPicOfConseller01")
If Numberofdocuments = 0 Then
MsgBox "áÇÊæÌÏ ÕæÑÉ áÊÍæíáåÇ", , "ÕÝÍÉ ãÍãæÏ ÚÈÏ ÇáÛÝÇÑ"
End
Else

    SetPathOfFiles
    NumDoc = Me.IDD
  
    If Len(Dir(PathOfFile & Forms![Form1]![IDD], vbDirectory)) = 0 Then
                MkDir PathOfFile & Forms![Form1]![IDD]
            End If
    DoCmd.OpenReport "ReportToPdf", acViewPreview, , , acHidden
    DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", PathOfFile & NumDoc & "\" & NumDoc & "-" & Format(Now, "d-m-yy_h-n-s") & ".pdf"
    DoCmd.Close acReport, "ReportToPdf", acSaveNo
End If


  'Deletes jpgs after convert
Dim sFile As String
If Me!OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImagesSubform.Form.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If InStrRev(![AttchFiles] & "", ".jpg") <> 0 Then
                sFile = ![AttchFiles]
                .Delete
                On Error Resume Next
                If Len(Dir$(PathOfFile & NumDoc & "\" & sFile)) <> 0 Then
                    Kill PathOfFile & NumDoc & "\" & sFile
                End If
                 On Error GoTo 0
            End If
             .MoveNext
          Loop
    End With

Me.PicView.Requery
End If

Dim Ttb2 As Recordset
        Set Ttb2 = CurrentDb.OpenRecordset("Image")
        Ttb2.AddNew
        Ttb2![IDD] = Forms![Form1]![IDD]
        Ttb2![Path] = PathOfFile & NumDoc & "\" & NumDoc & "-" & Format(Now, "d-m-yy_h-n-s") & ".pdf"
        Ttb2.Update
        Me.ImagesSubform.Requery
End Sub

 

[ebs17]

A report is primarily a printed output on paper. A paper format used has dimensions, the printer used also requires its own minimum dimensions for margins.

So if your content needs more space there, leftovers are spent on additional pages.

 

[camiramzi]

A report is primarily a printed output on paper. A paper format used has dimensions, the printer used also requires its own minimum dimensions for margins.

So if your content needs more space there, leftovers are spent on additional pages.

there is no problem with report layout
it adds the pdf file that is on the list as an additional blank page
i need to find a way to baypass or exclude the pdf when the convertion process is working
i guess the problem is here in this code:

DoCmd.OpenReport "ReportToPdf", acViewPreview, , , acHidden
DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", PathOfFile & NumDoc & "\" & NumDoc & "-" & Format(Now, "d-m-yy_h-n-s") & ".pdf"
DoCmd.Close acReport, "ReportToPdf", acSaveNo

 

[arnelgp]

you post a sample "with some records".

 

[camiramzi]

 

[ebs17]

OpenReport ... does the report have blank pages? Look, don't believe.
PDF is created by a PDF printer, whether you see it or not doesn't matter. This printer may require custom margins.
So reduce the content in the report significantly and test again.

i guess the problem is here in this code

A file name certainly has no bearing on blank pages.

 

[arnelgp]

I don't know if this will fix it.
i added a query qryForReport and use it as Recordsource of report ReportToPdf.
i also add code on the Open event of the report.

 

[ebs17]

when i click to convert again the second/third time i think it includes the pdfs in subform list

What does re-convert mean? Did you scan the PDF? By which? Are PDFs embedded in the report and you want the whole thing to be output as a PDF again?
What do you want your pictures to explain?

i'm a beginner at access vba please help me with this issue.

I think this is less of a VBA or Access problem. Think about processes, what you do with what.

 

[camiramzi]

unfortunately it didn't fix it
thank you

arnelgp & ebs17​

for your time and effort
this is the file in case you want to modify it, if you fix it please send it to me.

 

[camiramzi]

What does re-convert mean? Did you scan the PDF? By which? Are PDFs embedded in the report and you want the whole thing to be output as a PDF again?
What do you want your pictures to explain?

I think this is less of a VBA or Access problem. Think about processes, what you do with what.

i scan paper documents (archive) and export to pdf , my problem is the pdf contains additional blank pages.

 

[bastanu]

Can you please try this version?
I have set all margins to 0 and set the printer to Microsoft Save As PDF (in the report).

Cheers,

 

[ebs17]

DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", ...

What I can see:
=> The resulting JPG files from the scan are not used in any way by this process, they have a life of their own.
=> The report contains only one ImageControl. A PDF is loaded into that (or several?) via query, and a PDF is generated from this report. The ImageControl has smaller dimensions than the A4 format.

I can't see any sense in the measure. If you want to merge PDF files, there are suitable solutions like PDFtk.

 

[camiramzi]

Can you please try this version?
I have set all margins to 0 and set the printer to Microsoft Save As PDF (in the report).

Cheers,

i tried it and it still adds an additional blank page to the second time
maybe it's because it loops through the records and incules the pdf and generates it as a blank page

 

[ebs17]

it loops through the records

Somewhere, but that has nothing, I repeat, nothing to do with the report and the PDF creation. The report has a query as the data source, and in the demo shown, it returns exactly one PDF document and nothing else. Check it out and trade belief for knowledge of reality.

 

[camiramzi]

Somewhere, but that has nothing, I repeat, nothing to do with the report and the PDF creation. The report has a query as the data source, and in the demo shown, it returns exactly one PDF document and nothing else. Check it out and trade belief for knowledge of reality.

is there a way that i can use criteria that excludes the Pdfs and only selects jpgs in this code

DoCmd.OpenReport "ReportToPdf", acViewPreview, , criteria , acHidden
DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", PathOfFile & NumDoc & "\" & NumDoc & "-" & Format(Now, "d-m-yy_h-n-s") & ".pdf"
DoCmd.Close acReport, "ReportToPdf", acSaveNo

 

[bastanu]

Not in there but in the report's record source, replace what you have with this and give it a try:

SELECT Conseller01.IDD, image.Path, IIf(IsNull([Path]),"N\A",Right([Path],4)) AS FileType
FROM Conseller01 INNER JOIN [image] ON Conseller01.IDD = image.IDD
WHERE (((Conseller01.IDD)=[Formulaires]![Form1]![IDD]) AND ((IIf(IsNull([Path]),"N\A",Right([Path],3))="jpg"));

Cheers,

 

[ebs17]

is there a way

SELECT Conseller01.IDD, image.Path
FROM Conseller01 INNER JOIN [image] ON Conseller01.IDD = image.IDD
WHERE (((Conseller01.IDD)=[Formulaires]![Form1]![IDD]));

This query provides the data for the report - as you know and can see in your application. The images table provides full paths for files.
=> So you could filter the query for the JPG file extension.
=> You could also simply not enter a PDF in the table. A PDF is not an image.

If you edit an application (others?), you should first deal with it a little bit before you get hands on.

 

[camiramzi]

Not in there but in the report's record source, replace what you have with this and give it a try:

SELECT Conseller01.IDD, image.Path, IIf(IsNull([Path]),"N\A",Right([Path],4)) AS FileType
FROM Conseller01 INNER JOIN [image] ON Conseller01.IDD = image.IDD
WHERE (((Conseller01.IDD)=[Formulaires]![Form1]![IDD]) AND ((IIf(IsNull([Path]),"N\A",Right([Path],3))="jpg"));

Cheers,

It works a million thanks to you

bastanu and ebs17​

 

[arnelgp]

you also change this portion and you delete the File in the [Path] field:

If Me!OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImagesSubform.Form.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            If InStrRev(![Path] & "", ".jpg") <> 0 Then
                sFile = ![Path]
                .Delete
                On Error Resume Next
                If Len(Dir$(sFile)) <> 0 Then
                    Kill sFile
                End If
                 On Error GoTo 0
            End If
             .MoveNext
          Loop
    End With

Me.PicView.Requery
End If

Dim Ttb2 As Recordset
        Set Ttb2 = Me!ImagesSubform.Form.RecordsetClone 'CurrentDb.OpenRecordset("Image")
        Ttb2.AddNew
        Ttb2![IDD] = NumDoc
        Ttb2![Path] = PathOfFile & NumDoc & "\" & NumDoc & "-" & Format(Now, "d-m-yy_h-n-s") & ".pdf"
        Ttb2.Update
        Me.ImagesSubform.Requery

 

[camiramzi]

i did that
Thank you for your help arnelgp