Generating and Export pdf files keep overwriting existing files

[aharleston]

I am generating multiple pdfs from MS Access report, but files keep overwriting each other and the code seems to run forever. Please help. See Code below:

Option Compare Database

Private Sub Command0_Click()



    Dim rs                    As DAO.Recordset
    Dim sFolder               As String
    Dim sFile                 As String


    On Error GoTo Error_Handler


    sFolder = "D:\Journal\"


    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryAllRecords", dbOpenSnapshot)
 
    With rs
        .MoveFirst
        Do While Not .EOF
            DoCmd.OpenReport "rptPreJournal", acViewPreview, , "[NRegistrationCentreid]=" & ![NRegistrationCentreid], acHidden
            sFile = Nz(![District], "") & " - " & Nz(![NRegistrationCentreid], "") & ".pdf"
            sFile = sFolder & sFile
            DoCmd.OutputTo acOutputReport, "rptPreJournal", acFormatPDF, sFile
            'If you wanted to create an e-mail and include an individual report, you would do so now
            DoCmd.Close acReport, "rptPreJournal"
            .MoveNext
        Loop
    End With


    'Application.FollowHyperlink sFolder    'Optional / Open the folder housing the files


Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Exit Sub


Error_Handler:
    If Err.Number <> 2501 Then    'Let's ignore user cancellation of this action!
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_GenPDFs_Click" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Sub

[NeoPa]

It's never fun dealing with questions of the form :
"Here's all my code & it doesn't work. Can someone fix it for me."

Nevertheless, if you look at line #26 and go backwards from there to where sFile is managed you'll see that it depends on whatever's in ![NRegistrationCe ntreid]. This should give you something to start from. We can't really help as you didn't get as far as explaining what actually happens or whether this should change between iterations of the loop. Or anything much really.

[aharleston]

multiple files are generated at the change of every centre id. the problem is, a file should not be generated again, overwriting the original file. so the loop seems to go on forever.

[NeoPa]

I'm not sure you really appreciate what I'm saying here. Asking a question takes at least a bit of work on your part. Simply dropping in your code and a small hint at the problem doesn't do it. Every time I look at your question and get blocked because you have put so little effort into asking it that I have to do extra work in order to try to get past that then I get annoyed. It happened the first time. It's happened again. Feel free to rely on everyone else doing what you should take responsibility for if you like. Your call obviously.

NB. There are site rules that stipulate how a question should be asked. This does not fit within them. We make allowances for those new to the site - but not forever. If you don't respond positively when told then I doubt you'll have a very positive experience of being a member here.