Button to output database entries to separate pdfs

[pyre]

Hello, I hope that someone will be able to help me overcome this snag I have run into creating this access 2010 database. I have a database that users will input information into (IDs, names, etc) via form that I will need to retrieve. The basic purpose of this database is to replace users having to send me physical forms when requesting system access. Once they have filled out the form and submit, I have a report that lists all of the users who have done so along with some basic information. On this report there is a button that, when clicked, I need to take each of the entries and output each one to a separate pdf of a full report I have created and deleted them out of the database. The code that I have for this is as follows:

Private Sub OutputPdf_Click()

for i = 1 To DCount("ID", "database")
If Not DCount("ID", "database") = 0 Then

‘opens the full report and limits it to one entry of database
DoCmd.OpenReport "FullReport", acViewPreview, , "ID = '" & ID & "'"
‘outputs full report to pdf on HDD with file name based upon fields in table
DoCmd.OutputTo acOutputReport, "FullReport", acFormatPDF, "C:\...\Desktop\" & LegalName  & " - " & [ID] & ".pdf"
DoCmd.SetWarnings off
‘deletes item from table
DoCmd.RunSQL "DELETE * FROM database WHERE ID = '" & ID & "'"
‘closes full report
DoCmd.Close acReport, "FullReport", acSaveNo
‘closes and opens list report to refresh 
DoCmd.Close acReport, "ListReport", acSaveNo
DoCmd.OpenReport "ListReport", acViewReport, , , acWindowNormal

End If
Next i

End Sub

If I run this code a single time it works perfectly fine. The problem occrurs when it tries to recurs back up to the DoCmd.OpenReport "FullReport", acViewPreview, , "ID = '" & ID & "'". This particular version of the code uses a ‘for’ loop, but I have tried it with a ‘while/until’ loop as well and get the same result. I have also tried not having it close and open the list report and use DoCmd.GoToRecord , , acNext, but all that seemed to accomplish was getting of the same full report and it would only delete the first entry. Any help with this would be greatly appreciated.

 

[sxschech]

Maybe missing something, do you need to open the report in preview as well as send to pdf or could you skip/comment out the report opening part and see if that helps?

 

[pyre]

Yes, I believe so. If I don't it outputs a pdf with all of the data entries instead of just one.

 

[sxschech]

Could you create a recordset and refer to the id in the recordsource of the report property and also use the recordset to loop through it that way?

 

[MazeWorX]

what is the data source for the users to be added/removed? (table name)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Maybe this is what you want. I'm not sure why you open and close a report continually. Also "database" is a terrible name for any object in a db. It is a reserved word and should be changed, I have substituted it below for tblMyTable. Just an additional note you used 'DoCmd.SetWarnings off' which only accepts true or false but you need to remember to turn it back on when your done

Private Sub OutputPdf_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

   On Error GoTo OutputPdf_Click_Error

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblMyTable", dbOpenDynaset)

    rs.MoveLast
    rs.MoveFirst
    With rs
        DoCmd.OpenReport "FullReport", acViewPreview, , "ID =" & !ID
        DoCmd.OutputTo acOutputReport, "FullReport", acFormatPDF, "C:\...\Desktop\" & LegalName & " - " & !ID & ".pdf"
        DoCmd.Close acReport, "FullReport", acSaveNo
        .Delete
        .MoveNext
    Loop

   On Error GoTo 0
   Exit Sub

OutputPdf_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OutputPdf_Click of Module Module4"
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[pyre]

I had the list table open and close because, at least when i was trying to get the operations to occur on a single pass it wouldn't update the table unless i did so. If I tried clicking the button again it would say that the record was deleted until i refreshed it. Also, 'database' is just what I called it here to make it a bit more clear as to what it was. It's actually called AccessFormTable, so no worries about using a reserved name. I tried the code you supplied and it seems to be missing something. It tells me that it needs a 'do' for the 'loop'.

 

[pyre]

I updated it so that it has the parts in red and that let it complete the cycle but but now it gives me multiple pfds named the same thing but only one of them actually has any data. However, now it actually succeeds in deleting all of the entries instead of just the first one.

    rs.MoveLast
    rs.MoveFirst
    With rs
      [COLOR=red]Do until .eof[/color red]
        DoCmd.OpenReport "FullReport", acViewPreview, , "ID =" & !ID
        DoCmd.OutputTo acOutputReport, "FullReport", acFormatPDF, "C:\...\Desktop\" & LegalName & " - " & !ID & ".pdf"
        DoCmd.Close acReport, "FullReport", acSaveNo
        .Delete
        .MoveNext
    Loop
   [COLOR=red] end with[/color red]
   On Error GoTo 0
   Exit Sub

 

[pyre]

Got it! Apparently my issue was that I was referring back to the fields in the report I was pulling instead of the table, so once I fixed that everything happens perfectly. Thanks for all your help.

 

[MazeWorX]

Sorry i missed the error before i posted it and was looking at my code the next day , found the error, but couldnt find this post to correct it lol

Glad it worked out

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>