Solved problem with exporting to pdf (1 Viewer)

[camiramzi]

i have this application that scans and attaches documents

when i click convert to pdf button it exports the pdf file in the same directory with jpgs as shown in the image above and they appear in the subform list , but when i scan the second time and then click to convert to pdf the function includes the exported pdf files and deletes the previously converted pdf and replaces it with a pdf contains a blank first page and i want it to only select the jpg files

this is my code:

Private Sub CommandConvertToPdf_Click()



Dim Numberofdocuments As Integer

Numberofdocuments = DCount("IDD", "QPicOfIn")

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 & ".pdf"

    DoCmd.Close acReport, "ReportToPdf", acSaveNo

End If





' this removes the converted images

        If Me.OptionDeletePicAfterConvertPDF = -1 Then

                DoCmd.GoToControl "ImagesSubform"

                DoCmd.RunCommand acCmdSelectAllRecords

        DoCmd.SetWarnings False

                DoCmd.RunCommand acCmdDelete

        DoCmd.SetWarnings True

        Me.PicView.Requery

        End If



Dim Ttb2 As Recordset

        Set Ttb2 = CurrentDb.OpenRecordset("Images")

        Ttb2.AddNew

        Ttb2![IDD] = Forms![Form1]![IDD]

        Ttb2![Path] = PathOfFile & NumDoc & "\" & NumDoc & ".pdf"

        Ttb2.Update

        Me.ImagesSubform.Requery

End Sub

i want to function to convert the jpg only and the second part of the function to delete only the jpgs and leave only the PDF files in the subform.
please help me.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[The_Doc_Man]

OK, you are not explicitly deleting the files, which suggests that your files are being overwritten. Which THEN leads to the idea that your problem is in this line, because it is the only line capable of overwriting files:

DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", PathOfFile & NumDoc & "\" & NumDoc & ".pdf"

The best way to figure out what is happening is to put a breakpoint on this line to see what is in each of the variables. The breakpoint stops before executing the break instruction, so you can see what path, document number combination you are attempting. I suspect that something is going on here that causes the overwrite to occur. You probably DON'T care about the first file it writes - but you really DO care about what it is going to do for the second and any subsequent file because that is when an overwrite could occur.

 

[camiramzi]

OK, you are not explicitly deleting the files, which suggests that your files are being overwritten. Which THEN leads to the idea that your problem is in this line, because it is the only line capable of overwriting files:

DoCmd.OutputTo acReport, "ReportToPdf", "PDFFormat(*.pdf)", PathOfFile & NumDoc & "\" & NumDoc & ".pdf"

The best way to figure out what is happening is to put a breakpoint on this line to see what is in each of the variables. The breakpoint stops before executing the break instruction, so you can see what path, document number combination you are attempting. I suspect that something is going on here that causes the overwrite to occur. You probably DON'T care about the first file it writes - but you really DO care about what it is going to do for the second and any subsequent file because that is when an overwrite could occur.

thank you
i did what you told me and i think it needs some kind of criteria in this line

DoCmd.OpenReport "ReportToPdf", acViewPreview, , , acHidden

or here


If Me.OptionDeletePicAfterConvertPDF = -1 Then
DoCmd.GoToControl "ImagesSubform"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDelete
DoCmd.SetWarnings True
Me.PicView.Requery
End If

if you can help me i can send you the access file.

 

[arnelgp]

there is already a code for the Option Button to delete it, you just need to modify it:

' this removes the converted images
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.Form.RecordsetClone
        If Not (.Bof And .Eof) Then
            .MoveFirst
        End If
        Do Until .Eof
            If InstrRev(![AttchFiles] & "", ".jpg") <> 0 Then
                .Delete
            End If
             .MoveNext
          Loop
    End With
End If
Me.PicView.Requery
End If

 

[camiramzi]

there is already a code for the Option Button to delete it, you just need to modify it:

' this removes the converted images
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.Form.RecordsetClone
        If Not (.Bof And .Eof) Then
            .MoveFirst
        End If
        Do Until .Eof
            If InstrRev(![AttchFiles] & "", ".jpg") <> 0 Then
                .Delete
            End If
             .MoveNext
          Loop
    End With
End If
Me.PicView.Requery
End If

it works great deleting the jpgs on the subform list but doesn't delete the images from the drive

 

[arnelgp]

we delete it from your drive:

' this removes the converted images
dim sFile As String
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.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
                Kill PathOfFile & NumDoc & "\" sFile
                 On Error Goto 0
            End If
             .MoveNext
          Loop
    End With
End If
Me.PicView.Requery
End If

 

[The_Doc_Man]

I'm going to move this to the VBA thread, as that seems more appropriate for the subject matter.

 

[camiramzi]

we delete it from your drive:

' this removes the converted images
dim sFile As String
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.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
                Kill PathOfFile & NumDoc & "\" sFile
                 On Error Goto 0
            End If
             .MoveNext
          Loop
    End With
End If
Me.PicView.Requery
End If

i get an error here i tried everything
Kill PathOfFile & NumDoc & "\" sFile
i would appreciate if you take a look at the file and maybe help with problem.

 

[arnelgp]

you test if the jpg file still exists:

' this removes the converted images
dim sFile As String
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.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
End If
Me.PicView.Requery
End If

 

[camiramzi]

you test if the jpg file still exists:

' this removes the converted images
dim sFile As String
If Me.OptionDeletePicAfterConvertPDF = -1 Then
    With Me!ImageSubForm.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
End If
Me.PicView.Requery
End If

it works perfectly thank you very very much
one last thing if you could help me with
when i click to convert the second or third time the output pdf file contains a second blank page , i think because it adds the previously converted pdf
this is the code

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

 

[arnelgp]

first test your report.
view it in Layout view.
you will see "dash" lines on top, left, right.
this line is your Page boundaries (page limit, excluding the Margin).
try to put every textboxs, labels or controls on your report "Inside" the borders.

if your textbox/controls "cross" this line, it means it will going to print a blank page.

 

[camiramzi]

first test your report.
view it in Layout view.
you will see "dash" lines on top, left, right.
this line is your Page boundaries (page limit, excluding the Margin).
try to put every textboxs, labels or controls on your report "Inside" the borders.

if your textbox/controls "cross" this line, it means it will going to print a blank page.

the problem is not in the layout
i think it includes the pdf 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).