Create multiple reports 1

[TrekBiker]

I currently have this selector form for customers with unpaid invoices.

This allows a report to be generated based on the selected CustomerID, then emailed to the customer using this, which works a treat.

Private Sub cmdStore_Click()
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem  
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"   
   'Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatement-copy", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)    
    With oEmailItem
        .To = Me.sfmDebtorList.Form.[E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath

End Sub

I now want to be able to be able to send reports to any customers with ticks in 'Requested Email', all in one operation.

 

[Andrzejek]

You selected 2 Customers. Do you create 1 report for both? And you want to e-mail this 1 report to both Customers?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Each customer gets his own report, so in this case two separate reports to be generated, one to each customer.
I experimented with a new query selecting the full records for each ticked customer but couldn't get the code right. I'm not that familiar with recordsets, if they're the route to take.

 

[Andrzejek]

First, Angel Inn (CustomerID 17832827) does not have any e-mail, to whom are you going to send the e-mail when they are selected?

Is 'Requested Email' (True/False) a field in your table where you mark which Customer(s) to process? Is that what your [tt]rptDebtorsStatement-copy[/tt] uses to create your PDF report?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Yes, not all customers have given their email addresses, and not all who have done so want their reports by email, preferring them to be sent.
So the Requested Email field identifies who to email reports to. The others will be handled separately.
And yes, rptDebtorsStatement-copy will contain all the relevant information for each report, fed from a source query with criterion 'yes' for Requested Email.
What's in the subform I sent is just a lookup for all customers owing money. I'd added email address for the easier case of picking just one customer, going into the code line .To = Me.sfmDebtorList.Form.[E-Mail]

 

[Andrzejek]

The way I understand it - your [tt]rptDebtorsStatement-copy[/tt] will process one, two, or 100 Customers (as long as they are marked with 'Yes' in 'Requested Email' field) in one PDF report. Is that correct?

If so, do you have a version of your [tt]rptDebtorsStatement[/tt] report that will process just one customer if you pass its CustomerID?

That would be a lot simpler (pseudo-code):

strSQL = Select CustomerID From MyTable
    Where 'Requested Email' = 'Yes'
    And 'E-Mail' Is Not Null

rst.Open, strSQL, MyConnection

For x = 1 to rst.RecordCount
    Call CreateAndEmailReportFor(rst!CustomerID.Value)
Next X
...
Private Sub CreateAndEmailReportFor(ByRef lngCustID As Long)[green]
''' Create PDF report And Email it to the Customer[/green]
...
End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Sorry Andy, I haven't been explicit enough. Yes, originally a single multi-page report was created, covering all customers in a period. My client had then to print the pages for each customer and send them by post. For customers wanting their reports emailed, their pages had to be extracted to a single PDF and emailed.

In my attempted update I got as far as being able to extract any single customer's report and send it to email using the form and code I showed at the start. My question was how to repeat this process for a number of customers who had requested emails, in one go rather than one at a time.

I'd copied rptDebtorsStatement, now using source query for Requested E-mail = Yes, so potentially including data for several customers. It looks as though your suggested code could do the job of stepping through these customers and feeding their data to the report copy.

 

[Andrzejek]

Don’t be sorry.
Just recently I did something very similar:
My app used to create one long PDF for many Utilities with some information, because this information was printed and (snail) mailed to each Utility. So, one PDF with 25 Utilities.
Now, they wanted to e-mail (a portion of that) PDF to each individual Utility instead. So now, 25 PDFs for 25 Utility Companies.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Great stuff, thanks Andy. I'll battle on with constructing the procedure tomorrow but suspect I'll be back with more questions!

 

[TrekBiker]

Hi again after tearing my hair out.

Slightly different situation now as previous form didn't go far enough to feed required reports.

This is the required report source qryDebtorsStatementForEmail, taking a date range entered into frmSales

SELECT qryDebtors.InvoiceNumber, qryDebtors.CustomerID, qryDebtors.SearchName, qryDebtors.[E-Mail], qryDebtors.Title, qryDebtors.FirstName, qryDebtors.LastName, qryDebtors.LabelName, IIf(Len([Title])=0,[FirstName] & " " & [LastName],[Title] & " " & [FirstName] & " " & [LastName]) AS FullName, qryDebtors.Address1, qryDebtors.Address2, qryDebtors.Town, qryDebtors.County, qryDebtors.PostalCode, qryDebtors.ShipDate, qryDebtors.TotalSale, qryDebtors.AmountDue, CCur(Nz([TotalPayments],0)) AS Payments, [CustomerID] & " " & [Town] AS [Company & Town], qryDebtors.[Invoice Status], qryDebtors.CompanyName, qryDebtors.SearchName, qryDebtors.RequestEmail
FROM qryDebtors
WHERE (((qryDebtors.ShipDate)>=[Forms]![frmSales]![Start] And (qryDebtors.ShipDate)<=[Forms]![frmSales]![End]) AND ((qryDebtors.AmountDue)<>0) AND ((qryDebtors.RequestEmail)=Yes))
ORDER BY qryDebtors.InvoiceNumber;

The data from qryDebtorsStatementForEmail with two companies selected will be like this

These are various attempts at replicating Andy's outline, with the descriptions of errors in different ones.

Private Sub cmdEmailRequested_Click()
Dim dbsTest As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = CurrentDb
On Error GoTo ErrorHandler

    strSQL = "SELECT * FROM qryDebtorsStatementForEmail"
    Set rst = dbs.OpenRecordset(strSQL)  ' gave error 3061, two few parameters, expected 2
'    Set rst = dbsTest.OpenRecordset(strSQL)  ' gave error 91, object variable or With block not set
'    rst.Open , strSQL, MyConnection  ' gave error pointing at MyConnection

'   From Access Programmer's Reference
'    Set qdf = dbs.QueryDefs("qryDebtorsStatementForEmail")
'    qdf.Parameters("RequestEmail") = True 'gave error 3265 Item notfound in this collection
'    Set rst = qdf.OpenRecordset()  'gave error

    If rst.EOF Then Exit Sub
    
    For x = 1 To rst.RecordCount
        Call CreateAndEmailReportFor(rst!CustomerID.Value, rst![E-Mail].Value)  'I think it objected to the Call too
    Next x
    
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   
End Sub

I then want reports to be created for each company, and have this

Private Sub CreateAndEmailReportFor(ByRef lngCustID As Long, E_Mail As String)

Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"
   'Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatement-copy", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
        .To = E_Mail
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath
End Sub

 

[Andrzejek]

First, your Select statement would be a lot easier to read if you do not repeat qryDebtors with every field in your statement:
[tt]
SELECT InvoiceNumber, CustomerID, SearchName,
[E-Mail], Title, FirstName, LastName, LabelName,
IIf(Len(Title)=0,FirstName & " " & LastName,Title & " " & FirstName & " " & LastName) AS FullName,
Address1, Address2, Town, County, PostalCode,
ShipDate, TotalSale, AmountDue, CCur(Nz(TotalPayments,0)) AS Payments,
CustomerID & " " & Town AS [Company & Town], ]Invoice Status],
CompanyName, SearchName, RequestEmail
FROM qryDebtors
WHERE ShipDate [blue]BETWEEN [/blue][Forms]![frmSales]![Start] [blue]AND [/blue][Forms]![frmSales]![End])
AND AmountDue > 0 AND RequestEmail = 'Yes'
ORDER BY InvoiceNumber;
[/tt]
You are going after just one table. Also, if you name your fields without spaces and reserved words, you may omit all [ and ] around your fields.

And, shouldn't your query NOT include the dates? Shouldn't you use the dates when you actually use this query in your form?

    strSQL = "SELECT DISTINCT CustomerID FROM qryDebtorsStatementForEmail " & _
           " WHERE  ShipDate [blue]BETWEEN [/blue][Forms]![frmSales]![Start] [blue]AND [/blue][Forms]![frmSales]![End])"
    Set rst = dbs.OpenRecordset(strSQL)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks for these, Andy. qryDebtors gets fields from four tables but yes to omitting unnecessary repetitions of the name. I'd love to make [E-mail] just Email but the table it comes from is in the Backend on the client's server and goes into several other database components so they'd all need checking. I will do this when I can. And yes I can put the Start and End Dates in the query.

The fun begins from here on!

 

[Andrzejek]

I do know what you mean with the DB being designed by somebody else and you just have to work with what you have.

You may need to do [blue]this[/blue] since you deal with Dates (I hope):

strSQL = "SELECT DISTINCT CustomerID FROM qryDebtorsStatementForEmail " & _
   " WHERE ShipDate BETWEEN [blue]#" &[/blue] [Forms]![frmSales]![Start] [blue]& "# AND #" &[/blue] [Forms]![frmSales]![End]) [blue]& "#"[/blue][red]
Debug.Print strSQL[/red]
Set rst = dbs.OpenRecordset(strSQL)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks again for this. To try to understand provokes the Error 3061, 'too few parameters' I've tried running strSQL = Select * FROM qryXYZ for several queries to find the cause. It seems to happen when a field in the query uses a criterion to look up another form, eg in my case frmSales for Start and End dates.

As you said, putting the criteria into the SELECT statement instead does prevent the error, so thanks for that.

Private Sub cmdEmailRequested_Click()

Dim dbsTest As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim x As Integer
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = CurrentDb

On Error GoTo ErrorHandler
strSQL = "Select DISTINCT CustomerID From qryDebtorsStatementForEmail2 WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"
    Set rst = dbs.OpenRecordset(strSQL)
   
    If rst.EOF Then Exit Sub
         For x = 1 To rst.RecordCount
         Debug.Print rst!CustomerID
'        more code
    Next x
ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

The query qryDebtorsStatementForEmail2 correctly select 6 records for the date range, 4 for one company and 2 for a second. Debug.Print for the DISTINCT version does give 2 results but repeating just one of the two CustomerID values.

Sorry this is dragging on.

 

[Andrzejek]

>repeating just one of the two CustomerID values
Try this:

    ...
    For x = 1 To rst.RecordCount
         Debug.Print rst!CustomerID
[green]'        more code[/green][blue]
         rst.MoveNext[/blue]
    Next x

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Great again, thanks Andy, works properly. What I need now is to do what you advised early on and use each CustomerID in a function CreateAndEmailReportFor(rst!CustomerID.Value) that will
[ul]use rst!CustomerID as parameter in the report's source
use acOutputReport to send the report to a temporary location as a PDF
attach the PDF to an email and somehow feed in the email address[/ul]
Previously I'd collected [E-Mail] from a form but I'm not sure how to do so in the function.

I did try adding [E-Mail] to the Select DISTINCT statement then using the function
Private Sub CreateAndEmailReportFor(ByRef lngCustID As Long, [E-Mail] as String), but this kept failing.

 

[Andrzejek]

I usually use just one parameter (lngCustID) and then - on the 'other side' of the Call - use this parameter to get anything I need for that Customer.

But, you can go the other way around - [pre]
strSQL = "Select DISTINCT [blue]CustomerID[/blue], [red][E-Mail] As EMail[/red] " & _
" From qryDebtorsStatementForEmail2 " & _
" WHERE (ShipDate BETWEEN #" & [Forms]![frmSales]![Start] & "# AND #" & [Forms]![frmSales]![End] & "#)"[/pre]
and pass E-Mail along with CustomerID to your Sub CreateAndEmailReportFor

...
    For x = 1 To rst.RecordCount
         Debug.Print rst!CustomerID
         Call CreateAndEmailReportFor([blue]rst!CustomerID[/blue], [red]rst!EMail[/red])
         rst.MoveNext
    Next x 
...

Private Sub CreateAndEmailReportFor(ByRef [blue]lngCustID[/blue] As Long, ByRef [red]strEMail[/red] as String)
...
End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

That's excellent Andy, all working now, thanks so much for your help.

For completeness I'll add the whole process to another post soon.

 

[Andrzejek]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Spoke a bit too soon and ran into a difficulty in feeding the lngCustID parameter into the report source.

Private Sub Test(ByRef lngCustID As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim CustID As Long
Dim FileName As String
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

On Error GoTo ErrorHandler

    Set dbs = CurrentDb
    CustID = lngCustID
    
    strSQL = "Select * From qryDebtorsStatementForEmail2 WHERE CustomerID=CustID "
    Set rst = dbs.OpenRecordset(stSQL)
    Debug.Print rst!CustomerID

    FileName = "Unpaid Items"
    FilePath = "C:\Users\David\Documents\" & FileName & ".pdf"

'   Create temporary file
    DoCmd.OutputTo acOutputReport, "rptDebtorsStatementForEmail", acFormatPDF, FilePath

    If oOutlook Is Nothing Then
        Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)

    With oEmailItem
        .To = rst![E-Mail]
        .CC = ""
        .Subject = "Debtor Items"
        .Attachments.Add FilePath
        .Display
    End With

    Set oEmailItem = Nothing
    Set oOutlook = Nothing
    Kill FilePath
    
ErrorHandler:
    Resume Next
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
   
End Sub

With this code, Debug.Print shows nothing and there's an error that it can't find parameter CustID.

If this thread has expired I'll start a new one.