INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Button to output database entries to separate pdfs

Button to output database entries to separate pdfs

(OP)
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:

CODE

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 [i] of the same full report and it would only delete the first entry. Any help with this would be greatly appreciated.
 

RE: Button to output database entries to separate pdfs

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?

RE: Button to output database entries to separate pdfs

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

RE: Button to output database entries to separate pdfs

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?

RE: Button to output database entries to separate pdfs

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>>

RE: Button to output database entries to separate pdfs

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

CODE

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>>

RE: Button to output database entries to separate pdfs

(OP)
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'.

RE: Button to output database entries to separate pdfs

(OP)
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.

CODE

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

RE: Button to output database entries to separate pdfs

(OP)
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.

RE: Button to output database entries to separate pdfs

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>>

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close