Open separate reports for each record in a continuous form (1 Viewer)

[chadbrewyet]

New to the site. Hopefully this is in the right forum.

I have a continuous form that displays a list of invoices (frmInvoiceList) based on an adjustable filter contained within the form. I have the filters working the way that I want them through VBa and I have a button on each record to open a report (rptInvoice). Inside that report, I have some controls to "print", "email", and "export to PDF". Again all that works just the way I want. The Form and the Report are based on a different query and each has an InvoiceID field to link.

What I am trying to do is put the Print, Email, and Export buttons on the main form which would perform the appropriate action for all the records that are displayed on the form. I've been able to get the Print function to work to a degree. It will print all the records, but it changes the background colors based on the alternating records. When I go to Email or Export, it creates a single file with multiple pages and each page is a new record, again with the alternating background colors.

Ideally, I would like to have a separate file exported for each record that can be renamed and archived separately. I'm not so concerned with the email function but if it would be possible to generate a separate report for each invoice, then pull the appropriate email address for the record, that would be really nice.

I've tried some "for" and "do" loops that I found through some Googling but none of the samples ended up working like I wanted.

Any help would be greatly appreciated.

 

[GinaWhipp]

Welcome to the Forum!

I'm not quite clear on what you want, so hoping this will help...
http://www.access-diva.com/vba3.html

 

[chadbrewyet]

Sorry if my OP wasn't clear. I'll try to explain better.

Continuous form has 3 command buttons, print, export, email.

Print button
- when clicked, want to open a separate report for each record
- the opened reports do not have to be visible
- each individual report needs to be printed to the default printer

Export button
- when clicked, want to open a separate report for each record
- each report should be saved as individual files
- file name should include reference to the InvoiceID

Email button (again, not as important. Just a wish)
- when clicked, want to open a separate report for each record
-the exported file should be attached to an email for sending using the default mail client
- the exported file name should reference the InvoiceID
- the To: email address should be inserted automatically based on the customer file (email address is part of the report query)
- User must click Send to allow them to review the file before sending



I tried modifying the code from the link but I get a "compile error: Method or Data Member not found" error on the .ListCount.

Dim intCounter As Integer
Dim cboCode As TextBox

Set cboCode = Me![InvoiceID]

For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport "InvoiceForCustomer", acViewPreview, , _
"[InvoiceID] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
DoCmd.OutputTo acReport, "rptInvoice", acFormatRTF, "C:/images/invoice " & Format(intCounter, "000") & ".rtf"
DoCmd.Close acReport, "rptInvoice"
Next

 

[chadbrewyet]

Here is another code snipet I found and it exports the file but it only exports 1 file for 1 record. The remaining files seem to not be there even though it appears to open a report for all the records.

Set rstSubForm = Me.Form.RecordsetClone

Do While Not rstSubForm.EOF
    Dim strFileName As String
    Dim strPath As String
    strPath = "C:/export/"
    strFileName = Me.CustomerName & " - " & Me.InvoiceID & ".pdf"
    DoCmd.OpenReport "rptInvoice", acViewPreview, , "[InvoiceID]=" & [InvoiceID], , InvoiceID.value
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, strPath & strFileName & ".pdf", False
    DoCmd.Close acReport, "rptInvoice"
    
    rstSubForm.MoveNext
Loop

 

[GinaWhipp]

Are these buttons in the Header of the Continuous Form?

 

[chadbrewyet]

Yes, the buttons I want to have working are in the header of the form.

There are buttons to print/export each individual record in the detail section and those work fine. It's only the buttons in the header that do not.


I made a few changes to try to troubleshoot and it appears that it is not looping through the recordset. If the form is filtered to 3 records, it will open 3 separate reports but each report is for the first record on the form.

 

[GinaWhipp]

Hmm, this should get you started...

http://www.utteraccess.com/forum/Access-Report-Individual-t2021171.html

 

[JHB]

..
I made a few changes to try to troubleshoot and it appears that it is not looping through the recordset.

I think you are missing the reference to the recordset.

DoCmd.OpenReport "rptInvoice", acViewPreview, , "[InvoiceID]=" & rstSubForm![InvoiceID], , rstSubForm![InvoiceID]

 

[vbaInet]

Just another way (i.e. 1 of 2 ways) that doesn't require you to open the report. It can be output without requiring the OpenReport command (post #10):

http://www.access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto

 

[chadbrewyet]

Thanks everyone for the help. With your links and suggestions I was able to come across a code that finally worked. Not sure what I was really missing initially but it works now and that all that matters I guess. If there are any "best practices" that I should be using instead of what's here, I'm open to suggestions.

Here's what ended up working-

Automatically prints filtered records to machine's default printer:

Private Sub Command292_Click()
Dim rs As DAO.Recordset
Set rs = Me.Recordset

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        DoCmd.OpenReport "rptInvoice", acViewNormal, , "[InvoiceID]=" & [InvoiceID], , InvoiceID.value
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records to print."
End If

End Sub

Exports filtered records to a default location and opens them for review:

Private Sub Command293_Click()
Dim rs As DAO.Recordset
Set rs = Me.Recordset

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 
    Do Until rs.EOF = True
        DoCmd.OpenReport "rptInvoice", acViewPreview, , "[InvoiceID]=" & [InvoiceID], , InvoiceID.value
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, "C:\export\" & Me.CustomerName & " - " & Me.InvoiceID & ".pdf", True
        DoCmd.Close acReport, "rptInvoice"
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records to export."
End If

End Sub

Converts filtered records to a PDF, attaches it to an email, and opens the send email dialog (default mail client) before sending:

Private Sub Command294_Click()
Dim rs As DAO.Recordset
Set rs = Me.Recordset

On Error Resume Next

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        DoCmd.OpenReport "rptInvoice", acViewPreview, , "[InvoiceID]=" & [InvoiceID], acHidden, InvoiceID.value
        Reports(InvoiceForCustomer).Caption = "Invoice - " & Me.InvoiceID
        DoCmd.SendObject acSendReport, "", acFormatPDF, Me.CustomerEmail, , , "Invoice - " & Me.InvoiceID, "Thank you for your business.  Attached, you will find an invoice for your review.", True
        DoCmd.Close acReport, "rptInvoice"
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records to send."
End If

End Sub

 

[vbaInet]

Not sure what I was really missing initially but it works now and that all that matters I guess. If there are any "best practices" that I should be using instead of what's here, I'm open to suggestions.

Yes, for starters your naming convention, Command29X doesn't mean anything.

But going back to your code there are a lot of unnecessary steps involved.

1. "Automatically prints filtered records to machine's default printer:" - I don't see any Print code and you should open the report once and use the Filter and FilterOn properties of the report to change the criteria.

2. "Exports filtered records to a default location and opens them for review:" - OpenReport and Close not necessary. The link I posted (post #10) explains how to do it with only OutputTo command. I'm sure Gina's link also expresses the same view.

3. "Converts filtered records to a PDF, attaches it to an email, and opens the send email dialog (default mail client) before sending:" - this time OpenReport, Caption, SendObject and Close are not necessary. Again the steps explained in the link I posted should give you some ideas on working around all this. For starters the OutputTo command saves to PDF.